topic Re: After a means to compare Table record counts across Report refreshes in Report Server

After a means to compare Table record counts across Report refreshes

Anonymous — Fri, 08 Aug 2025 11:37:13 GMT

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

Re: After a means to compare Table record counts across Report refreshes

jaineshp — Fri, 08 Aug 2025 11:41:57 GMT

Hey Anonymous,

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

Re: After a means to compare Table record counts across Report refreshes

v-achippa — Mon, 11 Aug 2025 12:42:58 GMT

Hi Anonymous,

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

Re: After a means to compare Table record counts across Report refreshes

Anonymous — Tue, 12 Aug 2025 13:59:48 GMT

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

Re: After a means to compare Table record counts across Report refreshes

v-achippa — Mon, 18 Aug 2025 12:16:49 GMT

Hi Anonymous,

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"},

)

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