Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
KilleenJeffrey
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
KilleenJeffrey
Frequent Visitor

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
KilleenJeffrey
Frequent Visitor

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

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?  

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

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous
Not applicable

@ImkeF Thanks alot this is a really powerfull pattern.  

ImkeF
Super User
Super User

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

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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.

Hi @KilleenJeffrey,

yes, it is.

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?

 

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

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors