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

     

     

MasonMA's avatar
MasonMA
Icon for Super User rankSuper User
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