Forum Discussion
Moving values to a new column based on another column
Hi.
Consider the situation. I have column CompanyName that are associated with rows of another coumn called Type. These are "Chain" ,"NF", "FF", and "DF"
| CompanyName | Type |
| Walmart | Chain |
| Costsco | NF |
| Publix | NF |
| Bar | FF |
Consider that Costco and Publix are both Chains, but read as NF. Which is wrong.
How do I create a calculated column or expression that places these names into the right Type, "Chain." This is going to be on a large scale of data with multiple other company names as well.
Thank you!
Hi park ,
The above code won't work with approximate matches. Try this one instead:
Here the code:
if Text.Contains([CompanyName], "Publix") or Text.Contains([CompanyName], "Costco") then "Chain" else [Type]
Hope this helps 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
6 Replies
Hi park ,
Maybe something like this?
You could create a nested if statement with all your conditions. Here an example
Here the code:
if [CompanyName] = "Publix" or [CompanyName] = "Costco" then "Chain" else [Type]Alternatively, you could create an own mapping table according to your definition and then you merge the mapping table with your large table on CompanyName.
Let me know if this helps! 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/Thanks! This look like it might work. I will try in a second 🙂
Will this work for approximate matches too? Like lets say I have Publix #1 - Publix #100thanks!
Hi park ,
The above code won't work with approximate matches. Try this one instead:
Here the code:
if Text.Contains([CompanyName], "Publix") or Text.Contains([CompanyName], "Costco") then "Chain" else [Type]
Hope this helps 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
