Add "missing" date rows for respective group and default value in another column in PowerBI?

Question

I'm using PowerBI and looking to summarize (average) data over a period of time, however I realized that my source data doesn't reflect "empty" (zero totals) date values. This are valid and required for accurately aggregating totals over a period of time.

I've created a new date table using the following expression, to create all the dates within the preliminary tables range:

Date_Table = CALENDAR(MIN('SalesTable'[Daily Sales Date]),MAX('SalesTable'[Daily Sales Date]))

However, when trying to create a relationship with the created table and the original SalesTable to fill in the "missing dates" I haven't been successful. If anyone has encountered this a similar issue and has any advice or could point me towards resources to resolve this, I would be greatly appreciative.

I've included an example of my current and expected results below. Thanks!

current:

Item Group	Daily Sales Date	Total
Fruit	January 1	5
Vegetable	January 5	10

expected:

Item Group	Daily Sales Date	Total
Fruit	January 1	5
Fruit	January 2	0
Fruit	January 3	0
Fruit	January 4	0
Fruit	January 5	0
Vegetable	January 1	0
Vegetable	January 2	0
Vegetable	January 3	0
Vegetable	January 4	0
Vegetable	January 5	10

user_2872922 · Answer

To do this in Power Query as you request, you can create the Date Table in Power Query, then Join it with each group in the Item Group column:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcisqzSxR0lHySswrTSyqVDAEsk2VYnWilcJS01NLEpNyUpFkTYFsQwOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Item Group" = _t, #"Daily Sales Date" = _t, Total = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Item Group", type text}, 
        {"Daily Sales Date", type date}, 
        {"Total", Int64.Type}}),

//create a table with a list of all dates for the date range in the table
    allDates = Table.FromColumns({
                    List.Dates(List.Min(#"Changed Type"[Daily Sales Date]),
                Duration.Days(List.Max(#"Changed Type"[Daily Sales Date]) - List.Min(#"Changed Type"[Daily Sales Date]))+1,
                #duration(1,0,0,0))},type table[Dates=date]),

//group by the item group column
//Then join each subtable with the allDates table
    group = Table.Group(#"Changed Type",{"Item Group"},{
        {"Daily Sales Date", each Table.Join(_,"Daily Sales Date",allDates,"Dates",JoinKind.RightOuter)}
    }),

//Expand the grouped table
    #"Expanded Daily Sales Date" = Table.ExpandTableColumn(group, "Daily Sales Date", {"Total", "Dates"}, {"Total", "Dates"}),

//replace the nulls with zero's
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Daily Sales Date",null,0,Replacer.ReplaceValue,{"Total"}),

//set proper column order and types
    #"Reordered Columns" = Table.ReorderColumns(#"Replaced Value",{"Item Group", "Dates", "Total"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"Dates", type date}, {"Total", Int64.Type}})
in
    #"Changed Type1"

If you wanted to average over the existing date range, you can try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcisqzSxR0lHySswrTSyqVDAEsk2VYnWilcJS01NLEpNyUpFkTYFsQwOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Item Group" = _t, #"Daily Sales Date" = _t, Total = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Item Group", type text}, 
        {"Daily Sales Date", type date}, 
        {"Total", Int64.Type}}),

//count the number of dates
    numDates = Duration.Days(List.Max(#"Changed Type"[Daily Sales Date]) - List.Min(#"Changed Type"[Daily Sales Date]))+1,
    
//group by Item Group, then average using Sum/Number of dates for each subgroup
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Item Group"}, {
        {"Average", each List.Sum([Total])/numDates}})
in
    #"Grouped Rows"

And there are numerous other ways of accomplishing what you probably require.

Add "missing" date rows for respective group and default value in another column in PowerBI?

1 Answers