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 add a new column using Table.RowCount and use the query-name column as parameter.  Is there a way of achieving this?

 

Table NameRow Count
Query A 
Query B 
QueryC 

 

Thank you.

 

Best regards,

David

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.

3 Replies

  • 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

  • primolee's avatar
    primolee
    Helper V
    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.