Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 |
Solved! Go to 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
Hi @khisla please check this
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
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
Got error
Hi @khisla please check this
Thanks!!! This worked perfectly.
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)
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]?
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:
If this helped, please consider giving kudos and mark as a solution
@me in replies or I'll lose your threadconsider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
Did not work.
I have this Text.Start(Text.AfterDelimiter([סניף],"IL"),3) which worked however need to also include the IL portion. Any idea?
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
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
User | Count |
---|---|
25 | |
11 | |
7 | |
6 | |
6 |
User | Count |
---|---|
30 | |
13 | |
11 | |
9 | |
6 |