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

     

     

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

Not sure I understand the calculation.  The store number does not sit within the same text location in each row therefore not sure the 5 is the same for each row.  What do I enter for Table [String]?

 

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