Referencing a table given in column as text

3
votes

this should be an easy question. I'm fairly new to Power Query.

I have a report table, there is a column "Queries" which are names of queries i have in my workbook. I wish to add a column to count the number of rows in the queries.

The formula i use is =Table.AddColumn(Source, "RowCount", each Table.RowCount([Query]))

My report table would looks like below:

| Queries   | RowCount |
| Qry Apple |          |
| Qry Orang |          |

However I am getting the error:

Expression.Error: We cannot convert the value "Qry Apple" to type Table.

Details:

Value=Qry Apple

Type=Type

Does anyone know how to solve this?

Thanks!

3
powerquery

3 Answers

1
votes
= Table.AddColumn(Source, "Row Count", each Table.RowCount(Expression.Evaluate([Query],#sections[Section1])))

It seems like this is one of those things that requires random obscure knowledge about the structure of PQ. Expression.Evaluate needs to know the "environment" to resolve the string in, and it appears tables in PQ are sitting in a record called [Section1] in a global query called #sections.

1
votes

I found a solution to this from Chris Webb's BI Blog: Expression.Evaluate() In Power Query/M.

Basically, we need to use Expression.Evaluate in order to read the text in the [Query] column as a table. Note also that you need to include the #shared parameter so it has access to the necessary environment. (For more details, see the linked blog and the references it gives.)

= Table.AddColumn(Source, "RowCount", each Table.RowCount(Expression.Evaluate([Query], #shared)))
0
votes

Finally, found the answer, after a year of practicing,

I tried using Expression.Evaluate as suggested but to no avail, hence I don't think the function can properly convert a text into a #table. I stand to be corrected.

The solution do make use of #sections, thanks so much for the brilliant idea by @Alexis Olson and @Wedge!

I used the Record.Field function to "Get" the tables into a column as table objects, then finish it off with the Table.RowCount function. For clarity, I split them into two steps.

So here it is:

let
    Source    = Excel.CurrentWorkbook(),
    MyTables  = Source{[Name="MyTables"]}[Content],
    GetTblObj = Table.AddColumn(MyTables, "MyTables", each Record.Field(#sections[Section1],[Query])),
    RowCount  = Table.AddColumn(GetTblObj, "RowCount", each Table.RowCount([MyTables]))
in
    RowCount

enter image description here