Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Transformation to Multiply and Divide in the Same Step

  Guys, what is wrong with this script? = Table.TransformColumns(#"Expanded Table Column1", {{"Avg Talk", each _ *[Interactions]/ 86400, type number}})   What I aim to is to multiply column: Avg ...
Need Help
  • xzmiche's avatar
    xzmiche
    5 years ago

    Table.TransformColumns can access only each column in isolation. We have to use below walk around method (this solution is from power-query-transform-a-column-based-on-another-column 😞 

     

     

    = Table.FromRecords(Table.TransformRows(#"Changed Type", (r)=> Record.TransformFields(r,{{"Avg Talk", each _ * r[Interactions]/86400}})))

     

     

     

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Anonymous 
You may just use Table.Replacevalue():

 

= Table.ReplaceValue(#"Changed Type", each [Value], each [Value] * [Interaction]/86400,Replacer.ReplaceValue, {"Value"})

 

 

Full code of the example:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsjAzMTBQ0lEyNFCK1YkGUiAA4ivFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Value = _t, Interaction = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Value", Int64.Type}, {"Interaction", Int64.Type}}),
    Custom1 = Table.ReplaceValue(#"Changed Type", each [Value], each [Value] * [Interaction]/86400,Replacer.ReplaceValue, {"Value"})
in
    Custom1

 

 

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.