Having problems with multiple date formats after last refresh

[ranz_vincent]

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 some cells that contains wrong date formats and now I can't refresh my data. I tried using change data type using locale but it is still showing errors. My source file is a folder containing multiple csv files and I've also checked my source file and all of them are shown in dd mm yyyy format but when checking thru power query there are multiple date formats. Then I tried making a new model with fewer data and everything was fine but after cleaning some minor erros from source file suddenly after refresh, date column have a lot of errors and I noticed that those errors came from the csv files that I made some changes. Source File Data 

[danextian · Accepted solution]

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.

[v-kpoloju-msft]

Hi @ranz_vincent,

Thank you for reaching out to the Microsoft fabric community forum. Also thanks @ajaybabuinturi, @Nasif_Azam, for those valuable insights on this issue. I have identified few workarounds that may assist in resolving the thread.

Based on your description, it appears the problem arises from inconsistent date formats across your CSV files, likely introduced after recent edits. Power BI infers column types using the first file in your folder source, and any structural or formatting differences in other files can result in refresh errors, particularly with date parsing.

Please check all CSV files in the folder. Ensure they all use the same delimiter (comma, semicolon, etc.). Confirm that the date columns are in the same format, such as dd/mm/yyyy. When saving CSVs from Excel, avoid incompatible regional formats—save as CSV UTF-8 (Comma delimited) if using Excel.

Update Power Query Steps: Open Power Query Editor. In the Transform Sample File query: Remove or modify any automatic "Changed Type" step. Apply Change Type Using Locale instead:

Table.TransformColumnTypes(#"Previous Step", {{"YourDateColumn", type date}}, "en-GB")

Use "en-GB" for dd/mm/yyyy format

To disable Auto-Type Detection, navigate to File > Options > Data Load and uncheck “Automatically detect column types and headers for unstructured sources.” If necessary, rebuild the Sample File Logic. If the issue continues, consider recreating the folder query using a verified CSV sample and then apply consistent transformation logic to all files.

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[v-kpoloju-msft]

Hi @ranz_vincent,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-kpoloju-msft]

Hi @ranz_vincent,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[Nasif_Azam]

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

[ajaybabuinturi]

Hi @ranz_vincent,

I would like to let you know that i was also faced samekind of issue sometime ago. Sometimes the date shows dd/mm/yyyy in csv/excel files but it's not a date. There is a way to debug the errors. 

1. Identify the error row/s and just click on the erorr it will show what's inside the error at bottom of the query editor like below

2. Identify the data source file name for that specific error and go through that.

3. Check that data, is it in date format/date data type in csv/excel file. If the date is in genral or someotther format just chnage it.

Let me knwo if you have any questions.

Thanks,
If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.

[ranz_vincent]

the error that shows are formatted like this 13 01 2025 but in my csv files all my date columns are formatted as date and shown like that but when i try to check and change the first transformation of date columns to text in power query it shows multiple date formats some are yyyy-dd-mm,dd/mm/yyyy,dd MMMM yyyy is this normal? by the way my system region and pbi region are set to english philippines

[ajaybabuinturi]

Hi @ranz_vincent,

YYYY-DD-MM, DD-MM-YYYY and DD MMMM YYYY all are normal as far as I know, one of my dataset having same formats. Also please let me know on what day/date data refresh you got errors.

Could you please provide the .pbix file for sample data of errors and let me know which source file it is. So that I will provide exact solution.

Thanks,
If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.