Forum Discussion
Transform By Dividing Based On Another Column Conditionally
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?
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.
3 Replies
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"})
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.
