date format retrieved from Google Sheet

[primolee]

Hello everyone,

I am retrieving data from Google Sheet and the date format is MM/DD/YYYY.

However, Power Query got some date in the wrong format right in the beginning when expanding the table.

Original:

09/30/2021

10/01/2021

10/02/2021

.......

10/10/2021

10/11/2021

10/12/2021

10/13/2021

But Power Query makes it like:

09/30/2021

1/10/2021

2/10/2021

.......

10/10/2021

11/10/2021

12/10/2021

10/13/2021

Only dates between 2021/10/1 and 2021/10/12 are formatted wrong, and become correct on the 13th maybe because there is no 13th month.

Because this happens right in the beginning when expanding the sheet, I cannot fix it through changing data format.

Is there a smart way of fixing it?  Thank you!

Best regards,

David

[Syndicate_Admin]

Hi @primolee ,

You can try to use Using Locale to change the date format to en-US to modify:

= Table.TransformColumnTypes(#"Changed Type", {{"Soak Start Date", type date}}, "en-US")

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yingjl · Accepted solution]

Hi @primolee ,

You can try to use Using Locale to change the date format to en-US to modify:

= Table.TransformColumnTypes(#"Changed Type", {{"Soak Start Date", type date}}, "en-US")

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

what's the column format at tha source? Can you influence that?  Can you use ISO8601 dates instead?