Hello everyone and welcome to my blog. In today’s blog I will show how you can troubleshoot errors you may encounter when creating Power BI reports with DataVerse data using Direct Query option.
Without further ado, let’s jump straight to an example. My Power BI Data model has couple of Data sources.
- An excel file imported locally
- DataVerse using Direct Query
If you are not aware of Import and Direct Query options to connect to DataVerse and what are the benefits and scenarios of each of their usage, I strongly suggest to go through this documentation before reading further.
Below is the Power BI data model.
“contoso_project” is the table from DataVerse and “Machines” is the table from the locally imported excel sheet. Below are the visualizations built using these data models and everything works great in Power BI Desktop.
But when I go ahead and publish this, I get the following error in Power BI Service – “There is no available gateway”.
As you can see, the visualization with excel data is displayed but the one with DataVerse has an error.
Honestly the error does not make much sense. Why shall I need a gateway at all? I am using Direct query to get real-time data from DataVerse and that’s an online service too.
My initial hunch was it has be some issue with the DataVerse connection. To cross check that, I went to DataSet -> Settings but to my surprise I could see the Data source Credentials greyed out.
As part of troubleshooting, I went ahead and removed the excel data source and just kept the DataVerse model. And guess what! The visualization was now visible.
If I navigate to DataSet -> Settings, I could now find the “Data source credentials” enabled.
Let’s now add one more troubleshooting step and see how the system behave. Instead of importing the excel from local, I uploaded the excel file in OneDrive. And then I connected to OneDrive from Power BI Desktop to get the data.
Everything works fine in Power BI Desktop as always. But again when I publish the report to Power BI service, I got an error again in the visualization of DataVerse.
But this time there was a difference. The Data source credentials section was enabled and all that was needed was to fix the connection.
And when I did that, the visualization started showing up.
All I could take away from this experiment is that Direct query does not seem to work if you have multiple data models and one or more of them is imported locally.
Hope you liked this post. If this post has helped, you can buy me a coffee. Links on right pane.
For similar topics on Microsoft.NET and Power Platform, subscribe to my blog using the Subscribe option on right pane.
You will also like the below posts.
Debajit Dutta
Business Solutions MVP