Forum Discussion

khisla's avatar
khisla
Icon for Helper II rankHelper II
1 year ago
Solved

Extract part of text string power query

  I have a column with a text sting that I need to extract the store number Unfortunately, the store ID is not always in the same place   Test String Expected Result 232 אאוטלט מול הרי...
Need Help
  • MasonMA's avatar
    MasonMA
    1 year ago

    Hi khisla 

     

    Or, assuming your StoreIDs start with IL and if you'd like to extract them in Power BI, you can create a new Column with below DAX (You can also add other letters to search in case your StoreID start with other letters)

    StoreID = 
VAR _letterToSearch= "IL"
VAR _txt = [Test String]
VAR _startPos = SEARCH(_letterToSearch, _txt, 1, -1)
VAR _result =
    IF(
        _startPos > 0,
        MID(_txt, _startPos, 5),
        BLANK()
    )
RETURN
    _result

     

     

khisla's avatar
khisla
Icon for Helper II rankHelper II
1 year ago

None of the above has worked.

 

So I have data that includes employee hours worked every day.  I need to identify at which store location they clocked into.  Unfortunately, the store number does not sit in the same position within the text string for each line.

I need a power query solution since I then need to merge the store column with targets in order to calculate the sale commision. 

 

I can't split columns after the space since the store number (ILXXX) comes in within different columns for each row.

 

I have used the below however it is only giving me the number after IL.  However, I need to also include the IL as part of the store number.

 

Text.Start(Text.AfterDelimiter([סניף],"IL"),3)

 

Test StringExpected Result
232 אאוטלט מול הריםנוף הגליל IL213IL213
232 אאוטלט מול הריםנוף הגליל IL213IL213
227 אדידס אאוטלט ביג ירכא IL208IL208
235 IL216 זכרון יעקבIL216
235 IL216 זכרון יעקבIL216
250 אאוטלט חוצות המפרץ חיפה IL225IL225
222 אדידס אאוטלט בילו IL204IL204

 

  • ronrsnfld's avatar
    ronrsnfld
    Icon for Super User rankSuper User
    1 year ago

    Since what I posted obviously worked on the sample data you provided (which you can see from the screenshots in my answer), then either you adapted it to your real data incorrectly, or your sample data is not truly representative of your actual data.

     

    Please provide sample data that is truly representative.

     

    If your sample is truly representative, then you will need to show how you adapted my answer to your problem.

  • Abhilash_P's avatar
    Abhilash_P
    Icon for Super User rankSuper User
    1 year ago

    Hi khisla ,
    Have you tried with below one
    Text.Middle([Test String], Text.PositionOf([Test String], "IL"), 5)

    Also any possiblity can you share PBIX file you are working on..?