How do I return a count of records for all tables

[Kieran]

Hi,

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

Kind Regards,

Kieran

[Kieran]

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 ....

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

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 · Accepted solution]

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.

[Kieran]

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]

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!