Major bug in Excel Powerquery (Get & Transform) for dates

[amalm]

My query data is from another excel file. The source file is updated on a daily basis with new records, and I need the query file to transform that data and present it in a proper format. 

There is a date column in the source file that has the date in the format DD-MM-YYYY. I need the exact same thing in the query file, so I have set the column as "Date" type. When I go to edit query, this is what I see: (DD-mmm-YYYY which is perfectly fine).

Now when I close & load the data, I get this (DD-MM-YYYY which is also perfectly fine with me):

However, the problem starts when the data is refreshed the next day with new records. Randomly, some of the dates that were fine the previous day will show up as Integer Type. 

The same column after refreshing with new data:

Some dates showing as integer types and others are fine   

However if you go back to Edit Query, there is no sign there of this change and all the dates look perfect. It's only in the loaded table that this issue arises.

Needless to say, this is causing great nuisance for my work. Please, I need urgent help. 

[Anonymous · Accepted solution]

Hi @amalm ,

I suspect this may not be so much PQ issue. This looks more like a table/cells formatting issue in Excel itself.

Could you please try:

1. Format the entire column to "Short Date" format. Not just a column in the table, but the entire column in Excel (like "H").

2. If this does not help, delete the existing table from the file (better the entire tab/sheet) and re-export using Load-To or alike to a new tab?

Kind regards,

JB

[amalm]

@Anonymous 

Hi, I thought the problem was resolved, but unfortunately it is not. In one of my files it works well, but in another file it doesn't.

Even after formatting the entire column as "Date", this is the result I am getting after refreshing the data if it has new entries:

The cell formatting of A1:2617 after refreshing stays as "Date"

The cell formatting of A2618:A2628 after refreshing changes to "General"

The cell formatting of A2629 till the end of the column after refreshing stays as "Date"

Can you please explain this weird behaviour?

[Anonymous]

Hi @amalm 

I think for some weird reason Excel remembers the "initial" formatting of cells in the table. I don't remember seeing this with date formatting, but had few examples with cells colour. The best what I could do, deleting the table entirely and re-do it from scratches.

Can you try this? Please make sure that you return "date-formatted" data from your query. If this does not help you can try returning dates in the text format and see if this helps.

Kind regards,

JB

[amalm]

Thank you so much! Your first point solved the issue I was beating my head over!

The thing is, the file had been in use for many months and everything was working fine earlier when the computer was on Office 2016. They recently installed Office 365 on it and this broke the file. Now it works fine again. 🙂