cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KilleenJeffrey Frequent Visitor
Frequent Visitor

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
KilleenJeffrey Frequent Visitor
Frequent Visitor

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

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

View solution in original post

9 REPLIES 9
Super User
Super User

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

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

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




KilleenJeffrey Frequent Visitor
Frequent Visitor

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

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.

Super User
Super User

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

Hi @KilleenJeffrey,

yes, it is.

KilleenJeffrey Frequent Visitor
Frequent Visitor

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

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?

 

Super User
Super User

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

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

 

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Highlighted
KilleenJeffrey Frequent Visitor
Frequent Visitor

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

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

View solution in original post

T-Rock Frequent Visitor
Frequent Visitor

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

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?  

Super User
Super User

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

Hi @T-Rock ,

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

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




T-Rock Frequent Visitor
Frequent Visitor

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

@ImkeF Thanks alot this is a really powerfull pattern.  

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Top Kudoed Authors
Users Online
Currently online: 453 members 4,496 guests
Please welcome our newest community members: