Error while importing data from SQL server

[giramswa]

Hello,

I have a view created in SQL server and imported its data in my power bi dataset which created new table in Power BI.

Then I modified the databse view in SQL server, by adding two more columns and want to get these two columns in Power BI table. So I tried to refresh the table as well as refresh the complete report in Power BI, however its failing with error "load was cancelled by an error in loading a previous table".

So then I deleted the table in power BI, saved and closed file. Reopened the file and trying to import the data from SQL view into Power BI however its failing again wtih error - Failed to save modifications to the server. Error returned: 'Failed to resolve name 'PM_FORECAST_ACCURACY'. It is not a valid table, variable, or function name.
OLE DB or ODBC error: [DataSource.Error] Microsoft SQL: Query timeout expired.'.

I do not have any table named 'PM_FORECAST_ACCURACY' in my Power BI, it was there earlier but I already deleted it few days ago. So cannot understand how to resolve this error.

[Anonymous]

[v-kkf-msft]

Hi @giramswa ,

Please try clearing the Desktop cache and data source credentials, then reconnect to this table.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[giramswa]

My issue resolved with step - in  Power Query press "Refresh All" to force a meta data refresh on your SQL connection. However, information you provided is still usefull on how to clear the cache. Appreciate your help.

[lbendlin · Accepted solution]

Either start over, or in  Power Query press "Refresh All" to force a meta data refresh on your SQL connection.

Try to avoid adding and removing columns all the time.  Use a view to buffer the changes.

[giramswa]

Thank you @lbendlin this resolved my issue. Can you please provide more information on how to "use a view to buffer the changes"?

[lbendlin]

If you know that your SQL table changes frequently but you don't really need to follow these changes (because the columns you need are not affected) then you can create a view on the SQL server database that only contains the columns you need.  Then connect  Power Query to that view instead of the underlying table. That way you will not be impacted when they mess with the table structure.