Forum Discussion
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?
9 Replies
Hi Anonymous and ImkeF This approach is very cool. I have been struggling with finding a way to integrate strings into expression values. Anonymous 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 ...
Hi Anonymous
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"?
Hi Anonymous,
yes, it is.
