Custom Function Parameter - passing Table as argument

Dan Bliss

Dan Bliss

New member
Joined
Dec 7, 2016
Messages
45
Reaction score
0
Points
0
Excel Version(s)
Office 365
Table.Profile is a neat function for showing basic descriptive statistics of each column in a table. I want to wrap this in a custom function, "ListNonNullColumns", so I can easily see which columns in my table have data. I want to pass the table name to the function. How do I do this properly?

Try #1: where aTable is "Any" type.

Code: 
(aTable) =>


let
    Source = Table.Profile(aTable),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Average", type number}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Average] <> null))
in
    #"Filtered Rows"


Invoking ListNonNullColumns produces this, which has error:
Code: 
let
    Source = ListNonNullColumns("Table11")
in
    Source
Hand corrected to:
Code: 
let
    Source = ListNonNullColumns(Table11)
in
    Source

To eliminate the error passing text when I need to pass a table, I change the type of the parameter:
Try #2: where aTable is type table

Code: 
(aTable as table) =>
The function itself has no errors, but if invoked by typing Table11 into "Enter parameter" text field in UI, Power Query complains "Enter a Table Value". Entering #Table11 does not help.

So what is the correct function parameter syntax?
 
This is the correct syntax:

Code: 
(MyTable as table) =>
let
    Result = Table.Profile(MyTable)
in
    Result

I suspect you want to enter an Excel table name as parameter value, but you need to enter a Power Query table, so you need to load your Excel table into Power Query first.
 
Don't understand Invoking Custom Function w Parameter - Table type

Marcel,
Thanks again for your help.
In my earlier example Table11 is loaded into PQ - I don't think that is the source of my problem.
And the following runs without a problem.
Code: 
[COLOR=#3E3E3E]let[/COLOR]    Source = ListNonNullColumns(Table11)
in [COLOR=#3E3E3E]    Source[/COLOR]
Let me clarify here.
My custom function, ListNonNullColumns runs fine when used in sample queries (like the one above), without error & generating expected results. And it runs fine regardless of parameter type: Whether as ListNonNullColumns(table as table) or ListNonNullColumns(table as any), Power Query M parses it correctly. But I agree entirely with Marcel that (table as table) is preferable as a parameter specification.

My problem is I don't understand how to use invoke. Why do I get errors described earlier, and how can I fix it so no errors arise when invoking ListNonNullColumns()?

Any thoughts here?
 
My suspicion is that you want to invoke the function when adding a new column to your table?
Well, you won't get this table (or any of the earlier query steps) in your dropdown.

I created an example with 2 tables, Table1 and Table2,
The function can be invoked with either tables, but if you want to invoke the function when adding a column, you can only choose the other table(s), as illustrated in this video .
 
You must log in or register to reply here.
Back
Top