Forum Discussion

Richard_Bowmaker's avatar
Richard_Bowmaker
Copper Contributor
Aug 10, 2025

PowerQuery tranforming date from different locale stopped working?

I sometimes have to import CSV files from the Philippines, which contain date/time values with a M/DD/YYYY format. I'm not in the Philippines or US, so my default date format is D/MM/YYYY. Just opening the CSV file in Excel would result in errors because Excel would mix up the month and day values.

The solution used to be to use Data->Get Data->From Text/CSV in excel, which would open the file in PowerQuery. I could then select the datetime column, right-click and choose Change Type->Using Locale... and select Date/Time and "English United States" and then import the data. This used to work, but today it has stopped working:

For example, in this file the first line of data contains a date/time in 13 Jan 2025. Here's the raw data in a text file:

However, when I follow the above steps, this date time now just come out as "Error" in PQ:

If I import this into Excel, the cells where the day is greater than 12 are blank, and in the others the day and month are still reversed.

Is this a bug, or am I doing something wrong?

BI & Data Analysis
excel

3 Replies

Sort By
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Aug 12, 2025

    I can't reproduce that

    let
    Source = Csv.Document( File.Contents("G:\Downloads\test.csv"),
        [Delimiter=",", Columns=4, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    PromoteHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    DeclareType = Table.TransformColumnTypes( PromoteHeaders,
        {
            {"RUN_TIME", type date},
            {"TIME_INTERVAL", type datetime}
        }, "en-US")
in
    DeclareType

    Perhaps you may share csv file

  • Kidd_Ip's avatar
    Kidd_Ip
    MVP
    Aug 12, 2025

    Take this:

     

    1. Remove the Auto-Generated “Changed Type” Step
      In Power Query Editor, delete the first “Changed Type” step that Power Query adds automatically. This step assumes your system locale and causes misinterpretation.
    2. Manually Apply Locale Conversion
      • Select the date column(s).
      • Right-click → Change Type → Using Locale…
      • Choose Data Type: Date/Time and Locale: English (United States).
    3. Reapply Data Types for Other Columns
      Use the column header icons to set types for non-date columns.
    4. Check for Numeric Locale Conflicts
      If your data includes numbers with commas or periods, apply “Change Type Using Locale” to those columns too.

Resources