How to Refer to Tables and Table Name Variables in Custom Functions

[dahya_mistry]

I'm struggling with referencing Tables (as type table) and Tablenames (as type text) in a Custom Function. I have the following (part of a function) where the bolden items need to refer to the Table variable ( RandomTableSource) as a Table and also as a String/Text in the same line, but I can't figure out how to do this?

(DatasetName as table, IDToReplace, RandomTableSource as table, RandomDataIDColumn as text ) =>

let
// ******************* First Remove duplicates from original dataset and clean data ************************

// Group the ID Column rows to get Distinct Values
#"Grouped Rows" = Table.Group(DatasetName, {IDToReplace}, {{"Other Columns", each _}}),

// Remove other columns
#"Removed Other Columns" = Table.RemoveColumns(#"Grouped Rows",{"Other Columns"}),

// Remove duplicate IDs
#"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),

// Remove Blank IDs
#"Removed Blank Rows" = Table.SelectRows(#"Removed Duplicates", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),

// Add an Index as we'll need it to join datasets
#"Added Index" = Table.AddIndexColumn(#"Removed Blank Rows", "Index20", 1, 1, Int64.Type),

// Merge the data with our Random Clients dataset
MergedWithRandClientData = Table.NestedJoin(#"Added Index", {"Index20"}, RandomTableSource, {"Index99"}, RandomTableSource , JoinKind.LeftOuter),

I've used lines like the following to refer to the Table (where it is passed as a text item to the function):

RandomTableSource = List.First(Table.SelectRows(Record.ToTable(#sections[Section1]), each [Name] = RandomTableSource)[Value]),

but I then get the following error when I "Close and apply" in Power Query, although the table shows up fine in Power Query and it doesn't generate any other errors anywhere else:

"Failed to save modifications to the server. Error returned: 'OLE DB or ODBC error: [Expression.Error] We cannot convert the value null to type Table.. An unexpected exception occurred. "

I've also had to turn the privacy off for the report in order to try the line which uses #sections, otherwise I get a firewall error.

So in summary, I need help in:

1. Finding out how to refer to table variables both as a table/query variable AND as a text variable; and

2. Finding out why I get this "OLE/ODBC....null to type table" error  (I think it comes from the use of the #sections line)?

[AntrikshSharma]

@dahya_mistry The last time I checked #sections resulting tables aren't allowed to be loaded into data model.

[dahya_mistry]

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 !

[PwerQueryKees]

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

[dahya_mistry]

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

[PwerQueryKees]

I was also wondering. How do you tables get in powerquery in the first place? Can't you insert some code to keep track of the tables created there? 

[PwerQueryKees]

I was messing with #sections when I saw you were using it. I beleive it could be a solution to your problem.

So I ried to filter on only tables: Is.Type(#sections[value], type table)

In my case it happened one of my queries contain an error.

Doing the SelectRows produces the error in my erroneous query.

So using the [Value] appears a bit fragile... 

No solution, but may be my observation helps...

[Anonymous]

Whoah you're all up in the #sections that's pretty advanced--is all that necessary?

Anyway, in your first example you're using RandomTableName as the table for your nested join, but then as a table for the name of the nested column, which should be text--perhaps add NewTableName as text to your function.

--Nate

[dahya_mistry]

Thanks Nate, that would probably work, but I did think of that and I'd rather reduce duplication in my (all of my) code as it can make things confusing too. Also I'm ideally looking for a long term solution that I can apply to future custom functions that I will be creating too.

[dahya_mistry]

Ive tried the last simple script a few times now, but using the GUI, so two step as follows:

= Table.SelectRows(Record.ToTable(#sections[Section1]), each [Name] = "SQLServerData")[Value]
= Source{0}

And when I click on APPLY, I still get the error "....(first) column does not exist in the rowset.."

The error always seems to refer to the first column in the dataset, no matter if the dataset is an excel worksheet or a table from SQL Server.

[dahya_mistry]

When I do the following simple PQ script:

(DatasetName as table, IDToReplace, RandomDataTableName as text, RandomDataIDColumn as text ) =>

let
RandomTableSource = List.First(Table.SelectRows(Record.ToTable(#sections[Section1]), each [Name] = RandomDataTableName)[Value]),

in
RandomTableSource

I still get an error as below, with the data showing the invoked function:

It's saying that the Person ID doesn't exist in the rowset, but yet I can clearly see that it does in the invoked function table !!!