Here is a different Power Query Approach:
- Create a list of ALL the possible weeks
- Group by the Criteria
- Create a subTable for each group
- Create a list of the Missing Weeks
- For each missing week, create a Record whereby
- The Criteria are carried over from the group
- The Date is incremented according to the missing week list
- the various counts are set to Null (you could set the to zero if you prefer, or anything else by changing the value in the List.Generate function)
- Then expand the subTables and set the Type
M Code
let
Source = Excel.CurrentWorkbook(){[Name="Table6"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{
{"Criteria One", type text}, {"Criteria Two", type text}, {"Start of Week", type date},
{"Count", Int64.Type}, {"Unique Count in Week", Int64.Type}, {"Cumulative Unique Count", Int64.Type}}),
//create a list of all the possible weeks
wkFirst = List.Min(#"Changed Type"[Start of Week]),
wkLast = List.Max(#"Changed Type"[Start of Week]),
numWeeks = Duration.Days(wkLast-wkFirst)/7+1,
allWeeks = List.Dates(wkFirst,numWeeks,#duration(7,0,0,0)),
//Group; then add missing weeks to each sub-table
#"Grouped Rows" = Table.Group(#"Changed Type", {"Criteria One", "Criteria Two"}, {
{"All", each _, type table [Criteria One=nullable text, Criteria Two=nullable text, Start of Week=nullable date, Count=nullable number, Unique Count in Week=nullable number, Cumulative Unique Count=nullable number]},
{"with All weeks", each if Table.RowCount(_) = List.Count(allWeeks) then _
//create the subtables whereby we fill in the missing weeks
//and create a record for the missing weeks whereby the Criteria are carried over
//and the Counts are "nulled".
//if you want to make the counts Zero, just change that within the List.Generate function
else Table.Sort(
Table.Combine({_,
let
missingWeeks = List.RemoveMatchingItems(allWeeks, [Start of Week]),
startDts = List.Generate(()=>
[Counter =0, Criteria One= [Criteria One]{0}, Criteria Two= [Criteria Two]{0}, Start of Week=missingWeeks{0}, Count=null, Unique Count in Week=null, Cumulative Unique Count=null],
each [Counter] < List.Count(missingWeeks),
each [Counter = [Counter]+1, Criteria One=[Criteria One], Criteria Two=[Criteria Two], Start of Week=missingWeeks{[Counter]+1}, Count=null, Unique Count in Week=null, Cumulative Unique Count=null])
in
//Remove the "Counter" column from the Combined subtable
//Then Sort by "Start of Week"
Table.RemoveColumns(Table.FromRecords(startDts),"Counter")}),
{"Start of Week"})
}
}),
//Remove Extra Columns, then expand the subtable, and type it
#"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Criteria One", "Criteria Two", "All"}),
#"Expanded with All weeks" = Table.ExpandTableColumn(#"Removed Columns", "with All weeks", {"Criteria One", "Criteria Two", "Start of Week", "Count", "Unique Count in Week", "Cumulative Unique Count"}, {"Criteria One", "Criteria Two", "Start of Week", "Count", "Unique Count in Week", "Cumulative Unique Count"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded with All weeks",{{"Criteria One", type text}, {"Criteria Two", type text}, {"Start of Week", type date}, {"Count", Int64.Type}, {"Unique Count in Week", Int64.Type}, {"Cumulative Unique Count", Int64.Type}})
in
#"Changed Type1"
Orig Data
Results
