Forum Discussion
Dynamic Query using parameters without DirectQuery
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
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
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.