Forum Discussion

NovelKel's avatar
NovelKel
New Member
3 years ago

Multiplying Original Value by Multiple Varying Columns

Hello!

 

I am not sure how to approach this so I would welcome any direction. I have an original value that needs to be multiplied in sequence by varying numbers of columns. I do need the output of each arithmetic operation in the original column or another column.

 

 

Is there a smart way to do this in Power Query? I appreciate your help!

Need Help

1 Reply

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    3 years ago

    Use this

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k0syShW0lEyNDAAkkZ6JkDSQM/YFEwZWIJk9MyVYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Headers = _t, Amount = _t, #"Multiplier 1" = _t, #"Multiplier 2" = _t, #"Multiplier 3" = _t, #"Multiplier 4" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Headers", type text}, {"Amount", Int64.Type}, {"Multiplier 1", type number}, {"Multiplier 2", type number}, {"Multiplier 3", type number}, {"Multiplier 4", type number}}),
    Custom1 = Table.FromRecords( Table.TransformRows(#"Changed Type", (r) => Record.TransformFields(r, {{"Multiplier 1", each r[Amount] * _}, {"Multiplier 2", each r[Amount]*r[Multiplier 1] * _}, {"Multiplier 3", each r[Amount]*r[Multiplier 1]*r[Multiplier 2] * _}, {"Multiplier 4", each r[Amount]*r[Multiplier 1]*r[Multiplier 2]*r[Multiplier 3] * _}})),Value.Type(#"Changed Type"))
in
    Custom1