Two Dimensional Append?

[kangelosanto]

Hey All,

I've been stuck on this at work all day so turning here in my final hour of desperation!

I have two tables, listed below:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Month[/TD]
[TD]Program[/TD]
[TD]Projected Sales[/TD]
[/TR]
[TR]
[TD]1/1/2017[/TD]
[TD]1[/TD]
[TD]ABC[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]2/2/2017[/TD]
[TD]2[/TD]
[TD]XYZ[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]3/3/2017[/TD]
[TD]3[/TD]
[TD]ABC[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]4/4/2017[/TD]
[TD]4[/TD]
[TD]XYZ[/TD]
[TD]400[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 500"]
<tbody>[TR]
[TD]Program[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]1.1[/TD]
[TD]1.2[/TD]
[TD]1.3[/TD]
[/TR]
[TR]
[TD]XYZ[/TD]
[TD]2.1[/TD]
[TD]2.2[/TD]
[TD]3.3[/TD]
[/TR]
</tbody>[/TABLE]


In the second table, the headers (1, 2, 3) are months, with the data set (1.1, 1.2, etc.) being modifiers we want to apply to our forecast. The problem is that these can change as frequently as weekly, so I need a way to add an additional column to the right of forecasted sales that is whatever the forecasted sale is multiplied by that month's modifier as according to the corresponding program, in a data table that's ~125,000 rows large. I know I could just run a vlookup(match()) function in a column to the right of the table but the table i'm referencing is linked to dozens of reporting workbooks and would need to have all the formulas updated.

Please help!

 

[MarcelBeug]

You can unpivot table2 and then merge table1 with the unpivoted table2.

Table2Unpivot:

let
    Source = Table2,
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Program"}, "Month", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Month", Int64.Type}})
in
    #"Changed Type"

Final query:

let
    Source = Table.NestedJoin(Table1,{"Program", "Month"},Table2Unpivot,{"Program", "Month"},"Table2Unpivot",JoinKind.LeftOuter),
    #"Expanded Table2Unpivot" = Table.ExpandTableColumn(Source, "Table2Unpivot", {"Value"}, {"Value"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table2Unpivot", "Multiplied", each [Projected Sales] * (if [Value] = null then 1 else [Value]))
in
    #"Added Custom"

I used 1 for missing multipliers like month 4 for XYZ.