Hi Vivian,

I believe it's better that you create the "Internal External" column by adding a custom column, like this:

Table.AddColumn(#"Previous Step", "Internal External", each if Text.Contains ([Debtor name], "Origin") then "External" else "Internal")

If that doesn't work for you, please upload an anonymized file so that we can see why not and come up with another solution.

Riny

Thanks Riny, I tried below code, got error message text.contains was not recognised, i have attached sample file.

So basically, in the sample date, if debtor name contains "origin", it would be classified as internal, which i wanna change it to external, other debtors remain their classification of internal/enternal in column of " Internal External".

much appreciated

OK. understood. Try attaching the file again. After adding a file, don't forget to press "Start upload" before you submit your reply.

thanks Riny, see attached.

See if the attached file works for you. I connected to the date with PQ, applied some steps and loaded the end result back to Excel (the orange table in Sheet "Table1").

The steps imply that I first added a Custom column that checks if the debtor name has "origin" in it. If so, return EXTERNAL otherwise return the value from the "Internal External" column. I noted that there were some rows that didn't have something in "Internal External" and these caused errors. That's why I wrapped the code for the added column in "try" and "otherwise". Something similar to IFERROR in Excel.

Then I removed the original "Internal External" column, renamed the Custom column and reordered all columns. Perhaps not the most sophisticated solution but it's easy to follow and it works.

Brilliant!! You rock, it is perfect.rnThanks for help.rnEnjoy the rest of your day.