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


New Member
Nov 18, 2014

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

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.


Board Regular
Sep 12, 2014
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: )


Board Regular
Sep 12, 2014
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)


New Member
Jun 1, 2020
Office Version
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

Latest member

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...