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

[ND_Pard]

Our Fiscal Year ends on June 30th of each calendar year.
Each record of our database contains fields for the FYYY_FM, Department, and a Monetary_Amount.
I need to extract data for periods of 24 consecutive months from our database by Department.
Sometimes the data is historical and the Department may not have expenditures in that FYYY-FM and sometimes the data may include future FYYY_FM (i.e., months with zero amounts as they have not occurred yet).

How can I make sure my Power Query will always return a record for each Department for each of the desired 24 consecutive months even if they have no historical expenditures for that FYYY_FM or for the future FYYY_FM that I want to display?

I'm assuming I will need to use a "list", but am not sure how to accomplish this.

Here is some code that I currently use:

#"Filter the Subdivision" = Table.SelectRows(dbo_HISTORY, each Text.StartsWith([SUBDIVISION], "410-74")),
#"Filter only Expenses" = Table.SelectRows(#"Filter the Subdivision", each [ACCOUNT] >= "5"),
#"Filter FYYYY_FM" = Table.SelectRows(#"Filter only Expenses", each [FYYY_FM] >= "2022_01" and [FYYY_FM] <= "2023_12"),
#"Grouped Rows" = Table.Group(#"Filter FYYYY_FM", {"DEPARTMENT", "FYYY_FM"}, {{"Expenditures", each List.Sum([MONETARY_AMOUNT]), type nullable number}}),
#"Sort by DEPARTMENT & FYYYY_FM" = Table.Sort(#"Grouped Rows",{{"DEPARTMENT", Order.Ascending}, {"FYYY_FM", Order.Ascending}})

Thanks in advance; your suggestions are appreciated.

Currently get this:			Want this returned:
DEPARTMENT	FYYY_FM	Expenditures		DEPARTMENT	FYYY_FM	Expenditures
9999	2022_02	$ 15,275		9999	2022_01	$ 0
9999	2022_03	$ 15,275		9999	2022_02	$ 15,275
9999	2022_05	$ 30,550		9999	2022_03	$ 15,275
9999	2022_06	$ 15,282		9999	2022_04	$ 0
9999	2022_07	$ 15,280		9999	2022_05	$ 30,550
9999	2022_08	$ 15,275		9999	2022_06	$ 15,282
9999	2022_09	$ 15,275		9999	2022_07	$ 15,280
9999	2022_10	$ 2		9999	2022_08	$ 15,275
9999	2022_11	$ 30,550		9999	2022_09	$ 15,275
9999	2022_12	$ 30,550		9999	2022_10	$ 2
9999	2023_02	$ 15,313		9999	2022_11	$ 30,550
9999	2023_03	$ 15,313		9999	2022_12	$ 30,550
9999	2023_04	$ 15,313		9999	2023_01	$ 0
9999	2023_05	$ 15,313		9999	2023_02	$ 15,313
9999	2023_06	$ 15,313		9999	2023_03	$ 15,313
9999	2023_07	$ 15,313		9999	2023_04	$ 15,313
9999	2023_08	$ 15,313		9999	2023_05	$ 15,313
9999	2023_09	$ 15,313		9999	2023_06	$ 15,313
				9999	2023_07	$ 15,313
				9999	2023_08	$ 15,313
				9999	2023_09	$ 15,313
				9999	2023_10	$ 0
				9999	2023_11	$ 0
				9999	2023_12	$ 0

[v-stephen-msft · Accepted solution]

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.