abnormal power pivot error message 'string cannot be converted to date type'

ulooz

New Member
Joined
Nov 18, 2014
Messages
10
Hi,

NORMALLY when I get a date error with PowerPivot it is in relation to the type of cell and it gives me the header to look into. For instance, if the "contract date" had a blank or a non-date value PP would call it out and i'd simply address the value... but the current error reads like this: "the query did not run or the data model could not be accessed. Here's the error message we got: The '12/29/2015' string cannot be converted to the date type". Now.. I have no headers that are 12/29/2015 nor do I know which string it is referencing... Which leads my to my question: Has anyone run into this or is there any insight around how to troubleshoot? Much appreciated.
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

alex1alex

Board Regular
Joined
Sep 12, 2014
Messages
56
FYI, I'm getting this as well. (I had an existing PowerPivot with a PowerQuery as source....the error popped up with I made some changes to the power query. No joy in figuring out how to fix with out recreating the data model. :eek: )
 

alex1alex

Board Regular
Joined
Sep 12, 2014
Messages
56
I just had this and fixed it. I had an existing power query feeding a data model. I had replaced the power query with another one (cutting and pasting via the Advanced editor). When I did, one of the columns didn't match the exiting data model (it was an integer instead of a date) and upon refresh I noticed this because one of the PivotTables was used this as a column and I was expecting a date.(but it did not pop up as an error) When I saw this, i went to PowerQuery and fixed it from integer to date. Hit refresh. At which point the same error you mentioned popped up.

Luckily, we have versioning in OneDrive :)

I opened my original file and redid the cut and paste of the power query but fixed the date column before running the query....problem gone.
(I'm happy with this solution as I didn't want to recreate the Data Model which is a huge pain)

(The data model seems to be very touchy about unexpected changes in the type of the data connections that feed it)
 

Jay_maan

New Member
Joined
Jun 1, 2020
Messages
1
Office Version
365
Platform
Windows
I know this original question was raised over a year ago, but I thought I'd share how I managed to fix this issue for the benefit of anyone else who's getting the same and pulling their hairs out on how to fix it (like I was :)).

The cause of the error has nothing to do with the power query. Instead this error is thrown if you have a power pivot with a filter on to the same date column on your power query table before you've converted that column into proper date formatting.

I originally had a power query table, which had a start date column with dates on, but not converted into proper date formatting via power query window. I then setup a power pivot to summaries this data and had a filter on it to show only a particular set of dates (but because the data wasn't formatted properly that filtering has happened as text value filtering). After a while, when my data set got larger, I realised it would be best to convert that date column into proper date formatting. When I did that and hit refresh all, that's when I started getting the above mentioned error. After several days of trying to figure what on earth was going on (because just like the original poster mentioned - I didn't have any incorrect date values in my data set), I finally tried removing all the filters that I had on that data column form my pivot table and tried refreshing again and it did the trick!! Turns out it was the power pivot filter, which was throwing that error all the time, because it must have been looking for text based values instead of date formatted ones.

Once I got rid of that error, I re-applied the filters and I didn't get that pesky error again!

Hope this helps peeps!
 

Watch MrExcel Video

Forum statistics

Threads
1,102,335
Messages
5,486,233
Members
407,538
Latest member
kbendelac

This Week's Hot Topics

Top