- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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?
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

@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...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Sorry, unclear how/where I would enter the syntax you indicated?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Unclear how I would set these though given some town names are more than one word, etc.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Doesn't look very promising:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

adjust the threshold
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

I put in the value you suggested and it stayed the same. What threshold would be better?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

I tried it with .1 but where does the info end up?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

That worked. Thank you so much!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

you're welcome 🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

@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...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Sorry, so be easier if just shared my PBIX?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

@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...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Here it is:
It would be the Axon Fuel data set
https://drive.google.com/file/d/1IH5dXc52ndj-7Gs4BH_tkZ4EoOb6Q1AZ/view?usp=drive_link

Helpful resources
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.
Power BI Monthly Update - June 2025
Check out the June 2025 Power BI update to learn about new features.

Subject | Author | Posted | |
---|---|---|---|
03-24-2025 04:42 PM | |||
04-10-2025 10:20 AM | |||
06-23-2025 09:17 PM | |||
03-24-2025 01:30 AM | |||
Anonymous
| 01-12-2021 07:12 AM |
User | Count |
---|---|
57 | |
55 | |
55 | |
37 | |
30 |
User | Count |
---|---|
79 | |
66 | |
45 | |
44 | |
42 |