Transform Multiple columns by multiplying by another column

[apoje]

Hi! I have merged 2 tables together, however to get the proper representation of the data I need to multiply several columns received from 2nd table with one column from the first. I would like that to be a transformation not loads of additional columns. 

Here is an example table:

Date	SKUbundle	Name	Quantity	SKU1	Quantity1	SKU2	Quantity2	SKU3	Quantity3
18.10.2021	1234	CoreName1	1	1234-1	2	1234-2	4	1234-3	6
18.10.2021	1236	CoreName2	5	1236-1	3	1236-2	5	1236-3	5
18.10.2021	1238	CoreName3	10	1238-1	1	1238-2	2	1238-2	3

the desired result would look like this:

Date	SKUbundle	Name	Quantity	SKU1	Quantity1	SKU2	Quantity2	SKU3	Quantity3
18.10.2021	1234	CoreName1	1	1234-1	2	1234-2	4	1234-3	6
18.10.2021	1236	CoreName2	5	1236-1	15	1236-2	25	1236-3	25
18.10.2021	1238	CoreName3	10	1238-1	10	1238-2	20	1238-2	30

I was playing around a bit with the code and so far I came up with beyow syntax which returns an error.

Table.TransformColumns(#"Replaced Value", List.Transform({"Quantity1", "Quantity2", "Quantity3"}, each {_, each (_ * [Quantity]) , type number}))

I appreciate the help!

Andraz

[mahoneypat · Accepted solution]

You can't access other columns from with Table.TransformColumns, so you have to use Table.TransformRows.  Here's one way to do it in the query editor.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrTQMzTQMzIwMlTSUTI0MjYBUs75Ral+ibmpYCGosC6IYQTjgBgmMI4xkGGmFKuDaZgZkmEgLaZQYbBhxjAOiowxmIPNMAskw8B6DaDiukjutACbZoTMMVaKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, SKUbundle = _t, Name = _t, Quantity = _t, SKU1 = _t, Quantity1 = _t, SKU2 = _t, Quantity2 = _t, SKU3 = _t, Quantity3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}, {"SKUbundle", Int64.Type}, {"Name", type text}, {"Quantity", Int64.Type}, {"SKU1", type text}, {"Quantity1", Int64.Type}, {"SKU2", type text}, {"Quantity2", Int64.Type}, {"SKU3", type text}, {"Quantity3", Int64.Type}}),
    Custom1 = Table.FromRecords(Table.TransformRows(#"Changed Type", (x) => Record.TransformFields(x, List.Transform({"Quantity1", "Quantity2", "Quantity3"}, (y) => {y, each x[Quantity] * _}))))
in
    Custom1

Pat

[ronrsnfld]

I don't think it's a big deal to have extra columns, but to do this in a single operation:

I've been frustrated in trying to get Table.TransformColumns to refer to another column in the transform operation.

But you can use the Table.TransformRows method as shown below:

Source

M Code

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Date", type text}, {"SKUbundle", Int64.Type}, {"Name", type text}, {"Quantity", Int64.Type}, 
        {"SKU1", type text}, {"Quantity1", Int64.Type}, 
        {"SKU2", type text}, {"Quantity2", Int64.Type}, 
        {"SKU3", type text}, {"Quantity3", Int64.Type}}),

    xForm = Table.FromRecords(
    Table.TransformRows(#"Changed Type", (row)=> Record.TransformFields(row, {
        {"Quantity1", each row[Quantity] * row[Quantity1]},
        {"Quantity2", each row[Quantity] * row[Quantity2]},
        {"Quantity3", each row[Quantity] * row[Quantity3]}
    })))
in
    xForm

Results