Create parameter with suggested values from query of table column names

[absherzad]

Hello everyone,
Is it possible to create a parameter with the suggested values from the query that returns the column names from a table?

It seems Table.ColumnNames() returns the names of columns from a table as a text, because I don't see any drop-down list as an input value for the parameter.

let
    Source = Excel.CurrentWorkbook(){[Name="MasterTable"]}[Content],
    #"Column Names" = Table.ColumnNames(Source)
in
    #"Column Names"

Even with suggested values from any it does not work. Any suggestions?

Regards,

 

[gazpage]

I just tried this and it worked fine for me.

When you are I the New Parameter wizard, and you selected your Query under "Suggested Values" do you not then see your query from the drop down? Sorry not very helpful answer I know.

 

[absherzad]

The M code works just fine. Also, the query is listed in the new parameter dialogue box. But

• after the parameter is created, the drop-down list as input value does not appear.
• also when I use this parameter inside the function as a place holder for the column name does not work.

I hope I could explain my concern.

 

[absherzad]

Hi everyone,
I found the solution and I would like to share it with others.
The solution is to use Record.Field() to allow a dynamic column name as a parameter inside the M language and it works like charm, as it is illustrated below

let
    Source = Excel.CurrentWorkbook(){[Name="MasterTable"]}[Content],
    // #"Filter by Column" is the parameter for column names
    // #"Filter by Value" is another parameter for the value
    #"Filtered Rows by Parameters" = Table.SelectRows(Source, each Record.Field(_, #"Filter by Column") = #"Filter by Value")
in
    #"Filtered Rows by Parameters"