Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Dear all,
I need to multiply two columns based on the Header Name:
The code I used is this:
Result = Table.AddColumn (
RemoveDuplicate,
"Result",
(_) => ((x,y) => List.Zip({x,y}))
(Table.SelectColumns (TransformPrice, List.Select(Table.ColumnNames(TransformPrice), each Text.Contains(_, "€"))),
Table.SelectColumns (TransformPrice, List.Select(Table.ColumnNames(TransformPrice), each Text.Contains(_, "%")))))
]
[Result]
I used List.Zip to match together rows with the same index. I know this is not complete because I am missing the last part making the calculation. However:
Thanks for your help.
Solved! Go to Solution.
Hi @Mic1979, check this. This will also work if you have more than 1 % or € column (they will be multiplied all together)
Output
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlVV0lFKBGJDAzBhYKAUqxMNZIDEk4DYCCRuChM3AosnA7ExTD1QIhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column1%" = _t, Column2 = _t, #"Column3€" = _t, Column4 = _t]),
ChangedType = Table.TransformColumnTypes(Source,{{"Column1%", Percentage.Type}, {"Column3€", type number}, {"Column4", type number}}),
Ad_Multiplied = Table.AddColumn(ChangedType, "Multiplied % and €", each
[ a = Record.ToList(Record.SelectFields(_, List.Select(Record.FieldNames(_), (x)=> List.Contains({"%", "€"}, x, (y,z)=> Text.Contains(z, y))))),
b = Expression.Evaluate(Text.Combine(List.Transform(a, (x)=> Number.ToText(x, "G", "en-US")), "*"))
][b], type number)
in
Ad_Multiplied
Can I ask you some question to understand the logic behind your code?
I am not familiar with this structure.
Thanks.
Hello dufoq3,
first question:
Thanks
Hi Mic,
result of a is list of number for selected columns (which contains % or € in the header)
List.Transform(a, (x)=> Number.ToText(x, "G", "en-US")) transforms list of numbers to list of text values with "." as decimal and no spaces or comma for thousands etc.
Text.Combine combines list into the text with delimiter "*"
Expression.Evaluate does the calculation based on text. Read this.
Many thanks
Please let me study this a little bit, I would like to make the right questions and not waste your time.
Thanks a lot!!
Hi @Mic1979, check this. This will also work if you have more than 1 % or € column (they will be multiplied all together)
Output
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlVV0lFKBGJDAzBhYKAUqxMNZIDEk4DYCCRuChM3AosnA7ExTD1QIhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column1%" = _t, Column2 = _t, #"Column3€" = _t, Column4 = _t]),
ChangedType = Table.TransformColumnTypes(Source,{{"Column1%", Percentage.Type}, {"Column3€", type number}, {"Column4", type number}}),
Ad_Multiplied = Table.AddColumn(ChangedType, "Multiplied % and €", each
[ a = Record.ToList(Record.SelectFields(_, List.Select(Record.FieldNames(_), (x)=> List.Contains({"%", "€"}, x, (y,z)=> Text.Contains(z, y))))),
b = Expression.Evaluate(Text.Combine(List.Transform(a, (x)=> Number.ToText(x, "G", "en-US")), "*"))
][b], type number)
in
Ad_Multiplied
Will do..Thanks a lot. Appreciated
Hello dufoq3
Your solution is working greatly. THANKS A LOT.