Forum Discussion

primolee's avatar
primolee
Helper V
4 years ago
Solved

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

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 a...
Need Help
  • primolee's avatar
    primolee
    4 years ago

    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's avatar
ImkeF
Community Champion
4 years ago

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 BIccountant)

 

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's avatar
    primolee
    Helper V
    4 years ago

    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