Extract date from parenthesis

[modwonka]

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!

[Jakinta · Accepted solution]

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} )

[Jakinta · Accepted solution]

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]

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 · Accepted solution]

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]

Hi Kelly, 

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

Cheers!