Forum Discussion
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 Talk and column: Interactions and then divide the output by 86400, by transforming not by adding new column.
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}})))
12 Replies
xzmicheResolver I
= Table.TransformColumns(#"Expanded Table Column1", each [Avg Talk], each [Avg Talk] *[Interactions]/ 86400, type number)
Syndicate_AdminAdministrator
- xzmiche
= Table.ReplaceValue(#"Changed Type",each [Avg Talk],each [Avg Talk]*[Interactions],Replacer.ReplaceValue,{"Avg Talk"})
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 Custom1Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.- Syndicate_Admin
@Magdoulin
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 Custom1Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly. - Syndicate_Admin
@Magdoulin
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 Custom1Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.