Different date formats in the same column

[Syndicate_Admin]

The source source is folders with csv files. There are many! 1 CSV file per day, since 2020.

It happens that some of those files have the date column written in these 2 ways:

• Some are written like this: month-day-year
• and others are like this: day-month-year

The correct format that PBI detects when converting the column as a data type "Date", is: month-day-year.

So when I put the column as the date data type, it throws me errors when I load.

For example : there are dates that the source files are written like this:

• 13/11/2022 -> this date should be interpreted by PBI as 13 Nov 2022. But PBI reads it as month-day-year, that is: 13(month)- 11(day)-2022(year), and this is where it obviously shows the error, because 13 is not a month.
• there may be other dates where maybe it is not an error, because they are written like this: 12/11/2022, where the correct thing is that PBI interprets it as November 12, 2022, but it is reading it as with December 11, 2022.

How can I do this transformation, considering that in the same column, the dates are written in 2 ways?

Thanks in advance.

The dataset is very large, and you could not change it in the original files.

[Syndicate_Admin]

Hello.

This is the case. I share image and PBI file.

In the source I only have 2 dates with months of November and December, only written both ways.

Imagine I'm connected to over 1,000 csv files, and it would be tedious to go find each of the csv files and change the shape from there.

When loaded into the model, it reads like this:

Here you can download the PBIX:

https://drive.google.com/file/d/14csUOFvoyZAmZVS0uWOAs6AaTtatqs_y/view?usp=sharing

[FreemanZ]

hi @Syndicate_Admin 

try to identify the files with different data format and change their Locale.

check this

https://support.microsoft.com/en-us/office/set-a-locale-or-region-for-data-power-query-d42b9390-1fff...

[amitchandak]

@Syndicate_Admin , for this we have to check and the format each row. Like check if there is

following functions can help

Power Query - Text.Contains: https://youtube.com/watch?v=Hs2q6qcrdgU&feature=share

Power Query - Text.Start, Text.Middle, Text.End: https://www.youtube.com/watch?v=vky4wPqm0O0

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.