Direct query dynamic column selection

[kahn_ohara]

I am familliar with passing user entered values to a query in Direct query/live connection using dynamic M query parameters as discussed here

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

However, so far I've only passed parameters to the where clause. The table I am hitting has hundreds of columns and users require the ability to view any/all of these columns in a live connection. Unfortunately, the performance will be awful if Power BI first selects all the columns in the database and then limits what's shown in power bi. Is there a way for me to allow users to select columns dynamically and only query the selected columns using live connection/direct query? Rather than passing user entered filter values to the where clause of my SQL I want to pass user selected column names to the select clause.

For example, suppose I have a PBI table called Table1. I'd like to create a parameter such that a user could select from a slicer col1 and col2 then Table1 will refresh based off the query below hitting the database

select col1, col2

from snowflaketable 

and if user selects from dropdown col1, col4, col3 from dropdown then Table1 will refresh

select col1, col4, col3

from snowflaketable 

Is this possible? From what I can tell from trying so far, PBI does not seem to support this functionality.

[v-jiewu-msft]

Hi @kahn_ohara ,

Based on the knowledge, it is impossible to create a parameter to filter the columns.

1.Create the query and use Table.ColumnNames to get the names.

2.Drag the Invoked Function into the slicer. When select the column, can not filter the table.

You can view the following documents to learn more about information.

Parameters - Power Query | Microsoft Learn

You can raise a new idea and add the comments there to make this feature coming sooner.

Home (microsoft.com)

Best Regards,

Wisdom Wu

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