How to build a dynamic SQL query in Power BI using user-selected attributes?

[adithyapythibi]

Hi all,

I’m trying to implement a setup in Power BI where users can select attributes/columns at runtime, and based on their selection, the SQL query should dynamically update the SELECT clause.

For this, I’m parameterizing the attributes (column names) and then trying to pass these parameters into the Power Query so the generated SQL only returns the chosen fields.

Has anyone implemented this pattern before? What’s the recommended approach in Power BI for passing user-selected columns into a dynamic SQL query?

Example of what I’m aiming for:

• If a user selects Season and Entity, the query should return

   

  SELECT Season, Entity FROM myTable

  • If a user selects Market, the query should return:

    Select market From Mytable

    Any guidance, best practices, or sample M query patterns would be much appreciated!

    Thanks in advance.

[Poojara_D12]

Hi @adithyapythibi 

In Power BI, the pattern you’re describing—allowing end users to dynamically choose which columns should appear in the SQL SELECT clause at runtime—isn’t natively supported in a fully dynamic way, because Power BI queries (M and SQL) are generally designed to be schema-stable once published. However, there are a couple of recommended approaches to approximate this. The most common is to load a broader dataset (all potential columns you might need) into the model and then use slicers, field parameters, or dynamic measures to let users control which fields are displayed in visuals, rather than changing the underlying SQL each time. If you really want to push column-level filtering into SQL, you can parameterize queries in Power Query by creating parameters for the column names and concatenating them into the SQL statement, but this is brittle—schema changes can easily break it, and it won’t scale well with end-user self-service. The best practice in Power BI is usually to separate data retrieval (bring in all needed fields once) from data presentation (use field parameters or dynamic columns in visuals to let users decide what they see). This keeps refreshes efficient and avoids overcomplicating query folding or gateway setups. If your use case is strongly tied to minimizing data retrieval (e.g., due to huge datasets), then you may need to implement a stored procedure or view that takes parameters for columns, and call that from Power Query, but this is more of a database-side solution than a Power BI-native one.

[V-yubandi-msft]

Hi @adithyapythibi ,

May I know if the issue has been resolved, or are you still facing any challenges? Please let us know if you need any additional details or assistance.

Thank you.

[V-yubandi-msft]

Hi @adithyapythibi ,

Just following up has your issue been resolved, or are you still having any difficulties? If you need more information or help, please let us know. 

Thanks.

[V-yubandi-msft]

Hi @adithyapythibi ,

I wanted to check in regarding your issue. Has it been resolved, or do you need any further information. Let me know if you’d like more details.

Thanks.

[V-yubandi-msft]

Hi @adithyapythibi ,
Thank you all for your valuable input. I agree with the points made by @hnguy71 , @Shahid12523 & @danextian. Currently, Power BI does not allow dynamic changes to the SELECT clause based on user interaction. Even with dynamic M query parameters, the functionality is limited to filtering rather than changing which columns are displayed.

Regards,
Yugandhar.

[danextian]

This is possible with DirectQuery only using dynamic M Query parameters (link in @hnguy71 's reply) as the data doesn't need to be imported. Apparentl, it is not possible with imports. 

[Shahid12523]

You can’t dynamically change the SELECT columns inside Power BI.

Options:

Dynamic M Query Parameters → only for filtering, not for columns.

SQL Stored Proc / Views → handle dynamic SELECT in SQL, pass parameter from Power BI.

Load all columns, use slicers/DAX → show only chosen attributes in visuals.

👉 Best practice: push column-selection logic to SQL (views/procs) or load wide and control visibility in Power BI.

[hnguy71]

Hi @adithyapythibi 

Assuming you are attempting to import the data into Power BI, I would say at the moment that's not possible especially when a semantic model is shared among many users / consumers. 

If it's a DirectQuery, you can try looking into dynamic M query parameters:

https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters