Changing value from one column to another in PowerBi

[SharonO]

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 Fred	Mr Jones

[vivien57]

Hello @SharonO ,

Can you elaborate? I don't understand what you want to do.

Have a nice day,

Vivien

[SharonO]

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 · Accepted solution]

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

[SharonO]

Hi Vivien

Thank you!