Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Remove Digits Dynamically based on value of another cell

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! 

Need Help
  • spinfuzer's avatar
    spinfuzer
    2 years ago

    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"}
)

     

     

     

1 Reply

  • spinfuzer's avatar
    spinfuzer
    Solution Sage
    2 years ago

    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"}
)