Extract Dates from longer text string through Power Query

[shaikhrulez]

Hello everyone,

I have come across somewhat similar problem as mentioned in this post with longer text length and different date formats which is manually entered, need null where there is no date. I tried to copy amazing solution provided by @Chihiro in my scenario but it didn't work perhaps due to my limited knowledge of Power Query.

Could anyone please look into the file attached and assist me in extracting dates from last comment column. The actual data contains more than 100K rows.

Thanks.

 

[Khalid NGO]

Hi Waqar, I hope you will find PQ solution, I am sharing formula seems to be working on posted samples:

=IFERROR(IFERROR(IFERROR(MID(B2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},B2&"0123456789")),10)+0,SUBSTITUTE(MID(B2,SEARCH("?-?-????",B2),10),".","")+0),SUBSTITUTE(MID(B2,SEARCH("202",B2)-8,10),".","-")+0),"")

Regards,

 

[GraH - Guido]

Based on the sample given.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Set_cols_as_txt = Table.TransformColumnTypes(Source,{{"TicketNumber", Int64.Type}, {"LastComment", type text}}),
    Get_date = Table.AddColumn(Set_cols_as_txt, "Date", each
                try
                List.Transform(
                                List.Select(Text.Split([LastComment], " "), each
                                            List.Count( Text.SplitAny(_, "-.")) =3
                                )
                            , each Date.FromText(_)
                        ){0}
                otherwise null, type date)
in
    Get_date

 

[shaikhrulez]

Hi Waqar, I hope you will find PQ solution, I am sharing formula seems to be working on posted samples:

=IFERROR(IFERROR(IFERROR(MID(B2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},B2&"0123456789")),10)+0,SUBSTITUTE(MID(B2,SEARCH("?-?-????",B2),10),".","")+0),SUBSTITUTE(MID(B2,SEARCH("202",B2)-8,10),".","-")+0),"")

Regards,

Thanks Khalid for your response, but unfortunately this didn't work with complete data set may be because there are many different date formats used by person entering dates. Here is the screenshot of filter which shows incorrect years..

 

[shaikhrulez]

Based on the sample given.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Set_cols_as_txt = Table.TransformColumnTypes(Source,{{"TicketNumber", Int64.Type}, {"LastComment", type text}}),
    Get_date = Table.AddColumn(Set_cols_as_txt, "Date", each
                try
                List.Transform(
                                List.Select(Text.Split([LastComment], " "), each
                                            List.Count( Text.SplitAny(_, "-.")) =3
                                )
                            , each Date.FromText(_)
                        ){0}
                otherwise null, type date)
in
    Get_date

Thanks @GraH - Guido for your solution, but unfortunately this didn't work either on my complete data set, it is giving wrong results and sometimes swapping date with months and vice versa. Have a look at the result when I applied it to around 20k record. The query has also converted non-dates values to dates which is incorrect..