Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.