Dynamic Query using parameters without DirectQuery

[CameronCox]

Hi everyone,

I'm looking for some advice on incorporating a slicer into my PowerBI report to update a parameter that the report's queries use to pull in data. Currently, I can't use any database that supports DirectQuery, so I'm relying on Excel files stored on SharePoint. Each file contains data for a specific month/year/site combination. Although this isn't an ideal data storage system, it's what I have to work with at the moment.

I want to create slicers for month, year, and site in the report. When I change the selections on the slicers, the query should update in the background and refresh, pulling data from a different Excel file.

Right now, I have a parameter set up in the Power Query editor, but changing it requires going into the Power Query menu, updating the parameter, and clicking 'save and load.' This isn't user-friendly, especially for colleagues who aren't familiar with PowerBI. They need to be able to change these parameters and refresh the data easily, whether they're viewing the report on PowerBI service or in an embedded report on SharePoint.

This seems like it would be a common use case, so I'm hoping there's a standard solution.

Given the large number of Excel files, I don't want to load all of them into PowerBI and use the slicer as an 'after-the-fact' tool to filter the data. Instead, I want the slicer to change which data gets loaded, making the report more efficient and fast.

If this is not possible using standard, non-third party tools, could someone please explain why it is not possible? It doesn't seem like it would be fundamentally difficult to implement

[lbendlin · Accepted solution]

This seems like it would be a common use case, so I'm hoping there's a standard solution.

It is not, by a long stretch. There isn't a standard solution.  This truly only works for Direct Query data sources and will likely never work for Excel sources, as Excel sources don't fold.

If this is important to you please consider voting for an existing idea or raising a new one at https://ideas.fabric.microsoft.com

[CameronCox]

Thanks for the quick reply!

Hmm, yeah, I thought that might be the case. It's a shame that the learning curve for setting up and maintaining proper databases that support DirectQuery is so daunting compared to 'just sticking everything in Excel'. However, it's probably worth investing the time and effort in the long run.

I still don't quite understand why the blame lies with Excel. It seems like the issue lies with PowerBI. If PowerBI allowed 'PowerBI parameters' to be passed into Power Query as 'Power Query parameters' and automatically refreshed the data connections when the slicer (and thus the parameter in Power Query) changes, the data requests to the Excel files wouldn't be any different or more complex than if you manually edited the Power Query parameters and clicked save and load.

I guess there could be undesirable looping problems where queries feed into slicers, which then feed back into queries. But surely there would be a simple way to only allow parameters to change queries that don't impact the original parameter.

As you said, if this isn't a common use case and can be done more effectively with proper databases, maybe the need to provide this functionality was never there.