Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The 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.

Reply
Mic1979
Helper V
Helper V

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:

  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.

1 ACCEPTED SOLUTION
dufoq3
Super User
Super User

Hi @Mic1979, check this. This will also work if you have more than 1 % or € column (they will be multiplied all together)

 

Output

dufoq3_0-1737631375740.png

 

 

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

 

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

11 REPLIES 11
Mic1979
Helper V
Helper V

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

I am not familiar with this structure.

 

Thanks.

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


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

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

Hi Mic,

 

result of a is list of number for selected columns (which contains % or € in the header)

dufoq3_0-1737728927115.png

 

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.

dufoq3_2-1737729192280.png

 

Text.Combine combines list into the text with delimiter "*"

dufoq3_3-1737729267942.png

 

Expression.Evaluate does the calculation based on text. Read this.


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Many thanks

You're welcome.


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

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
Super User
Super User

Hi @Mic1979, check this. This will also work if you have more than 1 % or € column (they will be multiplied all together)

 

Output

dufoq3_0-1737631375740.png

 

 

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

 

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Will do..Thanks a lot. Appreciated

Hello dufoq3

Your solution is working greatly. THANKS A LOT.

You're welcome Mic, enjoy 😉


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors
Top Kudoed Authors