Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
13 | |
11 |