PQ - Calculate variances based on different model selections

**sandy666** · 08-23-2020, 03:14 AM

								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)