Forum Discussion
Solved
Changing value from one column to another in PowerBi
Hi everyone Please can you help in changing a column value to another one Account Referrer Referrer needs to be 123456-1 Mr Jones Mr Jones 123456-2 Mr Smith Mr Jones 123456-3 Mr ...
Ok, it's clear
Steps in Power BI (Power Query):
- Split the account into:
- AccountPrefix = part before the -
- AccountSuffix = part after the - (as a number)
- Filter rows where AccountSuffix = 1 to get the original referrer.
- Create a mapping table with AccountPrefix and Referrer.
- Merge this mapping table back into the full dataset using AccountPrefix.
- Replace Referrer:
- If AccountSuffix = 1, keep the original.
- Else, use the referrer from the merged table.
- Clean up temporary columns and apply changes.
Please feel free to give a kudo and validate my answer as a solution if it suits you.
Have a nice day,
Vivien- Split the account into:
Hi Vivien
I would like to change the referrer code for accounts 2 onwards to the same as was given in account 1
So account 123456 - 1 shows a referrer code of Mr Jones
Accounts 2 (123456 - 2) shows a referrer code of Mr Smith which i would like to change to Mr Jones
Effectively any account where the second number is >1 I would like to to have the same referrer code as the number 1 account.
Hope that clarifies
vivien57
Super User
Super UserOk, it's clear
Steps in Power BI (Power Query):
- Split the account into:
- AccountPrefix = part before the -
- AccountSuffix = part after the - (as a number)
- Filter rows where AccountSuffix = 1 to get the original referrer.
- Create a mapping table with AccountPrefix and Referrer.
- Merge this mapping table back into the full dataset using AccountPrefix.
- Replace Referrer:
- If AccountSuffix = 1, keep the original.
- Else, use the referrer from the merged table.
- Clean up temporary columns and apply changes.
Please feel free to give a kudo and validate my answer as a solution if it suits you.
Have a nice day,
Vivien
Hi Vivien
Thank you!