Reply
ChrisAZ
Helper V
Helper V
Partially syndicated - Outbound

Trying to extract Town name

I am wanting to get the Town name out of each of these rows in this column, and not sure the best way to do it. For example, first seven would be WHITECOURT, the 8th would be HIGH PRAIRIE, the ones with (DEF Units:xxx) would wind up with just the Town name. I had posted yesterday and it sounded like maybe I need to have a table made that contains possible towns to do this? Can anyone explain this in further detail on what would be needed?

ChrisAZ_0-1742934457162.jpeg

 

 

2 ACCEPTED SOLUTIONS
Greg_Deckler
Super User
Super User

Syndicated - Outbound

@ChrisAZ Can you post as text data so that we can mock this up? You could possibly use Fuzzy matching in PQ or DAX but there *may* be a way to parse it out if there is a pattern of some sort.

https://community.fabric.microsoft.com/t5/Quick-Measures-Gallery/Fuzzy/m-p/1352914#M608



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

Syndicated - Outbound

no worries, just go to the merged column, click the expand icon, select the Town column, and click OK to add it to your table.

― Power BI | Microsoft Fabric | PL-300 | DP-600 | Blog: medium.com/@cseprs_54978

View solution in original post

20 REPLIES 20
v-csrikanth
Community Support
Community Support

Syndicated - Outbound

Hi @ChrisAZ 
You can use Power query or DAX measure to solve this-

For Power Query you can follow these steps.

1.You can go to query editor under transform data and use this code-

***************************************************

let
    RemoveSpecialChars = Text.Remove([YourColumn], {"-", "@", "#", "0".."9"}),
    RemoveAB = Text.Replace(RemoveSpecialChars, " AB", ""),
    CleanedText = Text.Trim(RemoveAB)
in
    CleanedText

 

2. Using DAX

You can use this DAX under calculated column

CleanedName = 
VAR Step1 = SUBSTITUTE('Table'[Column], "-", "")
VAR Step2 = SUBSTITUTE(Step1, "@", "")
VAR Step3 = SUBSTITUTE(Step2, "#", "")
VAR Step4 = SUBSTITUTE(Step3, " AB", "")  -- Remove the "AB" abbreviation
VAR FinalResult = TRIM(Step4)  -- Clean up extra spaces
RETURN FinalResult

***************************************************

You can modify the results as per your convenience.
Hope this gives you an idea about the work around!


If the above information helps you, please give us a Kudos and marked the Accept as a solution.
Best Regards,
Community Support Team _ C Srikanth.

Syndicated - Outbound

Sorry, unclear how/where I would enter the syntax you indicated?

techies
Solution Sage
Solution Sage

Syndicated - Outbound

Hi @ChrisAZ Using a reference table is better. However, if you prefer to use Power Query Editor, you can achieve this by using extract option text before delimiter, replacing unnecessary values with empty strings, applying trim, and other transformations as needed

― Power BI | Microsoft Fabric | PL-300 | DP-600 | Blog: medium.com/@cseprs_54978

Syndicated - Outbound

Unclear how I would set these though given some town names are more than one word, etc.

 

Syndicated - Outbound

Hi @ChrisAZ you can use a reference table for merging queries, open Merge Queries in Power Query and enable Fuzzy Matching. Adjust the Similarity Threshold to 0.6 or 0.7 for better matches. Additionally, check the options to Ignore Case and Ignore Spaces to improve accuracy.

― Power BI | Microsoft Fabric | PL-300 | DP-600 | Blog: medium.com/@cseprs_54978

Syndicated - Outbound

So I guess this comes back to the question on whether a reference table and this sort of approach is best or if there is a command such as someone else proposed above that would work to be able to extract it from the list? If I use a reference table is it just a single column Reference table that has town names?

Syndicated - Outbound

Yes, if you use a reference table, it would be a single-column table containing all possible town names. Then, you can use fuzzy matching in Power Query to match the town names from your main dataset.

― Power BI | Microsoft Fabric | PL-300 | DP-600 | Blog: medium.com/@cseprs_54978

Syndicated - Outbound

Doesn't look very promising:

ChrisAZ_0-1743023501685.png

 

Syndicated - Outbound

adjust the threshold

― Power BI | Microsoft Fabric | PL-300 | DP-600 | Blog: medium.com/@cseprs_54978

Syndicated - Outbound

I put in the value you suggested and it stayed the same. What threshold would be better?

Syndicated - Outbound

I tried it with .1 but where does the info end up?

Syndicated - Outbound

?

― Power BI | Microsoft Fabric | PL-300 | DP-600 | Blog: medium.com/@cseprs_54978

Syndicated - Outbound

Meaning after I apply it, where do I find the data? Sorry new to this and never used this command before. I was hoping it would appear as as column but I can't find it

 

Syndicated - Outbound

no worries, just go to the merged column, click the expand icon, select the Town column, and click OK to add it to your table.

― Power BI | Microsoft Fabric | PL-300 | DP-600 | Blog: medium.com/@cseprs_54978

Syndicated - Outbound

That worked. Thank you so much!

Syndicated - Outbound

you're welcome 🙂

― Power BI | Microsoft Fabric | PL-300 | DP-600 | Blog: medium.com/@cseprs_54978
Greg_Deckler
Super User
Super User

Syndicated - Outbound

@ChrisAZ Can you post as text data so that we can mock this up? You could possibly use Fuzzy matching in PQ or DAX but there *may* be a way to parse it out if there is a pattern of some sort.

https://community.fabric.microsoft.com/t5/Quick-Measures-Gallery/Fuzzy/m-p/1352914#M608



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Syndicated - Outbound

Sorry, so be easier if just shared my PBIX?

Syndicated - Outbound

@ChrisAZ That works too. 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

avatar user

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)