Power Bi parameter for Azure Data explorer Query

[sarath5140]

Hi Community,

Do you know if there is a way that I could use Paramter in Power BI for my Azure Data explorer query

I have a simple ADL query like this:

TableA | where category in ('A','B','C','D')

Now, I would like to replace Category values with Parameter that I need to get these values from different datasource and Table.

I have my sql db and Table like 

Type	Category
Employee	A
Employee	A
Employee	A
Manager	B
Manager	B
Manager	B
Manager	B
Staff	C
Staff	C
Staff	C
Security	D

Now in my ADL query where clause I need to pass these sql table Category values as parameter .I am using Import mode in Power BI and custom ADL for my sources. And help on this would be appreciated.

Example :- 

TableA | where category in (select distinct Category from Sql table)

Thanks

[v-yuta-msft]

@sarath5140 ,

I would suggest you to use Query Parameter, you may refer to blogs below:

https://powerbi.microsoft.com/en-us/blog/deep-dive-into-query-parameters-and-power-bi-templates/

https://biinsight.com/power-bi-desktop-query-parameters-part-1/

Community Support Team _ Jimmy Tao

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

[sarath5140 · Accepted solution]

Hi @v-yuta-msft 

Finally, I am able to get my output using M power query syntax instead of going with query parameters. Because query parameters doesn't allow multiple values selection and this is the limitation with dynamic query parameter so I used power query to acheieve the same.

Thanks for your help!

[sarath5140]

Hi @v-yuta-msft 

Any update on below query?

Thanks

[sarath5140]

Hi @v-yuta-msft 

Thanks for your reply and posts. I have tried the same in my query parameter but my problem is in my power query I need to use the IN condition for my parameter values beacuse I have multiple Category column values. 

Example :-

= let Source = [Query=[#"csl"="TableA | where Category in ("""&ServiceParameter&""")"

but this is not working for me beacuse it is considering only single value that is the current value which is "A". 

I should be able to pass all the values in the query parameter like TableA | where Category in ("A","B","C","D")

Can you please help me how to acheive this.

Thanks