After a means to compare Table record counts across Report refreshes

[RD1965]

I have a several reports that all use an ODATA connection to a PostgreSQL DB. Reports refresh daily and we've noticed that sometimes a report will bring back a few records less than the previous refresh.  The reports refresh successfully but this is starting to impact user belief in the reports.

So I'm after a means to be able to compare the number of records in each Table across Report refreshes.

e.g.

Report1 Table A had 15000 records on day 1, then on Day 2 it had 14900.  Table 2 had 20000 on day 1 and day 2.

Thanks

[v-achippa]

Hi @RD1965,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @jaineshp for the prompt response.

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa

[RD1965]

Hi,

How would I go about doing step 1?

All I need in the CountHistory table is a timestamp and a count of the number of rows in a Table called "Source".

[v-achippa · Accepted solution]

Hi @RD1965,

You can do this directly in the Power Query. Power query inside a PBIX or dataset is read-only. It can create a one row snapshot on each refresh, but it cannot append to a persistent history by itself. Power bi itself does not keep history across refreshes so this will show the current refresh row count.

In power query, open Advanced Editor and paste the below following code:

let

    RowCount = Table.RowCount(Source),

    RefreshTime = DateTimeZone.UtcNow(),

    CountHistory = #table(

        {"TableName", "RowCount", "RefreshTimestampUTC"},

        {{"Source", RowCount, RefreshTime}}

    )

in

    CountHistory

• Replace Source with the name of your actual query and make changes according to your data.
• Name this query as CountHistory and make sure Enable load is turned on so it appears in your model.

This will give you a table with the row count of source and the refresh timestamp. Each time the dataset refreshes, the row will update with the new count.

If this post helps in resolving the issue, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thanks and regards,

Anjan Kumar Chippa

[jaineshp]

Hey @RD1965,

Based on your requirement to monitor table record counts between daily refreshes, here are proven approaches to address this issue:

1. Power Query Historical Count Tracking

• Create a dedicated "Data Quality" table in each report
• Use Table.RowCount() to capture current row counts for each table
• Add DateTime.LocalNow() for timestamp tracking
• Configure table to append historical data rather than replace
• Set up conditional formatting to highlight significant count drops (>5% variance)

2. Database Audit Table Implementation

• Create audit tables in PostgreSQL to log daily record counts
• Schedule stored procedure to run before Power BI refresh window
• Include table name, record count, and date stamp
• Connect Power BI to audit tables via separate ODATA feed
• Build variance dashboard showing day-over-day changes

3. Power BI REST API Monitoring Dashboard

• Build automated monitoring report using Power BI REST APIs
• Query DISCOVER_STORAGE_TABLES DMV for accurate table statistics
• Create separate dataset that tracks all reports centrally
• Set up email alerts when count variations exceed defined thresholds
• Implement trend analysis to identify patterns in data loss

4. Automated PowerShell Validation Script

• Deploy PowerShell script to run post-refresh validation
• Extract table metadata using Invoke-PowerBIRestMethod cmdlets
• Compare against baseline counts stored in SharePoint list or SQL table
• Generate exception report highlighting significant variances
• Send automated notifications to report owners when issues detected

Implementation Priority: Start with database audit tables for most reliable tracking, then layer on Power BI dashboard for user visibility. This combination provides both technical validation and business transparency around data quality issues.

Fixed? ✓ Mark it • Share it • Help others!

Best Regards,
Jainesh Poojara | Power BI Developer