Dynamic Parameterization in Power Query Editor

[Anonymous]

Hi, 
I am creating a report which uses tables from Azure Data Explorer(Kusto). How do I make duration as a parameter that can be changed in my Power BI Report if this is the query in Power Query Editor. (This query is only a representative of original query) 

AzureDataExplorer.Contents("https://xyz.kusto.windows.net", "xyzdb", "let duration = 1h; #(lf)Table #(lf)| summarize count() by bin(TimeStamp, duration)#(lf)", [Timeout = #duration(0,0,4,0)])

Thanks. 

[v-xuxinyi-msft · Accepted solution]

Hi @Anonymous 

The following method is for your reference:

A prerequisite for using the following methods is that there must be a valid M query that uses one or more DirectQuery tables.

1. create parameters in Power Query

2. Referencing it in an M query

3. Create a table for each parameter containing a column that provides possible values that can be set dynamically based on the filter selection.

4. Bind the field to the parameter, the field type needs to be consistent with the parameter type, otherwise the option is not visible.

5. You can reference this field in the slicer or use it as a filter.

There is a potential security risk associated with this method, and you will be alerted accordingly when you bind fields to parameters. Avoiding string concatenation of M parameter values in the query reduces this risk.

The method also has some consideration and limitations:

For more details, you can read related document link:

Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Learn

Please feel free to let me know if I have misunderstood your needs.

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.