change to correct date format in power query

[pang22]

Dears,

The excel wrongly recognize the date as 3 Jan, but the correct date should be 1 Mar.

How to auto change in power query to correct date format, 1 Mar?

Thanks.

Excel:

Power Query:

[Vijay_A_Verma · Accepted solution]

Ideally, it should not happen. You need to validate in Excel whether this is really 3-Jan. 

You can put following formula and see what month is coming up.

=TEXT(A2, "dd-mmm-yy")

However, to change in PQ - 

Right click on your column header in PQ - Change Type - Using Locale - Select Data type as Date here and  you can select right locale here. 

[Anonymous · Accepted solution]

Hi @pang22 

The solution @Vijay_A_Verma mentioned is right, as @Vijay_A_Verma  mentined, you can consider to use the Using Locale, based on the soution, I can offer some sample for you.

You can change the locale to UK.

e.g Sample data 

Select 'Change type'->'Using Locale'

Then select the option as the following pictures.

Output

If this helps, please consider to use this and the suggestion @Vijay_A_Verma  as a solution.

Best Regards!

Yolo Zhu

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

[Vijay_A_Verma · Accepted solution]

Ideally, it should not happen. You need to validate in Excel whether this is really 3-Jan. 

You can put following formula and see what month is coming up.

=TEXT(A2, "dd-mmm-yy")

However, to change in PQ - 

Right click on your column header in PQ - Change Type - Using Locale - Select Data type as Date here and  you can select right locale here.