Power Query: How do I ensure every grouped record includes one of each item in a list

Hi ND_Pard ,

You can create a complete yearmonth table and merge it into the main table.

1.Create a blank query in Power Query, hen copy and paste the following code into the advanced editor. You can also modify the specific date to change the start and end of the year.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtI10jVUio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StartDate = _t]),
    #"Added Custom" = Table.AddColumn(Source, "EndDate", each Date.FromText("2023-9-30")),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"EndDate", type date}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type2",{{"StartDate", type date}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Dates", each {Number.From([StartDate])..Number.From([EndDate])}),
    #"Expanded Dates" = Table.ExpandListColumn(#"Added Custom1", "Dates"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Dates",{{"Dates", type date}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"StartDate", "EndDate"}),
    #"Added Custom2" = Table.AddColumn(#"Removed Columns1", "Year", each Date.Year([Dates])),
    #"Changed Type3" = Table.TransformColumnTypes(#"Added Custom2",{{"Year", type text}}),
    #"Added Custom3" = Table.AddColumn(#"Changed Type3", "Month", each Text.PadStart(Text.From(Date.Month([Dates])),2,"0")),
    #"Changed Type4" = Table.TransformColumnTypes(#"Added Custom3",{{"Month", type text}}),
    #"Added Custom4" = Table.AddColumn(#"Changed Type4", "Custom", each [Year]&"_"&[Month]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom4",{"Dates", "Year", "Month"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns")
in
    #"Removed Duplicates"

2.Merge them. The matching column and the join kind are as follows.

3.Expand it and remove the original year-month column and rename the new one.

4.Replace null with 0.

You can also download my attachment for more details.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.