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

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

Need Help

Anonymous
6 years ago
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

Not applicable

6 years ago

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?