CODEWITHSUNDEEP
powerbidaxpowerquery
mjp
mjp

Reputation: 119

Fill in Missing Dates Power BI

I have a table in Power BI that is missing some dates, as seen in the snapshot below, where there were no orders on January 1st (there are other missing dates as well).

OutboundOrders Table

I would like to add in 5 records for each missing date (one for each BRAND), where BRAND is a/b/c/d/e and ORDERS and UNITS are both 0, while keeping all other records the same. I have created a calendar table and created a relationship between the two from Date to SHIP_DATE, as seen below:

Relationship

In Query Editor, I tried merging these two tables with a Right Join to include all records from the calendar, hoping it would add in January 1st, but it resulted in no change at all.

If anyone has any ideas on how to fix this issue, I would greatly appreciate it.

Upvotes: 0

Views: 3382

Answers (1)

Aleksei Zhigulin
Aleksei Zhigulin

Reputation: 1634

In Query Editor you may append table for missing dates with fictive values.

If there are few missing dates and you know all of them:

= Table.Combine({your_data,
#table(type table [SHIP_DATE = date, BRAND = text, ORDERS = number, UNITS = number],
{{#date(2019,1,1),"a",0,0},
{#date(2019,1,3),"b",0,0},
{#date(2019,1,4),"c",0,0},
{#date(2019,1,5),"d",0,0},
{#date(2019,1,6),"e",0,0}})})

Or if you wish to catch missing dates automatically:

let
    Source = your_data,
    Types = Table.TransformColumnTypes(Source,{{"SHIP_DATE", type date}, {"BRAND", type text}, {"ORDERS", Int64.Type}, {"UNITS", Int64.Type}}),
    FactDates = Types[SHIP_DATE],
    AllDates = List.Generate(()=>List.Min(FactDates), each _ <= List.Max(FactDates), each Date.AddDays(_,1)),
    MissingDates = List.Difference(AllDates,FactDates),
    ToTable = Table.FromList(MissingDates, Splitter.SplitByNothing()),
    Index = Table.AddIndexColumn(ToTable, "i", 0, 1),
    Brand = Table.AddColumn(Index, "BRAND", each {"a".."z"}{[i]}),
    Removed = Table.RemoveColumns(Brand,{"i"}),
    Renamed = Table.RenameColumns(Removed,{{"Column1", "SHIP_DATE"}}),
    Types2 = Table.TransformColumnTypes(Renamed,{{"SHIP_DATE", type date}, {"BRAND", type text}}),
    Appended = Table.Combine({Types, Types2}),
    Replaced = Table.ReplaceValue(Appended,null,0,Replacer.ReplaceValue,{"ORDERS", "UNITS"}),
    Sorted = Table.Sort(Replaced,{{"SHIP_DATE", Order.Ascending}})
in
    Sorted

Keep in mind that this code will work fine while number of missing dates is less than or equal 26 (number of letters in the English alphabet). If you expect greater number of missing dates, just replace code in step Brand for something like this:

Brand = Table.AddColumn(Index, "BRAND", each "_brand"&Text.From([i]))

Upvotes: 2

Related Questions