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 ...
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

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