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

ulooz

New Member
Joined
Nov 18, 2014
Messages
14
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Additionally, getting 1/1/2018 and 41935 instead of 12/29/2015
 
Upvote 0
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: )
 
Upvote 0
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)
 
Upvote 0
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!
 
Upvote 0
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!
Thank you for saving me two years later
 
Upvote 0
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!
Thanks again for providing info way later. Never too late to save others time! Respect! May the force be with you.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top