Forum Discussion

modwonka's avatar
modwonka
Helper I
4 years ago
Solved

Extract date from parenthesis

Hello everyone,   first of all, thanks for your help.   I have one column with a long part of the text in each row from which I would like to extract a date.   This is one row: "TEXT_EXAMPLE1 (...
Need Help
  • Jakinta's avatar
    Jakinta
    4 years ago

    If your desired date/time is always inbetween 3rd pair of parenthesis, you can add step below...

    = Table.AddColumn(PriorStepName, "Custom", each Text.BetweenDelimiters([TextColumnName], "(", ")", 2, 0))

     

    If not you might do it relative to "TEXT_EXAMPLE3" position...

    = Table.AddColumn(PriorStepName, "Custom", each let l=List.Transform( Text.Split([TextColumnName]," "), each Text.Remove(_,{"(",")",","})), i=List.PositionOf(l, "TEXT_EXAMPLE3") in l{i+1} & " " & l{i+2} )

     

  • v-kelly-msft's avatar
    v-kelly-msft
    4 years ago

    Hi  modwonka ,

     

    Previousstep refers to the previous applied step,not the column.

    Take below steps as example:

    Now if you wanna create a new step,then "Added Custom" is the previous step.

    Check my .pbix file attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my reply as a solution!

     

Jakinta's avatar
Jakinta
Solution Sage
4 years ago

If your desired date/time is always inbetween 3rd pair of parenthesis, you can add step below...

= Table.AddColumn(PriorStepName, "Custom", each Text.BetweenDelimiters([TextColumnName], "(", ")", 2, 0))

 

If not you might do it relative to "TEXT_EXAMPLE3" position...

= Table.AddColumn(PriorStepName, "Custom", each let l=List.Transform( Text.Split([TextColumnName]," "), each Text.Remove(_,{"(",")",","})), i=List.PositionOf(l, "TEXT_EXAMPLE3") in l{i+1} & " " & l{i+2} )

 

modwonka's avatar
modwonka
Helper I
4 years ago

Hi Jakinta,

 

thanks so much for your answer. I tried your formula, it seems they work, but i have a new error that i don't know how to fix it. 

 

i understand i have to change "PriorStepName" with the real name of my column, isn't it? The name is: "Tags (created on)".

 

So, i have this formula:

= Table.AddColumn([#"Tags (created on)"], "Custom", each let l=List.Transform( Text.Split([TextColumnName]," "), each Text.Remove(_,{"(",")",","})), i=List.PositionOf(l, "TEXT_EXAMPLE3") in l{i+1} & " " & l{i+2} )

And then, i have this error: 

 

"Expression.Error: It can't convert the valor "TEXT_EXAMPLE1..." to Table type. 

Details: 
Value=TEXT_EXAMPLE1 (2021-06-25 15:00), TEXT_EXAMPLE2 (2021-06-25 16:00), TEXT_EXAMPLE3 (2021-06-25 15:20), TEXT_EXAMPLE4 (2021-06-25 15:25)

Type=[Type]

 

Do you know how can i fix this? 

thanks! 

 

 

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    4 years ago

    Hi  modwonka ,

     

    Previousstep refers to the previous applied step,not the column.

    Take below steps as example:

    Now if you wanna create a new step,then "Added Custom" is the previous step.

    Check my .pbix file attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my reply as a solution!

     

    • modwonka's avatar
      modwonka
      Helper I
      4 years ago

      Hi Kelly, 

       

      thanks so much for your answer! It was really helpful. With your help and Jakinta I could do my task. 

       

      Cheers!