Excel 2013 Errors in linked Tables: An answer

[masplin]

I'm posting this in case anyone else has this problem to save the month I have spend trying to track it down. Even MS paid support couldn't work it out. Basically the error reporting in the 2013 version is completely useless and much less informative than 2010. So if you see

"There were errors trying to update one or more linked tables. Use Options below to see ways to fix these problems, or OK to continue without fixing them." and then go to the options to basically wreck your model don't panic. I have found 2 causes

1. If you have incompatible data in one of your import fields e.g. date entered as US style 07/29/13 when your date format is UK 29/07/13, or text. In 2010 you got an incompatible data error now you get the above.
2. If you update a table that has a calculated field with a dependency on another table which it can't find you get the same error. I found this because I was updating weekly figures and updated the tables one at a time but in the wrong order.

Broadly I think any error created on import will throw this same warning. i hope this message saves some people a lot of heart ache.

Mike

 

[marious]

Thanks very much Mike


1) In order to use Time for powermap I need to set data type for Time field as Date and format as 13:30:55
2) in the power pivot data model design tab I set Mark Time as Date Table.
Everything is fine until here and the power map tim feature works, but when I refresh the data have an error "There were errors trying to update one or more linked tables. Use Options below to see ways to fix these problems, or OK to continue without fixing them."
The reason is that my Time field is linked to a time column in excel that is really formula and excel see it as TEXT regardlss of formatting the excel column as time 13:50:55
This post gave me hint. I uncheck Mark as Date Table in design data model and then when I refresh th data eveyting was updated. Of course the Time field was updated as TEXT instead of Date but I can change it again

 

[marious]

I finally solved. Since xl see TIME as TEXT I used this formula =TIMEVALUE([@Time]) within the table and now the data model see it as Date and I can have checked the Mark as Date Table.
there is a sandbox attached here http://1drv.ms/1q0khzN
Se spreadsheet run407 field TimeDos

 

[seanrez]

I'm posting this in case anyone else has this problem to save the month I have spend trying to track it down. Even MS paid support couldn't work it out. Basically the error reporting in the 2013 version is completely useless and much less informative than 2010. So if you see

"There were errors trying to update one or more linked tables. Use Options below to see ways to fix these problems, or OK to continue without fixing them." and then go to the options to basically wreck your model don't panic. I have found 2 causes

1. If you have incompatible data in one of your import fields e.g. date entered as US style 07/29/13 when your date format is UK 29/07/13, or text. In 2010 you got an incompatible data error now you get the above.
2. If you update a table that has a calculated field with a dependency on another table which it can't find you get the same error. I found this because I was updating weekly figures and updated the tables one at a time but in the wrong order.

Broadly I think any error created on import will throw this same warning. i hope this message saves some people a lot of heart ache.

Mike

Dude, you are an awesome human being. Thanks so much for taking the time to post this very helpful tip. I had a similar problem. My PowerPivot table column was formatted as text and I tried to update it with decimal data type values. It gave me the same generic error message. All I had to do was go to PowerPivot and change the column's data type to decimal, and the refresh worked!

Thanks again!! :D

 

[khhc14]

Thank you for posting this, that error is horrible!
Just to add, you need to change the powerpivot datatype to what it was originally before updating the linked table. If you have a text number that it has automatically converted to a "Whole Number", it needs to be that before you refresh. Same with a text date that it has automatically converted. Sometimes you will change these to link easier with other tables so watch out!