Unable to Refresh Powerpivot when data is modified

[Kaitlin29]

Hi,

I've linked a data set in one workbook (multiple tabs with identical columns) to a powerpivot in another workbook using a modified SQL in the table properties 'query editor'. The powerpivot loaded all 12 tabs from the data set without an issue.

However, when I change the data in the data set, ex. a 5 to a 1, when I click refresh in my powerpivot, it gives me an error message: 'We couldn't refresh the table '___' from connection '____'. OLE DB or ODBC error: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.'

I'm wondering how to resolve this? What I could be doing wrong? Please help!

 

[Matt Allington]

How about trying Power Query instead. I didn't even know you could use SQL to import from a spreadsheet, but Power Query is fabulous and well worth a look