Direct Query- Replace a piece of string if certain conditions met from another column

[Simonne]

Hi Power BI community! Newby here, and I need help! 
I've got a direct query project started and I need to replace a component of text in one column if conditions are met from another column.

Example. If column1 is Alberta OR British Columbia, a portion of Column 2 string needs to change: from C1 to C2. I need the rest of string to stay the same.

Before:                

Column 1	Column 2
Alberta	C1Triangle
Alberta	C1Circle
Quebec	C1Circle
British Columbia	C1Square
Newfoundland	C2Circle

After:                

Column 1	Column 2
Alberta	C2Triangle
Alberta	C2Circle
Quebec	C1Circle
British Columbia	C2Square
Newfoundland	C2Circle

Thank you!!!

[Simonne]

Ok I got it! Thank you both!!! 

[uzuntasgokberk · Accepted solution]

Hello @Simonne ,

I prefer to choose Power Query transformations for best practice. So transform data(power query) --> Add column --> Custom Column:
if [Column]="Alberta" or [Column]="Columbia" then Text.Replace([Column],"C1","C2") else [Column]

Kind Regards,
Gökberk Uzuntaş

📌 If this post helps, then please consider Accepting it as a solution and giving Kudos — it helps other members find answers faster!

🔗 Stay Connected:
📘 Medium |
📺 YouTube |
💼 LinkedIn |
📷 Instagram |
🐦 X |
👽 Reddit |
🌐 Website |
🎵 TikTok |

[Simonne]

Perfect, it worked thank you!!

[muhammad_786_1]

Hi @Simonne 

You can create a new column by using this DAX:

Upd_Column2 = 
IF(
    'Table'[Column1] = "Alberta" || 'Table'[Column1] = "British Columbia",
    SUBSTITUTE('Table'[Column2], "C1", "C2"),
    'Table'[Column2]
)

Best Regards,
Muhammad Yousaf

If this post helps, then please consider "Accept it as the solution" to help the other members find it more quickly.

LinkedIn

[Simonne]

Thanks @muhammad_786_1 !

I tried using add custom column in queries.. is that wrong?
Here's what I see:

[muhammad_786_1]

Hi @Simonne 

You just need to select Table View, then select New Column, and paste the DAX formula there. This will create a new calculated column.

However, if you prefer using Power Query, you can achieve the same result by adding a Custom Column directly in the Power Query Editor.

Best Regards,
Muhammad Yousaf

If this post helps, then please consider "Accept it as the solution" to help the other members find it more quickly.

LinkedIn