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

[ulooz]

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.

 

[ulooz]

Additionally, getting 1/1/2018 and 41935 instead of 12/29/2015

 

[alex1alex]

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. )

 

[alex1alex]

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]

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!

 

[jelephants]

I know this original question was raised over a year ago, but I thought I'd share how I managed to fix this issue...

Thank you for saving me two years later

 

[whatabout_]

I know this original question was raised over a year ago, but I thought I'd share how I managed to fix this issue...

Thanks again for providing info way later. Never too late to save others time! Respect! May the force be with you.

 

[amcaddress]

I know this original question was raised over a year ago, but I thought I'd share how I managed to fix this issue ...

Thanks for the time. Mine was due to hidden slicer using the month column I changed. After I removed the slicers I was good to go.

 

[Belsesame]

I've recently come across this issue, but the above didn't work for me - not sure what caused the issue, but it seemed that creating a pivot of a data model sync from SharePoint somehow thought a date field in the pivot wasn't formatted correctly. The only way I could fix this and remove the above error was to remove and re-add all date fields in the pivot.