power query advanced editor using m-code transforms dates incorrectely

[breilly00]

I have the following code that is not keeping the same date format as the input. The input dates are in a date column and have mm/dd/yyyy formatting in the cell (e. g. 5/7/2019). The result after running the following m-code is 07/05/2019 in a general format. How can I get the output dates to be the same as the input dates using the following code?


let
Source = Excel.Workbook(File.Contents("C:\Users\Sadie\MyDocuments\Excel\ExcelPowerBuilder\pb-v3-test.xlsm"), null, true),
Schedule_Sheet = Source{[Item="Schedule",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Schedule_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Time", type datetime}, {"Courts", Int64.Type}, {"Teams", type text}})
in
#"Changed Type"

 

[alansidman]

Have you checked your regional settings to ensure that they are what you want them to be.

 

[breilly00]

Yes, have went to windows 10 change date, tome or number formats and see the following.
Region is set to 'match windows display language'
short date is set to M/d/yyyy
example shows 7/26/2019

 

[sandy666]

also check Regional Settings in PQ Editor
File - Options and Settings - Query Options - Regional Settings - Locale (set to English (United States)

 

[breilly00]

OMG OMG .

I was using GB english instead of US english and they have dd/mm/yy and the US has mm/dd/yy. Such as simple solution, for me to overlook. But, the solution is always simple when someone else solves it for you.

My many thanks and appreciation for your excellent help. YOU ROCK !!!!

 

[sandy666]

You are welcome

Have a nice day