Multiply two columns based on column header name

[Mic1979]

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:

1. Is this the code I am showing you correct?
2. How can I complete it to make the multiplication of the columns?

Thanks for your help.

[Mic1979]

Can I ask you some question to understand the logic behind your code?

I am not familiar with this structure.

Thanks.

[dufoq3]

Of course, go ahead. Which part do you need to explain?

[Mic1979]

Hello dufoq3,

first question:

• what is the target for this part: b = Expression.Evaluate(Text.Combine(List.Transform(a, (x)=> Number.ToText(x, "G", "en-US")), "*")) 

Thanks

[dufoq3]

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.

[Mic1979]

Many thanks

[dufoq3]

You're welcome.

[Mic1979]

Please let me study this a little bit, I would like to make the right questions and not waste your time.

Thanks a lot!!

[dufoq3 · Accepted 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

[Mic1979]

Will do..Thanks a lot. Appreciated

[Mic1979]

Hello dufoq3

Your solution is working greatly. THANKS A LOT.

[dufoq3]

You're welcome Mic, enjoy 😉