Extract part of text string

[khisla]

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

[techies · Accepted solution]

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

[v-tejrama]

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

[techies · Accepted solution]

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

[khisla]

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

[khisla]

Got error

[techies · Accepted solution]

Hi @khisla please check this

[khisla]

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

[khisla]

Thanks!!! This worked perfectly.

[EricVieira]

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)

[khisla]

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

[FBergamaschi]

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

[khisla]

Did not work.  

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

[FBergamaschi]

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

[FBergamaschi]

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