topic Re: Extract part of text string in DAX Commands and Tips

Extract part of text string

khisla — Tue, 15 Jul 2025 08:34:33 GMT

I have a column with a text sting that I need to extract the store number

Test String	Expected Result
232 אאוטלט מול הריםנוף הגליל IL213	IL213
232 אאוטלט מול הריםנוף הגליל IL213	IL213
227 אדידס אאוטלט ביג ירכא IL208	IL208
235 IL216 זכרון יעקב	IL216
235 IL216 זכרון יעקב	IL216
250 אאוטלט חוצות המפרץ חיפה IL225	IL225
222 אדידס אאוטלט בילו IL204	IL204

Re: Extract part of text string

FBergamaschi — Tue, 15 Jul 2025 08:36:59 GMT

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

Re: Extract part of text string

khisla — Tue, 15 Jul 2025 09:23:58 GMT

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]?

Re: Extract part of text string

FBergamaschi — Tue, 15 Jul 2025 09:37:15 GMT

OK, sorry I thought it was always at the end

Table[String] is the column from where you need to extract the store

In this case:

Store Number =

VAR pos = FIND ( "IL", Table[String] )

RETURN MID ( Table[String], pos, 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

Re: Extract part of text string

khisla — Tue, 15 Jul 2025 10:53:37 GMT

Did not work.

I have this Text.Start(Text.AfterDelimiter([סניף],"IL"),3) which worked however need to also include the IL portion. Any idea?

Re: Extract part of text string

FBergamaschi — Tue, 15 Jul 2025 11:12:48 GMT

What you are using is an M functon (Power Query)

This is the DAX section of the forum so I suggested a DAX formula for a calculated column, please try that and if it works marks this as a solution.

If you need a Power Query solution, pls post there so you get also a PQeury solution

Thanks

Re: Extract part of text string

EricVieira — Tue, 15 Jul 2025 16:28:07 GMT

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)

Re: Extract part of text string

techies — Tue, 15 Jul 2025 16:51:34 GMT

Hi khisla please try this custom column in power query editor

= if Text.Contains([Test String], "IL")
then Text.Middle([Test String], Text.PositionOf([Test String], "IL"), 5)
else null

Re: Extract part of text string

khisla — Wed, 16 Jul 2025 04:22:59 GMT

Got error

Re: Extract part of text string

techies — Wed, 16 Jul 2025 05:09:24 GMT

Hi khisla please check this

Re: Extract part of text string

v-tejrama — Wed, 16 Jul 2025 07:21:54 GMT

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

Re: Extract part of text string

khisla — Wed, 16 Jul 2025 08:23:27 GMT

Thanks!!! This worked perfectly.

Re: Extract part of text string

khisla — Sat, 26 Jul 2025 13:10:04 GMT

I used this for another purpose but am getting an error message for cells which do not include a value or value if null. How can I adjust accordingly?

est String	Expected Result
232 אאוטלט מול הריםנוף הגליל IL213	IL213
null	0
227 אדידס אאוטלט ביג ירכא IL208	IL208
235 IL216 זכרון יעקב	IL216
235 IL216 זכרון יעקב	IL216
250 אאוטלט חוצות המפרץ חיפה IL225	IL225
222 אדידס אאוטלט בילו IL204	IL204

Re: Extract part of text string

khisla — Sat, 26 Jul 2025 13:21:49 GMT

I used this for another purpose but I am getting an error message when the cells does not contain a value or is null. How can I adjust this accordingly for a null value scenario.

est String	Expected Result
232 אאוטלט מול הריםנוף הגליל IL213	IL213
null	0
227 אדידס אאוטלט ביג ירכא IL208	IL208
235 IL216 זכרון יעקב	IL216
235 IL216 זכרון יעקב	IL216
250 אאוטלט חוצות המפרץ חיפה IL225	IL225
222 אדידס אאוטלט בילו IL204	IL204