Standardize Date formats

fb7894
Posts: 16
Joined: Thu Aug 16, 2018 11:18 pm

Standardize Date formats

Postby fb7894 » Sat May 25, 2019 2:28 am

Here is a tough one. I have a column of data that contain dates embedded in text. The dates come in no standard format.

Sample Data
Today's date is 05/24/2019
23-May-2019 was yesterday
Tomorrow will be 25/May/2019
the day after is 5-6-2019
and then 5-7-19 happens


Is there a PQ formula I can use to extract the dates from each cell?

Thanks!!

Rudi
Posts: 16
Joined: Fri Sep 01, 2017 4:33 pm

Re: Standardize Date formats

Postby Rudi » Tue May 28, 2019 8:13 pm

This custom function appears to work on your sampling.

I copied your sampling into Excel and loaded it into PQ
I copied the code for the custom function into a new blank query
Back in the sampling query, I invoked a custom function, selected the function and pointed it to the column containing the data
The function spat out the appropriate dates into the new column

{L_CODE}{L_COLON} {L_SELECT_ALL_CODE}

(myStr) =>
let
    myStr = Text.Replace(myStr, "- ", " "),
    Split = Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv)(myStr),
    Convert = List.Generate(
                            ()=> [Date=null, x=-1],
                            each [x]<List.Count(Split),
                            each [Date= try Date.From(Split{x}) otherwise null, x=[x]+1],
                            each [Date]),
    myDate = List.RemoveNulls(Convert){0}
in
    myDate


It does hook up on the last item where it gets the year swapped with the day or month; this is unavoidable as no-one will know what that format represents regarding day or month or year!

Credit for the function to: Chihiro at Chandoo.org in this thread

fb7894
Posts: 16
Joined: Thu Aug 16, 2018 11:18 pm

Re: Standardize Date formats

Postby fb7894 » Sat Jun 01, 2019 2:43 am

thank you!

fb7894
Posts: 16
Joined: Thu Aug 16, 2018 11:18 pm

Re: Standardize Date formats

Postby fb7894 » Wed Jul 10, 2019 3:53 am

Late addition.... what if I have a line of data like this....

This line is for June 4, 2019

Can I update the query to handle this format? The challenge is the spaces within the date.

Rudi
Posts: 16
Joined: Fri Sep 01, 2017 4:33 pm

Re: Standardize Date formats

Postby Rudi » Thu Jul 11, 2019 3:38 pm

That could be a problem. It would become very difficult to distinguish normal text from dates if they have spaces.

I'm not saying its impossible, but its beyond my ability to assist. If you don't get additional help from this forum, try posting in other PQ forums on the web and link to this post to try attract more help from others who are experts in the field.

Good luck... :)


Return to “Power Query”

Who is online

Users browsing this forum: No registered users and 1 guest