Forum Discussion

khisla's avatar
khisla
Helper 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

     

     

Abhilash_P's avatar
Abhilash_P
Super User
1 year ago

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"