PQ - Mixed date types in one column mm/dd/yyyy and dd/mmm/yyyy

[Patryk_PL_92]

Hello,

I pull data from Sharepoint folder which contains many CSV files, all files got same layout.

There is a problem with dates, as shown on below image.

Some dates are read by PQ correctly - as mm/dd/yyyy, but some are in incorrect format dd/mmm/yyyy.

How can I unify those dates, to have only the desired type which is mm/dd/yyyy?

I have no idea what approach can I take, what kind of transformation can I do to achieve that...

Initially this column is in "ANY" data type, when I change to "DATE/TIME" format then PQ throws an error, saying:
"We couln't parse the input provided as a DateTime value."

[Vijay_A_Verma]

Use this formula to convert your existing values to date format (Change Date Column appopriately)

= Date.From(Text.Select(Text.BeforeDelimiter([Date Column]," ",{0, RelativePosition.FromEnd}),{"0".."9","/","a".."z","A".."Z"," "}))

[Vijay_A_Verma · Accepted solution]

You need to put this formula in a custom column not in formula bar. If you want to put in formula bar, then use following formula where Previous step should be replaced with your previous step

= Table.AddColumn(#"Previous Step", "Custom", each Date.From(Text.Select(Text.BeforeDelimiter([Date Column]," ",{0, RelativePosition.FromEnd}),{"0".."9","/","a".."z","A".."Z"," "})))

[Patryk_PL_92]

Hi Vijay,

amazing formula, worked well for most of records 🙂

I found out that some dates are corrupted, so I needed to clean that up in the source.

Accepted as Solution.

[Patryk_PL_92]

Hello Vijay
thanks for response on that

I tried to use your formula, but it does not work for me or simply I do not know how to use it.

This is my column name and type:

And this is error I get when I use that formula - it seems that formula is not identifying [Execution Date] as a column name:

Any tips how to implement that correctly?