Converting Date Format

[Anonymous]

Hi

I've an excel file that has the date in the following format:

15.04.2022 (DD.MM.YYYY).

When I import to PowerBI, it assumes the date format is (MM.DD.YYYY).

What can I do to make PowerBI knows that in Transformation, it's (DD.MM.YYYY) and not (MM.DD.YYYY).

I tried to add step "Using Locale" and I selected English (Canada), but it didn't work.

The following is supposed to be 1st May 2022 but it shows as 5th Jan 2022.

[v-stephen-msft · Accepted solution]

Hi @Anonymous ,

You can split the date column with "-" and then add custom columns to regroup your dates.

1.Change the date column to text type.

2.Split the date column with "-".

3.Before you add a custom column to regroup, make sure three date columns are text type.

4.Add the custom column.

5.Change the new date column to date type, remove the other unneeded columns. And its format is "MM/DD/YYYY".

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PhilipTreacy]

Hi @Anonymous 

Change Type using Locale telsl PQ what format the date is coming from not what it is going to.  So your dates are curently in a UK or AU format so try changing format using locale and selecting English(UK) or English (Australia) then the dates will convert and if your system is set up as Canadian, they should display as YYYY-MM-DD.

regards

Phil