M Language - Custom Functions -> Passing a Table by a string name

[KilleenJeffrey]

This is very specific to writing a Power Query Custom Function.  Keep that in mind when responding because I am not looking for a another way to do it without a Custom Function.

I want to create a function that takes a table and group it by a KeyField creating a "count colunm" with the name of the original table before grouping.

For purposes of this example lets assume the following - A source table for an SQL Server called "PartsInventory_NorthAmerica" and that table has a column called "PartNumber"

I want a generalized function that will return a table with 2 columns...

[PartNumber] [PartsInventory_NorthAmerica]

The second column name will be the same as the source table name.  Once this Custom Function is working I can pass it other "PartsInventory" Tables (e.g.  PartsInventory_LatinAmerica, PartsInventory_EMEA).  The 2nd column in the function return would change name based on the source table name.  Obvoiusly the function outouts can be merged based om PartNumber creating a WW Parts Inventory count list.

I would like to pass to this Custom Function (RegionPartsCount)

a) RegionPartsCount = (Table as table, PartNumber as text) =>

or

b) RegionPartsCount = (TableName as text, PartNumber as text) =>

The problem with "a" is I have not been able to find the name of the table I passed

The problem with "b" is I have not been able to use TableName to reference the table object

Problems like will become more common as people more and more write their own Custom Functions

The actual Custom Function I am writing does many things - I just stripped it down to the basics for this posting.

Is there a way to get the name of the table from the table or to substaniate a table based upon a text varible?

[KilleenJeffrey · Accepted solution]

Here  is the solution...

let

    TargetTable = (TargetTableName as text) as table=>

      let

        TargetTable = Record.Field(#sections[Section1], TargetTableName)

      in

        TargetTable,

      MyTable = TargetTable("ccontl")
in
    MyTable

[Anonymous]

Hi @KilleenJeffrey  and @ImkeF  This approach is very cool.  I have been struggling with finding a way to integrate strings into expression values.  @KilleenJeffrey Your soloution is great!  Do you think there is a way to further refine it and remove the need to cosider it a table and simply a variable or are we trapped by type?  

[ImkeF]

Hi @Anonymous ,

the definition of the output type is optional, so if you remove it, you can use this query to reference queries of any type.

let
    fnTargetQuery = (TargetQueryName as text) =>
     
        Record.Field(#sections[Section1], TargetQueryName),

    CallFunction = fnTargetQuery("ccontl")
in
    CallFunction

... code is slighty rewritten ...

[Anonymous]

@ImkeF Thanks alot this is a really powerfull pattern.  

[ImkeF]

Hi @KilleenJeffrey 

if you want to refresh your query in the service, you have to use a)

Otherwise you could use Expression.Evaluate for b)

Unfortunately I don't yet understand what your problem with solution a) is. What exactly do you mean with "I have not been able to find the name of the table I passed"?

[KilleenJeffrey]

Before I explain further - is this the correct section to be posting a very advance "M Language" question in?  This does not involve Power BI Visuals and does not involve the Power Query UI.   This is a pure code question.

[Nolock]

Hi @KilleenJeffrey,

yes, it is.

[KilleenJeffrey]

OK Then - Let me give a simple example from VBA

Option "a"

SpreadSheetName = "PartsInventory_NorthAmerica"

OutputFromFunction = WhatIsMySpreadsheet(SpreadSheetName)

MsgBox (OutputFromFunction.Name )

Function WhatIsMySpreadsheet (NameOfSheet as Text) as Worksheet

      WhatIsMySpreadsheet =Worksheets(NameOfSheet)

End Function

In Option "a" the name of the worksheet is passed as a string (NameOfSheet) and it is converted in to the actual worksheet object (Worksheets(NameOfSheet) which the function returns.

Option "b"

MySpreadSheet = Worksheets("PartsInventory_NorthAmerica")

OutputFromFunction = WhatIsTheNameOfMySpreadsheet(MySpreadSheet)

MsgBox (OutputFromFunction)

Function WhatIsTheNameOfMySpreadsheet (TheSheet as Worksheet) as String

      WhatIsTheNameOfMySpreadsheet =Worksheets(NameOfSheet)

End Function

In Option "b" the worksheet is passed as an object (MySpreadSheet) and the Property Name of the actual worksheet object  (WhatIsTheNameOfMySpreadsheet ) is return by the function.

Back to the M Language

Custom Functions are very powerful way of solving a bunch of issues.  Ideally one wants to write generalized functions that are reusable.

Option "a" - reference a Table via a text variable

If we had a function we wanted to pass a table name and field name the field name part is easy.

FunctionFooBar = (MyTableName as text, MyFieldName as text) =>

for the Field part one can get the actual field using in a "M" function each Record.Field(_, MyFieldName)

What I can't find is a means to reference the table by a string variable

Option "b" - reference a Table Name via Table Object

FunctionFooBar = (MyTable as table, MyFieldName as text) =>

What I can't find is a means to reference the table's name

--------------

The above examples are contrived for purposes of discussing how the language works - the examples are not real business problems.

Once one goes down the road of writing generalized function the passing parameters by reference quickly becomes an issue.  I was able to solve it for fields (Record.Field) but I haven't found a way to do it for tables.  Any ideas?

[ImkeF]

Hi @KilleenJeffrey 

now I understand what you mean. I have been there before: https://social.technet.microsoft.com/Forums/en-US/aa6e667f-fa2b-4922-8d81-8b306a492395/restrictions-... 

and here: https://social.technet.microsoft.com/Forums/ie/en-US/208b9365-91e9-4802-b737-de00bf027e2a/alternativ... 

Like I said: When you want to use it with a refresh in the service, you're out of luck - there is currently no way.

A related idea to vote for: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/9312540-make-functions-refreshabl...