Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Kieran
Advocate II
Advocate II

How do I return a count of records for all tables

Hi,

How do I return a count of records for all tables using Power Query please?

 

Kind Regards,

 

Kieran

1 ACCEPTED SOLUTION
ManuelBolz
Responsive Resident
Responsive Resident

Hello @Kieran,

I don't know if this is a good idea - reading out your entire dataverse.

In the following query I have adapted the code to your Dataverse. I also added the “FilterFirstNRows” step. The number 5 is the number of tables to be returned. If you remove this step you will get your result.

 

let
    AllTables = CommonDataService.Database("myDataverse.crm4.dynamics.com"),
    FilterRows = Table.SelectRows(AllTables, each ([Name] <> "CountQuery")),
    FilterTables = Table.SelectRows(FilterRows, each Value.Is([Data], type table)),
    FilterFirstNRows = Table.FirstN(FilterTables,5),
    ColumnRowCount = Table.AddColumn(FilterFirstNRows, "RowCount", each Table.RowCount([Data])),
    Result = Table.SelectColumns(ColumnRowCount, {"Name", "RowCount"})
in
    Result

 

Best regards from Germany

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Kieran
Advocate II
Advocate II

Thank you @ManuelBolz ,
I need to provide more context. I need to count all the rows in all the tables for my data source. Where my data source is Dataverse and when querying Dataverse within SSMS you can't use the normal system view to count rows which is called SELECT * FROM sys.partitions. Where this solution is mentioned in the following link ... https://www.mssqltips.com/sqlservertip/2537/sql-server-row-count-for-all-tables-in-a-database/

So I tried your code ...

 

let
    AllTables = #shared,
    RecordToTable = Record.ToTable(AllTables),
    FilterRows = Table.SelectRows(RecordToTable, each ([Name] <> "CountQuery")),
    FilterTables = Table.SelectRows(FilterRows, each Value.Is([Value], type table)),
    ColumnRowCount = Table.AddColumn(FilterTables, "RowCount", each Table.RowCount([Value])),
    Result = Table.SelectColumns(ColumnRowCount, {"Name", "RowCount"})
in
    Result

 

 

This returned a count of the rows within all Power Query queries.
So I updated your code to the following ...

 

let
    AllTables = CommonDataService.Database("myDataverse.crm4.dynamics.com"),
    RecordToTable = Record.ToTable(AllTables),
    FilterRows = Table.SelectRows(RecordToTable, each ([Name] <> "CountQuery")),
    FilterTables = Table.SelectRows(FilterRows, each Value.Is([Value], type table)),
    ColumnRowCount = Table.AddColumn(FilterTables, "RowCount", each Table.RowCount([Value])),
    Result = Table.SelectColumns(ColumnRowCount, {"Name", "RowCount"})
in
    Result

 

 

So when clicked on the AllTables step I received the following result ....

Kieran_0-1717661219108.png

However, when I clicked on the RecordToTable step I received the following result ...

Kieran_1-1717661296953.png

Expression.Error: We cannot convert a value of type Table to type Record. Details: Value=[Table] Type=[Type]

Any further help would be greatly appreciated.
Kind Regards,
Kieran Wood.
Kieran Wood, Microsoft Certified Fabric Analytics Engineer, Azure Data Engineer, Data Analyst, Administrator and ...




 

ManuelBolz
Responsive Resident
Responsive Resident

Hello @Kieran,

I don't know if this is a good idea - reading out your entire dataverse.

In the following query I have adapted the code to your Dataverse. I also added the “FilterFirstNRows” step. The number 5 is the number of tables to be returned. If you remove this step you will get your result.

 

let
    AllTables = CommonDataService.Database("myDataverse.crm4.dynamics.com"),
    FilterRows = Table.SelectRows(AllTables, each ([Name] <> "CountQuery")),
    FilterTables = Table.SelectRows(FilterRows, each Value.Is([Data], type table)),
    FilterFirstNRows = Table.FirstN(FilterTables,5),
    ColumnRowCount = Table.AddColumn(FilterFirstNRows, "RowCount", each Table.RowCount([Data])),
    Result = Table.SelectColumns(ColumnRowCount, {"Name", "RowCount"})
in
    Result

 

Best regards from Germany

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Yes,
This works perfectly thank you.
Longer term I intend to raise a Microsoft ticket to backup the Dataverse and restore the Dataverse as an Azure SQL Database. This would enable me to manage the meta-data much more effectively. However, this will take time so getting all the tables which have a record count of more than one is a good starting indicator of what is used. Thanks again @ManuelBolz 

ManuelBolz
Responsive Resident
Responsive Resident

Hello @Kieran,

I don't know if I understood you correctly, but please try the following solution.

1) Go to Power Query and create a new query
2) Rename the query to "CountQuery"
3) Go to Advanced Editor and try the following Code:

let
    AllTables = #shared,
    RecordToTable = Record.ToTable(AllTables),
    FilterRows = Table.SelectRows(RecordToTable, each ([Name] <> "CountQuery")),
    FilterTables = Table.SelectRows(FilterRows, each Value.Is([Value], type table)),
    ColumnRowCount = Table.AddColumn(FilterTables, "RowCount", each Table.RowCount([Value])),
    Result = Table.SelectColumns(ColumnRowCount, {"Name", "RowCount"})
in
    Result


Did I answer your question? Please mark my post as a solution!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors