SQL Server Change Data Source gives "Expression.Error: The key didn't match any rows in the table."

[Anonymous]

We are migrating from SQL Server (version 11.0.7507.2) to SLQ Server (version 13.0.5865.1). Other than that all things remain the same, we tacke a backup of the old database and load that into the new database, the database name remains the same the table names remain the same and the functions/procedures remain the same. We changed none of these. I checked the data tables loaded into the new database all looks OK. 

Next I made a copy of an old .pbix Power BI report file and open it. Then change the data connection to the new Server and new Database uisng Transform Data / Data source settings. When the report tries to pull back the data (Apply changes) I get the message: "Loading blocked by failures with other queries." when I go into Transform Data all of the data queries except one are able to run, the one query that can't run hsa the following error "Expression.Error: The key didn't match any rows in the table."

Looking at the Query Settings / Applied Steps / Navigation I can see a list of all the table functions avalable on the SQL Server (profixed with fx), but the one causing the error is at the foot of the list with a down/right arrow. 

Looking at the Query Settings / Applied Steps / Source I can see a table of all the table functions avalable on the SQL Server, however the one that is causing the error is not in that table. When I refresh this table the missing SQL table function does not appear in the list.

When I look at the SQL New Server database via SLQ Server Management Studio I can see all the table functions, including the one that is giving the error. It has been loaded from the old database backup and exists in the new database.  The table function runs OK on the new Server/database with no issues, it is a simple function that returns onme row of text that gets used in the report headers.

When I try other reports with the new Server/Database, some work and some don't. I had the same errors in another report but with a different SQL table funstion. So it is not just one table function that does not work, there is at least one other, there could be more.  Some of my reports do run OK with the new Server/Database.  

What is going on? Why does Power BI not see the full list of table functions that were migrated to the new Server/Database? How can I refresh Power BI to see all the SLQ Server table functions on the new Server/Database? Any help appreciated. Thanks

[rgaroz]

A little bit late to the party but if someone is facing the same problem:

My problem was that the user we were using to connect did not have enough permissions to acces the tables used in the report. Once we granted sufficient permissiones, it worked well.

[Anonymous · Accepted solution]

Found the culprit: the two missing SQL Table Functions did not have SELECT access granted. For some reason this was not an issue in the old version of SQL Server, but became an issue for the new version. So the newer version of SQL Server was deying access to the data becasue the access rights had not been granted.