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 (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)"

 

How can I extract the date complete to create a new column from TEXT_EXAMPLE3? Which is the formula?

 

Thanks again for your time and help.

 

Cheers!

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!

     

4 Replies

  • 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!