cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
apoje
Helper II
Helper II

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

1 ACCEPTED SOLUTION
mahoneypat
Super User
Super User

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

2 REPLIES 2
mahoneypat
Super User
Super User

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

ronrsnfld
Resolver IV
Resolver IV

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

ronrsnfld_0-1634559623677.png

 

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

ronrsnfld_1-1634559714531.png

 

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors