Forum Discussion

IwanVB's avatar
IwanVB
Frequent Visitor
2 years ago

If statement based multiply without new column

I need one line of powerquery code for the following

- On a record, i have a field called "2024" (numeric)
- I have another field called "Currency" (text)
- If the Currency value is "EUR", then multiply the 2024 field by 2 in any other cast by 50.

 

Yes, this can be done by adding a new column and later remove the old ones, but looking for a solution without doing so.

Tried the code below but unfortunate not working: 

= Table.TransformColumns(#"PreviousStep", {{"2024", each if [Currency] = "EUR" then _ * 2 else _ * 50, type number}})



Need Help

1 Reply

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    You can also use Table.ReplaceValue.  Same basic idea,

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjVQ0lFyDQ1SitWJVjIHcUKDXZRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"2024" = _t, Currency = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"2024", Int64.Type}, {"Currency", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",each [2024],each if [Currency]="EUR" then [2024]*2 else [2024]* 50,Replacer.ReplaceValue,{"2024"})
in
    #"Replaced Value"