question

EugeneCloud-7252 avatar image
0 Votes"
EugeneCloud-7252 asked Lz-3068 commented

Issues with Power Query Parameter Manager

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?

power-query-not-supported
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EugeneCloud-7252 avatar image
0 Votes"
EugeneCloud-7252 answered EugeneCloud-7252 commented

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.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@EugeneCloud-7252
Some pointers if this can help:

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)


0 Votes 0 ·

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.

0 Votes 0 ·

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-3068

0 Votes 0 ·
Lz-3068 avatar image
0 Votes"
Lz-3068 answered EugeneCloud-7252 commented

Hi @EugeneCloud-7252

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:

PIC



· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


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.



0 Votes 0 ·

@EugeneCloud-7252. 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://

PIC

what would you expect that table to contain?


0 Votes 0 ·
demo.png (13.2 KiB)

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 ....

0 Votes 0 ·
EugeneCloud-7252 avatar image
0 Votes"
EugeneCloud-7252 answered Lz-3068 commented

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

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@EugeneCloud-7252. 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?

0 Votes 0 ·

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.






0 Votes 0 ·

@EugeneCloud-7252. 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...

0 Votes 0 ·