Transform By Dividing Based On Another Column Conditionally

[Anonymous]

Hello there,

Can someone help me on spotting the problem with this script:

= Table.TransformColumns(#"Grouped Rows2", {{"Tenure", each if [AD Account]="Shams, Magdoulin" then _ / 2 else _, type number}})

I get the below error

Expression.Error: We cannot apply field access to the type Duration.
Details:
Value=335.00:00:00
Key=AD Account

Although I have tried as a test to divide the column Tenure by 2 directly and it worked without errors, so, why when I set a condition based on another column value it does not accept?

[wdx223_Daniel]

the function of Table.TransformColumns can not get the value of other columns, you can try Table.ReplaceValue

= Table.RepaceValue(#"Grouped Rows2",each [AD Account],"",(x,y,z)=x/(1+Byte.From(y="hams, Magdoulin)),{"Tenure"})

[Anonymous]

So, this is something I cannot tweak the below to get output?

= Table.TransformColumns(#"Grouped Rows2", {{"Tenure", each _ / [Count], type number}})

[AlexisOlson · Accepted solution]

Correct. Table.TransformColumns only transforms columns individually. You can't cross-reference other columns in the transformation definition.

The easiest method is usually to define a new custom column instead of transforming an existing one since you can reference multiple columns that way.