Forum Discussion

ThiagoDuarte's avatar
ThiagoDuarte
Icon for Helper I rankHelper I
5 years ago
Solved

Extract text between delimiters based on other column value (position)

Hello,

 

I need to extract a text between delimiter ( "," ) based on the second cloumn value. The text between delimiter may have any lenght.

 

The second column has the position value for the substring in the first column

 

Data:

 

I want to create a third column that should be:

 

Q2.7X2.7X0.4 (Second value from rci_optioncombo = position 1)

Não aplicável

NÃO

Não se aplica

Pêra Horizontal

 

I'm using Direct Query

 

I hope you guys can help me!

 

Thanks

Need Help
  • FrankAT's avatar
    FrankAT
    5 years ago

    Hi ThiagoDuarte ,

    with DAX you can do it like this:

     

     

    Extracted = PATHITEM(SUBSTITUTE(MIN('Table'[rc_optionscombo]),",","|"),MIN('Table'[rci_measured]))

     

    With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
    FrankAT (Proud to be a Datanaut)

3 Replies

  • samdthompson's avatar
    samdthompson
    Icon for Memorable Member rankMemorable Member
    5 years ago

    Hello, this is best done in power query:

     

    Home>>>Split Column>>>By Delimiter

     

     

  • FrankAT's avatar
    FrankAT
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hi ThiagoDuarte ,

    with DAX you can do it like this:

     

     

    Extracted = PATHITEM(SUBSTITUTE(MIN('Table'[rc_optionscombo]),",","|"),MIN('Table'[rci_measured]))

     

    With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
    FrankAT (Proud to be a Datanaut)

    • ThiagoDuarte's avatar
      ThiagoDuarte
      Icon for Helper I rankHelper I
      5 years ago

      It worked!

       

      I had to add a +1 after calling the second column to get it to work properly here

      Extracted = PATHITEM(SUBSTITUTE(MIN('Table'[rc_optionscombo]),",","|"),MIN('Table'[rci_measured])+1)

       

      Thank you!