Extract part of text string

Hi khisla ,

Thanks for reaching out to the Microsoft fabric community forum.

You can do this easily in Power BI Desktop without writing any M code. First, I loaded my data into Power BI, where I had a column called "Test String" containing long texts like "Adidas Outlet IL208" or "Zichron Yaakov IL216". Then, I went to the Data view, clicked on Modeling > New Column, and wrote this simple DAX:

Here is the DAX  used:

StoreNumber =
VAR StartPos = SEARCH("IL", 'YourTableName'[Test String], 1, -1)
RETURN IF(StartPos > 0, MID('YourTableName'[Test String], StartPos, 5), BLANK())

This formula searches for the position of "IL" and pulls the next 5 characters (like "IL216"). Once the column was created, I went to Report view and added a Table visual where I placed both Test String and the new StoreNumber column. This helped me clearly see whether the store number was extracted correctly. You can also use a Bar chart or Matrix visual if you want to count how many times each store number appears. For easy filtering, I also added a Slicer with StoreNumber. Everything worked smoothly using only DAX, no Power Query needed.

Please find the attached pbix file for your reference.

Best Regards,
Tejaswi.
Community Support