Multiply each column by value in reference table (Key is column name)

[MartinZ1]

Hello everyone,

I'm looking for a way to multiply all values of each column in a table, by a multiplier that is determined by looking up the respective column name in another table.

Example:

Table1:

Col1	Col2	Col3
2	3	1
3	2	4

Table2:

Column	Value
Col1	6
Col2	4
Col3	2

Result:

Col1	Col2	Col3
12	12	2
18	8	8

The values of Table1 should be multiplied by the values in Table2. The specific value of Table2 by which to multiply is determined by looking up the column header name of Table1 in Table2.

The multiplier applied to all values in Table1.Col1 would therefore be: SELECT Value FROM Table2 WHERE Column = "Col1"

Table1 will contain < 20k records and about 40 columns

Table2 will only contain one record per column in Table1.

It is possible, that columns will be added to Table1. The solution should be flexible enough to automatically "consider" them. (Provided the correspondig record in Table2 has been added)

How could i achieve this in a efficient way?

[AlienSx]

Hello, @MartinZ1 

    r = Record.FromList(Table2[Value], Table2[Column]),
    c = List.Buffer(Table.ColumnNames(Table1)),
    functions = List.Buffer(List.Transform(c, (x) => {x, each _ * Record.FieldOrDefault(r, x, 1)})),
    z = Table.TransformColumns(Table1, functions)

[wdx223_Daniel]

=let a=List.Buffer(Table.ToRows(Table2)) in Table.FromRecords(Table.TransformRows(Table1,each Record.TransformFields(_,List.Transform(a,(x)=>{x{0},each _*x{1}}))))

[Anonymous · Accepted solution]

Hi @MartinZ1 

You can create two blank query , and put the following code to advanced editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PMVTSUTJTitUBc4yAHBMYxxjIMVKKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column", type text}, {"Value", Int64.Type}})
in
    #"Changed Type"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlLSUTIGYkOlWJ1oMAskYqIUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Col1 = _t, Col2 = _t, Col3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Col1", Int64.Type}, {"Col2", Int64.Type}, {"Col3", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Custom", each List.Min(Table.SelectRows(Query1,(x)=>x[Column]=[Attribute])[Value])*[Value]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Value"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Attribute"}, {{"Count", each Table.AddIndexColumn(_,"Index",1,1),type table}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Custom", "Index"}, {"Custom", "Index"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded Count", List.Distinct(#"Expanded Count"[Attribute]), "Attribute", "Custom"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns1"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.