SQL query from Dataverse connector - Doesn't update the query if you remove or include columns

[Anonymous]

Hi all,

Thanks in advance for all the support you provide to the community!

I am having some issues with the connector I have in a Power BI dashboard to some tables in Dataverse. Everything is handle with Power Query.

The issue I have is basically that after making some changes in the Dataverse tables (eg. remove or include columns), these are not reflected in the SQL query that runs in the second step from Power Query (first is where you select the source, in my case one enviroment from crm.dynamics.com). These things give two main problems:

1. In case I remove the columns from the Dataverse table, the second step from Power Query will fail as the SQL query running for it will still have the column names in it, and it'll return the error: [DataSource.Error] Microsoft SQL: "Invalid column name '...' as obviously that column no longer existe in the Dataverse table. The main problem is that after that, I can not make any changes to the Power Query query due to that.                                    
2. Secondly, in case I include some columns in the Dataverse table, these new ones will not be considered in the SQL query running in the second step as the query will not be updated automatically.

Now, things I have considered and tried in the meantime are:

• Try to find a way to edit the SQL query - UNSUCCESSFUL - I don't know where I can edit the query if I can, but the two buttons in the image below show the query, but doesn't allow to edit it (no edit button after clicking on those either)
• Clear the cache - UNSUCCESSFUL - I saw in some forums that cleaning the cache it could help in the process, but since the last Power BI update, the place where the cache management was is gone.
• Create from a completely new dashboard file the same connection - UNSUCCESSFUL - any new connection I have tried return the same SQL query than the one existing in the other dashboard, hence return the same columns and values.
• Even if it sounds a little bit odd, I have tried to reboot my laptop - UNSUCCESSFUL

Is there anyone who know how to solve this issue by any chance? This is slightly driving me crazy at the moment.

Once again, thanks a million times in advance!

Best,

Bruno

[Anonymous]

Hey Jing,

Thanks a lot for your message!

After testing a few times, with a colleague, we have realised that is an issue from the cache. After evrey change you do in dataverse tables, you need to clean the cache for the respective dashboard.

Thanks again for the help! I really appreciate it!

Best,

[Anonymous]

It's good news! Thank you as well!

[Anonymous · Accepted solution]

Hi @Anonymous 

It may take some time for changes to the columns to be reflected in Power Query. Are you able to see those changes now?

To clear cache in Power BI Desktop, you can go to File > Options and settings > Options. Under GLOBAL, select Data Load, and then select Clear Cache.

Best Regards,
Jing