Forum Discussion
Multiply each column by value in reference table (Key is column name)
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?
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.
3 Replies
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.
=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}}))))
