Forum Discussion
date format retrieved from Google Sheet
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
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.
3 Replies
what's the column format at tha source? Can you influence that? Can you use ISO8601 dates instead?
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.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.
