Log In
Register
Home
March 4, 2020
Offline
Hi All,
I am trying to replace column A's value based on Column B.
So i have a column call "debtor name" and a column call "Internal External".
If "Debtor name" contains "Origin", replace internal with external in column "Internal External", otherwise dont make change on column "Internal External"
I wrote below and does not work, please help
=Table.ReplaceValue(#"previousstep",each[internal external],each if text.contains ([debtor name],"Origin") then "external" else [internal external], replacer.replacetext,{internal external})
Trusted Members
Moderators
Power BI
January 31, 2022
Offline
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
March 4, 2020
Offline
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
Trusted Members
Moderators
Power BI
January 31, 2022
Offline
OK. understood. Try attaching the file again. After adding a file, don't forget to press "Start upload" before you submit your reply.
March 4, 2020
Offline
thanks Riny, see attached.
Trusted Members
Moderators
Power BI
January 31, 2022
Offline
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.
1 Guest(s)
