Forum Discussion

apoje's avatar
apoje
Helper II
4 years ago
Solved

Transform Multiple columns by multiplying by another column

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:

 

DateSKUbundleNameQuantitySKU1Quantity1SKU2Quantity2SKU3Quantity3
18.10.20211234CoreName111234-121234-241234-36
18.10.20211236CoreName251236-131236-251236-35
18.10.20211238CoreName3101238-111238-221238-23

 

the desired result would look like this:

 

DateSKUbundleNameQuantitySKU1Quantity1SKU2Quantity2SKU3Quantity3
18.10.20211234CoreName111234-121234-241234-36
18.10.20211236CoreName251236-1151236-2251236-325
18.10.20211238CoreName3101238-1101238-2201238-230

 

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

Need Help
  • mahoneypat's avatar
    mahoneypat
    4 years ago

    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

2 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    4 years ago

    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's avatar
    ronrsnfld
    Super User
    4 years ago

    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