Forum Discussion

arpost's avatar
arpost
Post Prodigy
4 years ago

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 init...
Need Help
arpost's avatar
arpost
Post Prodigy
4 years ago

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.

mahoneypat's avatar
mahoneypat
Microsoft Employee
4 years ago

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

 

  • arpost's avatar
    arpost
    Post Prodigy
    4 years ago

    Thanks for the response! This isn't a visualization-first report but actually needs to be fully dynamic on the query/ETL side as this could extend to complex scenarios like user-defined merges between tables and duplicate removals. My hope is to create a dataflow that will let users alter the data loaded into a table dynamically using an app as a kind of business logic/rules engine.