PQ - Calculate variances based on different model selections

										Product	Jan Low	Jan High	Jan Var	Feb Low	Feb High	Feb Var	Mar Low	Mar High	Mar Var	Apr Low	Apr High	Apr Var	May Low	May High	May Var	Jun Low	Jun High	Jun Var
Low Model										Prod A	15	80	-65	97	90	7	80	77	3	74	72	2	25	90	-65	35	99	-64
Product	Jan	Feb	Mar	Apr	May	Jun		Select1		Prod 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	35		Low		Prod 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	51				Prod 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	78		Select2
Prod D	78	52	83	57	77	89		High
Mid Model
Product	Jan	Feb	Mar	Apr	May	Jun
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
Product	Jan	Feb	Mar	Apr	May	Jun
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)

Code:

---

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

---

Code:

---

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

---

Models (tables are renamed suitably)

Code:

---

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

---

Code:

---

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

---

Code:

---

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

---

Join and calculate variances

Code:

---

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)

---