Remove Digits Dynamically based on value of another cell

[Anonymous]

I am trying to remove the last two digits from [ACCOUNT_NUMBER] if [ACCOUNT_TYPE] = "H" else simply return [ACCOUNT_NUMBER]. 

Where am I going wrong with this formula: 

Thanks! 

[spinfuzer · Accepted solution]

Table.TransformColumns only gives you access to a single column at a time,  not the entire row.  You cannot do conditional logic based on other columns in the same row.  The error is saying that _ is a number, not a record/row of the table.  [ACCOUNT_TYPE] is the same as _[ACCOUNT_TYPE] in this case.  

You have the right idea, but use Table.ReplaceValue instead. 

Table.ReplaceValue(#"Changed Type1",
each [ACCOUNT_NUMBER],
each if [ACCOUNT_TYPE] = "H" then Text.Start(Text.From([ACCOUNT_NUMBER]), Text.Length(Text.From([ACCOUNT_NUMBER])) - 2) else [ACCOUNT_NUMBER]
Replacer.ReplaceValue,
{"ACCOUNT_NUMBER"}
)