Forum Discussion
Multiply two columns based on column header name
Dear all,
I need to multiply two columns based on the Header Name:
- Column1 contains %
- Column2 contains €
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:
- Is this the code I am showing you correct?
- How can I complete it to make the multiplication of the columns?
Thanks for your help.
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
11 Replies
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_MultipliedCan I ask you some question to understand the logic behind your code?
I am not familiar with this structure.
Thanks.
