Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a report + dataset located in a workspace in the Power BI service. The workspace is called: Workspace1, and report/dataset is called CompanySales. This fetches the data from an on-prem SQL server every morning via the standard gateway.
Now I plan on testing out the Power BI composite model feature. So, I open Power BI Desktop to create a new report, clicked on Get Data and selected the Power BI dataset called CompanySales. This establishes a live connection to the dataset. Now I click on Get Data and choose Excel workbook and select an excel file (TestExcel.xlsx) located on my local machine. This has turned the report into a composite model with a direct query connection to the CompanySales dataset and the Excel table shows up as Import mode. I create 2 table visuals. One table visual shows the columns from the import table (TestExcel.xlsx), and the other table visual shows columns from one of the tables from the DirectQuery dataset (CompanySales). All good so far.
Now I publish this report (save it as TestReport.pbix) to Workspace2. Then, I open the Power BI web portal, go to Workspace2, click on this new report (TestReport) and I can see the following. At the top it says `There is no gateway to access the data source companysales`. The 1st table visual (which is from the excel file) shows up fine, but the second table visual says `Cannot display the visual`.
The reason for the There is no gateway to access the data source companysales error is:
There is no gateway to access the data source companysales. Verify the gateway connection configuration on the dataset settings page and make sure all required data sources are added to the selected data gateway.
If I click on the `dataset settings page` then it just takes me to the Power BI Homepage. Are you able to replicate this issue? What is the solution?
MSDN docs say (https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-composite-models#considerations-a...😞
Mixed-mode connections - When using a mixed mode connection that contains online data (such as a Power BI dataset) and an on-premises dataset (such as an Excel workbook), you must have gateway mapping established for visuals to properly appear.
What exactly does this mean?
If I go to this composite model's dataset settings then I see:
Any help from Microsoft on this please?
Now I click on Get Data and choose Excel workbook and select an excel file (TestExcel.xlsx) located on my local machine.
This is the actual cause of the error. Your gateway has no way of accessing that file. Move the file to a OneDrive/Sharepoint and access it via the SharePoint Folder connector.