Custom Function Parameter - passing Table as argument

[Dan Bliss]

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.

(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:

let
    Source = ListNonNullColumns("Table11")
in
    Source

Hand corrected to:

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

(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?

 

[MarcelBeug]

This is the correct syntax:

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

 

[Dan Bliss]

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.

[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?

 

[MarcelBeug]

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 .