Using column value as table name as a parameter to trigger a function

[primolee]

Hello everyone,

I have several queries that I would like to count their rows and store in a different table.

I have created a one-column table with the query names in the column.  I want to add a new column using Table.RowCount and use the query-name column as parameter.  Is there a way of achieving this?

Table Name	Row Count
Query A
Query B
QueryC

Thank you.

Best regards,

David

[primolee · Accepted solution]

I got it working.

Create a new column using the following code:

Table.AddColumn(previousStepName, "RowCount", each Table.RowCount(Record.Field(#shared,[Table Name])))

However, I think #shared still does not work in BI Service, so alternative is to create a record listing function names:

tableList = [
     #"Query A" = #"Query A",
     #"Query B" = #"Query B",
     QueryC = QueryC,
],

Then, change #shared to tableList:

Table.AddColumn(previousStepName, "RowCount", each Table.RowCount(Record.Field(tableList,[Table Name])))

This will work on Power BI Service and refresh with no problem.

[ImkeF]

Hi @primolee ,

this can be done, but if you want to refresh it in the service, you have to define an environment record (here is a link on how this can be done automatically, if you have a very large number of queries for example: Automatically create function record for Expression.Evaluate in Power BI and Power Query – The BIcco...)

You add a column with this formula:
Expression.Evaluate("Table.RowCount(" & [Table Name] & ")", [#"Query A" = #"Query A", #"Query B" = #"Query B", #"Query C" = #"Query C"])

If your query table names contain spaces, you have to adjust your table like so:

Table Name

#"Query A"

#"Query B"

#"Query C"

[primolee]

Hello @ImkeF 

Thank you so much for your reply.  As you were replying, I also figure out using Record.Field.

Table.AddColumn(previousStepName, "RowCount", each Table.RowCount(Record.Field(#shared,[Table Name])))

And since #shared might still not work in Power BI Service refresh, simply create a table name record as what you wrote in your reply and it will work on BI Service.

Thank you once again for helping me again.  Thanks!

Best regards,

David