topic Date Format Issues in Power Query

Date Format Issues

Anonymous — Mon, 04 Jan 2021 02:46:26 GMT

Hello everyone,

I have a raw data file of excel that is pulled to see transactional date and time. Each time it is pulled, the date format seems to be jumbled up.

As you can see below, the date format seems to be mismatched with each rows. The correct date format is supposed to be the one in yellow highlighted.

I'm not entirely sure why Excel seems to be showing this error and I would like to try fixing the error in Power Query instead. My intention to standardized the date format to be DD-MMMM-YYYY. However due to the date format being jumbled up, I'm unable to do so.

Is there a way I can fix the error as shown above? I have attached the raw date file below. Thank you so much for your help!

https://1drv.ms/x/s!AiNw1b4HnMZOox0ZRh_ILmQ4dAEf

Re: Date Format Issues

ImkeF — Mon, 04 Jan 2021 07:18:25 GMT

Hi Anonymous ,

your description doesn't match the picture: Do you want keep the time as well?

Also, the question is if the first date is the 2nd of May or 5th of February.

Re: Date Format Issues

v-robertq-msft — Mon, 04 Jan 2021 07:24:27 GMT

Hi, Anonymous

According to your description, I suggest you to set Format cells for the date column to uniform the date format in Excel, like this:

And you can get the uniform date format.

What’s more, you can also connect the Excel file to Power BI desktop and the Power query will automacially transform the data to the same format so that you can copy table to Excel too.

Using Power query in Power BI

Best Regards,

Community Support Team _Robert Qin

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