Composite model report gives error after publish - "there is no gateway to access the data source"

[nachrss]

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:

Data source unreachable

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: 

• One or more cloud data sources for this dataset have been deleted.
• If I expand the gateway section then it asks me to choose the mapping but I'm unsure of how to proceed further from here. (because I can't choose a gateway, and under the Cloud Connections if I select Personal Cloud Connection then I'm unable to proceed. And if I select Create a connection then it says `Currently, these cloud connections are only supported for Data Pipelines and Kusto. In the future, other artifacts can also make use of the cloud connections. To create personal cloud connections in Datasets, Datamarts, and Dataflows, use the Power Query Online experience in "Get data".`

Any help from Microsoft on this please?

[lbendlin]

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.