Extract part of text string power query

[khisla]

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 אאוטלט מול הריםנוף הגליל IL213	IL213
232 אאוטלט מול הריםנוף הגליל IL213	IL213
227 אדידס אאוטלט ביג ירכא IL208	IL208
235 IL216 זכרון יעקב	IL216
235 IL216 זכרון יעקב	IL216
250 אאוטלט חוצות המפרץ חיפה IL225	IL225
222 אדידס אאוטלט בילו IL204	IL204

[v-nmadadi-msft]

Hi @khisla 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

[khisla]

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

[ronrsnfld]

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]

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

[ronrsnfld]

This assumes that any word that begins with IL represents a store number.

The store number can be any number of characters.

• Split the Test String on the space.
• Find the item that has IL
• Extract that list item

(Depending on your actual data, the algorithm may need to be tweaked a bit)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pU5bDsIwDLtK1W8+RsaAKyBxg6l32hNWUcYbKi7k6+BM+9oHQkJVKseJHee5lVQMCr4WHQI6g54wGDSI8DjizPambcWx52SzlXlq3exPraxUW5OucZnYlGQrwy/igEJVyXq8mA0eS4MdR5GSk+5d8UL560aWTM7tCZ+st0btcafqoawnbNRNsjGzfM8c0A5hF9a5Dw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Test String" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Test String", type text}}),
    
    #"Add Store Number" = Table.AddColumn(#"Changed Type","Store Number", each 
        [a=Text.Split([Test String]," "),
         b=List.FindText(a,"IL"){0}][b], type text)
in
    #"Add Store Number"

[MasonMA]

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

[Abhilash_P]

Hi @khisla ,

You can create a custom column by using below power query 

Text.Middle([Test String], Text.PositionOf([Test String], "IL"), 5)


Below are the Power Query steps for your reference


let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rU9JDsIwEPtKlHMPJV3gC0j8oMqfukIjSil7xYf8HTxNTz0gIaEomcVjZ5xl2kRGIedp0KJDq9Az7RRqjHA4YWB5k7Ik7Ihsd2YV6UD7aIM/aZi1aFSEK1wWcgW7peIz4ohc2OHGsxn9BsmkmSrsOTKSepb5K14o5n/SXyeTcLHGgemT9y1WetzJfkjXMa1F1SRegdF7Mt89dWgmM/FsJtbWfgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Test String" = _t, #"Expected Result" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Test String", type text}, {"Expected Result", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Middle([Test String], Text.PositionOf([Test String], "IL"), 5)),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Expected Result"})
in
#"Removed Columns"