Report PowerBI Service SQL Server DirectQuery and Excel

[nia9229]

I would like to know if it is possible to publish a report whose data sources are SQL Sever (DirectQuery) and an Excel spreadsheet(Import).

I created the gateway and the data source for SQL Sever on PowerBI Service. However, I can only see those visuals that works with Excel and I get an error message that there is no gateway available

[nia9229]

Thank you for your answer.

I do not need to create a relationship between SQL Server and the excel spreadsheet. I guess this case is not a composite model because these objects are independent. They are used in different pages.

If I have understood correctly, I need to add all used datasources to the gateway even though they use the import mode. However, there is no Excel data source in the Power BI service. How can I fix this problem related to the spreadsheet?

[Tutu_in_YYC · Accepted solution]

It is still a composite model if you have independent tables with different modes in the same dataset.

If your excel file is on-premise ( in your computer, not in cloud), you should be able to add it to the gateway. All datasources that are not cloud-based, have to be added to the gateway.

[Tutu_in_YYC]

Yes you can, it is called composite model.
https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-composite-models

Once you set up the gateway, you need to map them to the dataset under gateway connection.
https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-data-sources#use-the-data-sou...