Forum Discussion

Mic1979's avatar
Mic1979
Post Partisan
1 year ago
Solved

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",...
Need Help
  • dufoq3's avatar
    dufoq3
    1 year ago

    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's avatar
Mic1979
Post Partisan
1 year ago

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

I am not familiar with this structure.

 

Thanks.

  • dufoq3's avatar
    dufoq3
    Community Champion
    1 year ago

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

    • Mic1979's avatar
      Mic1979
      Post Partisan
      1 year ago

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

      Thanks a lot!!

    • Mic1979's avatar
      Mic1979
      Post Partisan
      1 year ago

      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's avatar
        dufoq3
        Community Champion
        1 year ago

        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.