PQ - Calculate variances based on different model selections

[sandy666]

ProductJan
LowJan
HighJan
VarFeb
LowFeb
HighFeb
VarMar
LowMar
HighMar
VarApr
LowApr
HighApr
VarMay
LowMay
HighMay
VarJun
LowJun
HighJun
Var

Low Model Prod A
15
80
-65
97
90
7
80
77
3
74
72
2
25
90
-65
35
99
-64

ProductJanFebMarAprMayJunSelect1Prod B
34
68
-34
11
100
-89
84
93
-9
52
43
9
51
64
-13
51
90
-39

Prod A
15
97
80
74
25
35LowProd C
10
24
-14
13
18
-5
93
100
-7
22
50
-28
55
68
-13
78
100
-22

Prod B
34
11
84
52
51
51Prod D
78
64
14
52
56
-4
83
62
21
57
14
43
77
95
-18
89
36
53

Prod C
10
13
93
22
55
78Select2

Prod D
78
52
83
57
77
89High



Mid Model

ProductJanFebMarAprMayJun

Prod A
60
68
38
45
45
69

Prod B
60
68
20
83
59
41

Prod C
95
96
25
14
44
49

Prod D
31
32
98
82
88
96



High Model

ProductJanFebMarAprMayJun

Prod A
80
90
77
72
90
99

Prod B
68
100
93
43
64
90

Prod C
24
18
100
50
68
100

Prod D
64
56
62
14
95
36

Select models from Data Validation List (Low, Mid or High)

let in Excel.CurrentWorkbook(){[Name="SelectA"]}[Content]

let in Excel.CurrentWorkbook(){[Name="SelectB"]}[Content]
Models (tables are renamed suitably)

let in Table.UnpivotOtherColumns(Excel.CurrentWorkbook(){[Name="Low"]}[Content], {"Product"}, "Month", "Low")

let in Table.UnpivotOtherColumns(Excel.CurrentWorkbook(){[Name="Mid"]}[Content], {"Product"}, "Month", "Mid")

let in Table.UnpivotOtherColumns(Excel.CurrentWorkbook(){[Name="High"]}[Content], {"Product"}, "Month", "High")
Join and calculate variances

let All = Table.Combine({Low, Mid, High}),
UOC = Table.UnpivotOtherColumns(All, {"Product", "Month"}, "Attribute", "Value"),
S1 = Table.ExpandTableColumn(Table.NestedJoin(SelectA,{"Select1"},UOC,{"Attribute"},"Exp",JoinKind.LeftOuter), "Exp", {"Product", "Month", "Value"}),
S2 = Table.ExpandTableColumn(Table.NestedJoin(SelectB,{"Select2"},UOC,{"Attribute"},"Exp",JoinKind.LeftOuter), "Exp", {"Product", "Month", "Value"}),
Join = Table.NestedJoin(S1,{"Product","Month"},S2,{"Product","Month"},"EXP",JoinKind.LeftOuter),
Exp = Table.ExpandTableColumn(Join, "EXP", {"Select2", "Value"}, {"Select2", "Value.1"}),
Variance = Table.AddColumn(Exp, "Var", each [Value] - [Value.1], type number),
UOSC = Table.Unpivot(Variance, {"Value", "Value.1", "Var"}, "Attribute", "Value.2"),
MC = Table.CombineColumns(UOSC,{"Month", "Attribute"},Combiner.CombineTextByDelimiter(" #(lf)", QuoteStyle.None),"Merged"),
Value1 = Table.ReplaceValue(MC,"Value.1",MC[Select2]{0},Replacer.ReplaceText,{"Merged"}),
Value = Table.ReplaceValue(Value1,"Value",MC[Select1]{0},Replacer.ReplaceText,{"Merged"}),
RC = Table.RemoveColumns(Value,{"Select1", "Select2"})
in Table.Pivot(RC, List.Distinct(RC[Merged]), "Merged", "Value.2", List.Sum)