Create One Report That Can Switch Between Multiple Datasets (Excel Workbooks)

[CaveOfWonders]

I have around 300 different datasets (Excel Workbooks); the structure of the data within the workbooks is exactly the same, but the content differs depending on the project. All the workbooks are sitting in the same directory within SharePoint.

I have created a report based on one of the workbooks.

Essentially my question is, is it possible to allow clients to dynamically switch between datasets within the report, i.e., if they are viewing data relating to project #1 and want to see data relating to project #200, they could use a drop down or a search bar to find #200 which would refresh the data in the report to look at the data for that project.

This has to be possible in Power BI Desktop. I cannot use the service. There are no data source alternatives at the moment.

[v-frfei-msft · Accepted solution]

Hi @CaveOfWonders ,

Does Using Query Parameters in Power BI Desktop meet your requirement?

[Greg_Deckler]

Seems to me you want to use a Folder query. This imports everything in a folder and appends it all together. Then you can use a slicer as you are discussing.

[CaveOfWonders]

Hi Greg, thank you for your prompt reply. Preferably no, we don't want to do that, we'd like to be able to switch between datasets dynamically and not have to bring all the data in to one workbook.

But I've also just checked, it's not feasable to do so with sharepoint as it doesn't let you specify a folder. It brings in everything in that sharepoint. Please correct me if I'm wrong.