Combine Direct Query Dataset with single Excel File

[joshua1990]

Hi all!

I have a report based on a power bi dataset. Now I would like to create a new page in the same report that combines the PBI Dataset with 1 excel file. Here I would like to add a many-to-one relationship from the excel file to a specific table in the PBI Dataset.

This is possible, right?

But, what about the refresh time? How long would it take to refresh this new report? What is the logic behind?

[v-yueyunzh-msft · Accepted solution]

Hi , @joshua1990 

According to your requirements description, what you want to achieve is to create a new page in the same report, which combines the PBI dataset with 1 excel file, which will generate a PBI composite model connected to the PBI dataset in DQ mode and connected to Excel in import mode.

For PBI composite model, because it has two data connection modes, if you click the refresh button, for DQ data is real-time query calculation when you interact with the report, so it will only refresh according to the data displayed in real time on the current page of your report, and for the data source connected in import mode, it needs to be a full data refresh, which is equivalent to extracting the data from the data source and then overwriting the old data, for PBI For the optimization operation of the composite model and the best recommendations, you can check this official document in detail:

Composite model guidance in Power BI Desktop - Power BI | Microsoft Learn

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[lbendlin]

Depends on the cardinality of the join column.  You can use DAX Studio to check how big the query is.