Composite model publishing issue

[PBIDEV_10]

Hello Gurus

I have a published power BI dataset in a workspace which I am using as a source in one report which is in another workspace. Then I got one requirement to include an excel sheet in the same report. That excel sheet is stored on a common file server location where my dept have access. So I converted existing model for the report to composite (Power BI dataset: direct query mode, Excel sheet: import mode, storage mode for the model: mixed). 

All works fine in the desktop version. But when I published the report then it shows the error: There is no gateway to access the data source. and no visualization is displayed. Then I went to check the setting and I found I cant map the excel sheet since I dont see it as a source and the power BI dataset shows that it has to be on gateway. See the screenshot:

where as I was expecting something like this that my dataset doesnt need mapping but the excel sheet needs to be mapped: 

I dont know what and where I need to change in setting for the datasource in the report. Can someone help?

Regards

PP

[edhans · Accepted solution]

The data source has to be the same. So likely instead of using O:\ in Power Query, you need to use the UNC name of the network path that the gateway is using. You may need to contact your IT department or whomever set up the gateway datasource to ensure they are the same.

[edhans]

And FWIW, if you can, just put that Excel file in a Teams or SharePoint folder, then use the SharePoint connector to get to it. Bypasses all of the gateway hassle as SharePoint is a cloud resource.

[PBIDEV_10]

Thank you so much for your reply. Yes we are now thinking to move excel sources to sharepoint so that we dont get issues like this. But for now the issue is solved with the correct network path.