cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
primolee
Helper V
Helper V

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

1 ACCEPTED SOLUTION
primolee
Helper V
Helper V

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.

View solution in original post

3 REPLIES 3
primolee
Helper V
Helper V

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
Super User
Super User

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"

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!