Forum Discussion
Is it possible to create M query steps dynamically?
I have a scenario where I am needing to essentially create dynamic query steps based on a source table. In effect, this is "build-a-bear" but for Power Query.
For example, let's say my query initially just loads data from a DB:
I want to then allow an end user to add an item to a table that indicates we need to filter a column. Imagine something like this:
| User | Action | Column | Value |
| Joe Schmoe | Filter | Customer | Tailspin Toys (Head Office) |
I want my query to then "add" a query step that applies a filter:
Is something like this possible perhaps with functions or recursive function execution or something? Kind of like building a dynamic SQL query.
4 Replies
Thanks for the reply, mahoneypat! I'm sure parameters will play a part, but the issue is the number of filters to be applied could change as could the type of steps needed.
In the example above, a user may want to filter data to a specific customer when viewing a report one week but next week add filters that filter customer, product, and region. I need to not have to manually add filter logic that looks at all of those columns. He or she could even want to group by, say, product category, which isn't in the original query and is where (I'm hoping) the recursive behavior or custom function comes in.
Have you considered just loading all the data and then having all potential fitlers as slicers (or Filter panel options)?
You could also consider using paginated reports with parameters too.
Pat
