After merging tables with past (M-1, M-12') data and expanding columns, Qty_01 and Qty_12 have nulls, they have to be replaced by 0 for future calculations, otherwise nulls are returned. I added a step 'Replaced Value' after 'Removed Columns' in 'qOutput' and qOutput (2)' queries:
[tt]= Table.ReplaceValue(#"Removed Columns",null,0,Replacer.ReplaceValue,{"Qty_01", "Qty_12"})[/tt]
(a result from recorder after selecting 'Removed columns' step, selecting fields 'Qty_01' and 'Qty_12', and "replace values' action from menu - replace 'null' by '0')
The full code (advanced editor):
[pre]let
Source = tSource,
#"Filtered Rows" = Table.SelectRows(Source, each ([YearID] = 2022)),
#"Merged Queries" = Table.NestedJoin(#"Filtered Rows", {"Product", "Class", "ID_01"}, tSource, {"Product", "Class", "ID"}, "tSource", JoinKind.LeftOuter),
#"Expanded {0}" = Table.ExpandTableColumn(#"Merged Queries", "tSource", {"Qty"}, {"Qty_01"}),
#"Merged Queries1" = Table.NestedJoin(#"Expanded {0}", {"Product", "Class", "ID_12"}, tSource, {"Product", "Class", "ID"}, "tSource", JoinKind.LeftOuter),
#"Expanded {0}1" = Table.ExpandTableColumn(#"Merged Queries1", "tSource", {"Qty"}, {"Qty_12"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded {0}1",{"YearID", "ID", "ID_01", "ID_12"}),
#"Replaced Value" = Table.ReplaceValue(#"Removed Columns",null,0,Replacer.ReplaceValue,{"Qty_01", "Qty_12"}),
Calc_01 = Table.ReplaceValue(#"Replaced Value", each [Qty_01], each [Qty] - [Qty_01],Replacer.ReplaceValue, {"Qty_01"}),
Calc_12 = Table.ReplaceValue(Calc_01, each [Qty_12], each [Qty] - [Qty_12],Replacer.ReplaceValue, {"Qty_12"})
in
Calc_12[/pre]
Note that:
- excel may automatically change connection name when the query changes,
- pivot table duplicates query and uses the new connection, su it is necessary to change the query for pivot table too,
- power query has a simple pivot functionality, you can consider building output tables in power query and return results in tables (in a copy of qOutput, a step (Removed Columns1) [tt]= Table.RemoveColumns(Calc_12,{"Qty", "MonthID", "Qty_12"})[/tt] and next (Pivoted Column) [tt]= Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[Month]), "Month", "Qty_01", List.Sum)[/tt] loaded to worksheet returns a table close to pivot table summary for 'Qty_01').
combo