Forum Discussion

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

Extract part of text string

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...
  • techies's avatar
    techies
    1 year ago

    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

  • techies's avatar
    techies
    1 year ago

    Hi khisla please check this

     

     

FBergamaschi's avatar
FBergamaschi
Icon for Super User rankSuper User
1 year ago

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:

Store Number = 
VAR pos = FIND ( "IL", Table[String] )
RETURN MID ( Table[String], pos, 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

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

Did not work.  

I have this Text.Start(Text.AfterDelimiter([סניף],"IL"),3) which worked however need to also include the IL portion.  Any idea?

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

    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