One dataset for multiple reports

[Anonymous]

Hi, 

I am doing a cashflow report, where it consists of a few elements (eg: sales, loan repayment, HR costs and etc) that maintained by different teams.

Since multiple datasets for one report (gather data from different datasets into one table) is not supported, and the dashboard is not useful in this case since we want to know the impact of outflow and inflow.

The conditions in this case

1. The management want to know the net cashflow and also the detail of individual elements.

2. We want the data owner of each element to maintain their data and report and have no access to other datasets.

3. All the data are stored in sharepoint of different libraries.

The workaround suggested from other articles is to create a master dataset and use the Power BI Dataset connector to connect to the source data and create multiple reports.

However, I can't find where to delete/hide the tables or refresh the data in the target file from Power BI Desktop.

I assume the target table is live connected to the reference file, but the data is not updated unless a manual refresh from Power BI Online.

Also, whenever there is a new table/column added to the reference table after the target file is being created, the new table does not appear in the target file. How can I control whether the newly added table/column should appear in the target file? 

I hope you can correct me if I understand it wrong.

[mahoneypat · Accepted solution]

You can also do this with import mode. You could have 4 queries for each SharePoint list and append them together to perform the final query (disable loading on the original 4 queries). You can then use Row-Level Security to control who can see what. That approach would have the least overhead for you (versus managing multiple reports with different content). Lists would have to have or transform into tables with the same columns, and you can compile in logic on how to handle new columns, and so on.

FYI also that this video on how to get sharepoint list data quickly might be of interest as well.

Get SharePoint list data with Power BI... Fast - YouTube

Best regards

Pat