Forum Discussion
Major bug in Excel Powerquery (Get & Transform) for dates
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
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
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?
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