Forum Discussion

ranz_vincent's avatar
ranz_vincent
Icon for Helper I rankHelper I
1 year ago
Solved

Having problems with multiple date formats after last refresh

Hello I suddenly encounter a problem with date format after my last data refresh and I have been using this model since January 2025 and now all of a sudden my date columnns are saying that there are...
Need Help
  • danextian's avatar
    danextian
    1 year ago

    Hi ranz_vincent 

    When changing the data types, especially at the very beginning, always ensure that the culture  is specified. Also, if you have any transformations that combines the date with another column, specify the culture as well. 

    en-AU and en-GB follows dd/mm/yy while en-US follows mm/dd/yy.

Nasif_Azam's avatar
Nasif_Azam
Icon for Super User rankSuper User
1 year ago

Hey ranz_vincent ,

It sounds like you're dealing with inconsistent date formats in Power BI (via Power Query) after editing your CSV files.

Likely Causes After Editing

  1. CSV Saved with Different Locale Settings:

    • If you edited the file in Excel or a text editor and saved it, the system might've applied a different locale (e.g., en-US vs en-GB), affecting the date format.

  2. Structural Changes:

    • A different column header or new blank rows at the top.

    • Excel sometimes adds invisible BOM (Byte Order Marks) or other encodings.

  3. Mixed Date Delimiters:

    • E.g., one file uses dd-mm-yyyy and another dd/mm/yyyy.

Fixes & Recommendations

1. Force the Format using Locale when Converting Dates

Even if you've tried this, it's important to do it after expanding the column in the final query.

  • In Power Query:

    • Go to the step where dates appear as text.

    • Select the date column → Transform > Data Type > Using Locale.

    • Set Data Type: Date, Locale: English (United Kingdom) (or your correct one).

2. Edit the Auto-generated Function

Power Query generates a function like Transform Sample File. Do this:

  • Go to Transform Sample File query.

  • Find the step where date columns are parsed (usually something like Changed Type).

  • Delete or adjust the Changed Type step.

  • Manually transform each column using Using Locale.

3. Use a Clean Sample File

Ensure your sample file used to build the Transform Sample File represents the structure and format of all the files.

4. Pre-process Your CSVs

If possible, before loading into Power BI:

  • Open each CSV in Notepad or Excel.

  • Ensure consistent date delimiters and no extra header/footer rows.

  • Save all in the same encoding, ideally UTF-8 without BOM.

5. Add a Manual Date Parser

As a last resort, parse dates manually:

= Table.AddColumn(#"Previous Step", "Clean Date", each Date.FromText(Text.Replace([RawDateColumn], "/", "-")), type date)

 

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.


Best Regards,
Nasif Azam