Excel Power Query changes date value into general numbers after refreshing the query (from online source)

0
votes

Anyone has an idea why I am getting "general format (numbers)" in excel Power Query after refreshing the data? As you can see on the screenshot, there you can see mix of years-dates and numbers as date. That is giving me further errors when I am trying to refresh my Pivot model (screenshot 2). In Power Query Editor I changed to Date format, and it actually looking good, but when I try to refresh in Excel it gives me this mix of data. I have to change manually then here in Excel (home > format) and the I can refresh it properly - but I dont wanna to do that manually.

First picture:

enter image description here

Second picture: enter image description here

3
excelexcel-formulapowerqueryexcel-2016excel-365
There might be a fix, but with no useful data, and no code, hard to say. Suggest you read the HELP topics for How do I Ask a Good Question, and also How to create a Minimal, Complete, and Verifiable example. Finally, either edit your original question to improve it, or ask a new one if the original has been closed. – Ron Rosenfeld

3 Answers

1
votes

To me it looks like table/range glitches, they are hard to catch and usually remediate by creating new worksheet. To cure this specific error we need more information.

However I offer you a solution which eliminates intermediate table and errors associated with it.

First make a backup of your workbook. Go to Data tab, and ensure that Queries and connections is pressed, and there is a query list on the right of the screen. Then right-click on your query and select Load to..., then click on Only Create Connection and check Add this data to the Data Model, if you use one. This will eliminate Excel table, for what you'll be prompted.

Next, create you pivot table anew. You need this as for some reason Excel doesn't allow to change data source from table/range to a connection.

Using a connection rather than a table will let you skip an intermediate step for refreshing your table first and pivot table afterwards. Also you probably save lot of headache from managing this intermediate table.

1
votes

After some days of playing around, I found one of the solutions that at least works by me. There is actually general "issue" by Microsoft and preserving the format you wanna. In my case, I was getting always dates but in number format, and that disturbed my Pivot Model. I was looking everywhere and on some pages, I found this as a solution.

In table tools settings, just CHECK Preserve column sort/filter option, save as, close and restart. As I say, I tested in consequently 4 days without any issues!

https://www.excelguru.ca/blog/2016/08/23/fix-excel-formulas-dont-update-in-power-query-tables/

There is on another links also a bit but I forget them unfortunately...

enter image description here

0
votes

I had the same problem but the solution was quite easy. In power query, select the column with dates. Next, go to "data type" and select "date". Now when you close and load, it should load the dates you started with. enter image description here