Forum Discussion
How to Refer to Tables and Table Name Variables in Custom Functions
Thanks everyone for your input. Here's my responses:
AntrikshSharma : #sections tables aren't allowed to tbe loaded....etc...I am aware of not being able to use it in the PBI Service, and that you can get Firewall issues in PBI Desktop which you can avoid by chaning the privacy setting for that fil
PwerQueryKees : The tables are just spreadsheets at the moment
Everyone:
My main issue that I would like to resolve is the following which could be used for any function:
Finding out how to refer to table variables both as a table/query variable AND as a text variable;
What I'm attempting to do is to create a User Defined Function that can be applied to any Table that I feed to the function. At the moment I have gone with thw workaround that Anonymous suggested, which is to have two parameters for the specified Table, one as a table type, and one as a string, not ideal, but it works at the moment !
Super UserIf the tables are from Excel, I would look into doing the selection while pulling the data into powerquery.
In the simples form, you could manually create a query that produces a table with the name/table mapping.
like
#table({" TableName", " Table" },
{{"Customer1", #"Customer1",
{" Customer2, #"Customer2",}}
You can make it as advanced as you like by replacing the Queryes with a custom function loading the data.
This way of working will also clean up you list of queries and reduce the (I am assuming) copying of very similar logic to load the tables.
I realize I don't understand your work flow, but maybe this is for for thought...
That siounds like an interesting solution to be honest. I've tried creating a table as you suggested, but now I'm struggling to figure out how to refer to the Table in a function. So in your example, if I wanted to refer to the table #"Customer1", what PQ code can I use which will bring back the contents of that table as a table ?
eg do I do something like:
= #table[TableName] = "Customer1"or something like:
= Table.SelectRows(....... ????)
will this bring back the contents of the Customer1 table ?
I'm still trying to learn PQ so some things aren't obvious to me yet !- PwerQueryKees
#"Customer1"
It is that simple...