Extract part of text string

Create a calculate column

Store Number = RIGHT ( Table[String], 5 )

If this helped, please consider giving kudos and mark as a solution

me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

Not sure I understand the calculation.  The store number does not sit within the same text location in each row therefore not sure the 5 is the same for each row.  What do I enter for Table [String]?

Text.Middle(Text.Select([Test String], {"A".."Z", "0".."9"}), Text.PositionOfAny(Text.Select([Test String], {"A".."Z", "0".."9"}), {"IL"}), 5)


Text.Middle(Text.RegexReplace([Test String], ".*(IL\d{3}).*", "$1"), 0, 5)

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