Issues with Power Query Parameter Manager

Question

Issues with Power Query Parameter Manager

Anonymous

Trying to use Excel Query Editor and its Parameter Manager in conjunction with the "Create Function" from Navigation pane on a query. In Parameter Manager the following issues: creating a parameter with "Suggested Values" = Query; the Query dropdown box: 1) does not list all of the query names in project; 2) dropdown box extends beyond the edge of the Parameter Manager dialogue box; 3) sometimes the dropdown box is hidden behind the task bar at the bottom of my screen. When "Suggested Values" is "Query", "Query" dropdown has a selected query name and "Current Value" has another query name and the parameter is passed to a function the error message is produced that says "We cannot convert the value of (a literal) to type Table. It is not clear why there needs to be a "Current Value" since a query is listed in the query box. To get the "Create Function" to recognize that a parameter exists it seems the parameter must be defined in Parameter Manager. The query type parameter is not passed to the "Create Function" or so it seems. The Parameter Manger Type box does not include type Table, even though it is normal to pass tables as arguments to functions. The "Any" type does not seem to support type Table. Please explain how this is supposed to work?

3 answers

Your answer

Answer 1

Anonymous

I found this video https://www.youtube.com/watch?v=YiKtgTkj8wk that provides basics but doesn't address the issue of the dropdown when more than 20 queries are present or how to deal with a query as the parameter.

Lz._ 9,016 Reputation points

2021-05-07T12:39:43.34+00:00
@Anonymous
Some pointers if this can help:

Create a parameter query

What is a list query for a parameter?

Data-Driven Power BI Desktop Parameters Using List Queries

Using parameters

For the dropdown box that extends beyond the edge of the Parameter Manager dialogue box (> 20 queries) you can send this as a Frown (within Excel: File > Account > the smileys at the top right)
Anonymous

2021-05-16T19:51:37.893+00:00

Thank you. excellent references. While I do not seem to be able to specify a table as a parameter to pass as an argument to a function, I can convert a table to a list of records, thereby creating a "list" query that I can use as a parameter. A little recoding and it works great. Thank you. With out the references that included the description of the query parameter as a "list" query I never would have thought to convert a table to a list of records.
Anonymous

2021-05-16T19:59:57.447+00:00

Thank you. excellent references. While I do not seem to be able to specify a table as a parameter to pass as an argument to a function, I can convert a table to a list of records, thereby creating a "list" query that I can use as a parameter. A little recoding and it works great. Thank you. With out the references that included the description of the query parameter as a "list" query I never would have thought to convert a table to a list of records. @Lz._

Answer 2

Lz._ 9,016

Hi @Anonymous

Re. I do not seem to be able to specify a table as a parameter to pass as an argument to a function
Not sure this is exactly what you're after but check this example where I have n queries, 2 of them begin of type table (Table1, Table2). My function requires a Table as input:

(#"Select a table for this function" as table) =>  
let  
    Source = #"Select a table for this function",  
    /*  
        my function's next steps  
        ...  
    */  
    Result = "My function's result"  
in  
    Result

When I click on the arrow down button next to the drop down list:

Anonymous

2021-05-24T12:45:52.327+00:00

Not Exactly. Problem is with the interaction between Create Function (from query) and Parameter Manager

//GetFolderFiles
let
SofarTable =Table.FromList({},null,{"grpmems"}),
s1 = Folder.Files(FolderPath),
Source = Table.Combine({SofarTable, s1})
in
Source

In Parameter Manager define FolderPath and SofarTable as [Required, Type =Text, Suggested Value = Any, Default = C://]. Need to select SofarTable as type table which is not available. Using 'Create Function' on above code works. Remove 'SofarTable'-line causes error because SofarTable can not be defined as type table in Parameter Manager. Can manually edit function to make SofarTable as type table, but that breaks the edit linkage.
Lz._ 9,016 Reputation points

2021-05-24T15:15:43.09+00:00

@Anonymous . Still not 100% clear to me

Let's suppose the SofarTable parameter would be of type table. With your example where it's Current Value is C://

what would you expect that table to contain?
Anonymous

2021-05-24T18:35:12.097+00:00

The Current Value is only a place holder to satisfy Parameter Managers requirement for a default value. Would not invoke with that value.

I'm trying to get 'Create Function' to include a table as a parameter in the function definition.
GetFolderTable = (FolderPath as text, SofarTable as table) => let ....

Answer 3

Anonymous

The current value is only a place holder to satisfy the requirement for a default value. I should have used SofarTable as default. Sorry.

Lz._ 9,016 Reputation points

2021-05-24T17:34:52.543+00:00

@Anonymous . OK but this doesn't answer my key question: Assuming SofarTable would be of type table, what is it supposed to contain? In other words, where does SofarTable come from? Is it the name of an existing query?
Anonymous

2021-05-24T19:46:18.217+00:00

Reviewed Miguel Escobar's blog in the parameters as headers section. I created an empty table from query: Sofar = Table.FromList({},null,{"grpmems"})

I followed Miguel's example of adding a column to my FolderPath table. The column FolderPath has two entries of folder paths (and I can add paths without changing code). Then selected add a column and invoke custom function as he describes. The added column has a table in each row. Expand the tables and remove unneeded columns and I have what I wanted without messing with the Parameter Manager. Learn something new every day. Would have provided code by ran out of characters. Thanks for the discussion.
Lz._ 9,016 Reputation points

2021-05-25T08:27:56.707+00:00

@Anonymous . I now understand what you wanted to do. Thanks for posting what you implemented + for the link to Miguel's blog post that can help others. Take care...

Share via

Issues with Power Query Parameter Manager

3 answers

Your answer