Forum Discussion
Continued ContributorHandling Fast Refresh for a 50M-Row Dataset in Power BI
Hi Community,
I have a dataset with 50 million rows, 100+ columns, a unique identifier (e.g., ID), and an FIELD_UPDATEDTIME column allowing updates to any row (even 10 years old), full refreshes would be extremely slow and resource-intensive. What I'm using now is a semantic model with incremental refresh, with a Power BI Premium Per User capacity. It partitions data and processes only what's needed.
I'm using a stable partitioning column (immutable date, like PartitioningDateColumn—not FIELD_UPDATEDTIME, as changes to it can cause duplicates by "moving" rows between partitions). This is the configuration:
- RangeStart and RangeEnd are default dates to get all the range of data that I need.
- In Power Query: Add RangeStart/RangeEnd parameters; filter table on stable date (e.g., PartitioningDateColumn >= RangeStart &&PartitioningDateColumn < RangeEnd).
- In model: Right-click table > Incremental refresh > Configure policy, enable detect changes (select FIELD_UPDATEDTIME), optional hybrid.
- Publish; initial full refresh is long (once), then scheduled refreshes are fast (minutes if changes are sparse).
However, the process keeps duplicating the data and in Semantic Model Settings, it's not partitioning as expected on each semantic model. What could be wrong? Does anyone has any idea or possible solution to solve this problem?
Hi julsr
Your setup looks good, but duplicates usually happen from a partition mismatch.
I suggest these fixes:- Check your Power Query filter - use < not <=:
[PartitioningDateColumn] >= RangeStart and [PartitioningDateColumn] < RangeEnd- Enable "Only refresh complete periods" in your incremental refresh settings
- Verify data types match - RangeStart, RangeEnd, and PartitioningDateColumn should all be DateTime
- Do one full refresh to reset partitions, then check if the next incremental refresh still duplicates
The issue is likely that when FIELD_UPDATEDTIME changes, Power BI fetches the row but doesn't remove it from the old partition.
Check your refresh history in the service, is it actually running "Incremental" or falling back to "Full"?
Did it work? 👍 A kudos would be appreciated
🟨 Mark it as a solution to help spread knowledge 💡
5 Replies
- DataVitalizer
Super User
Hi julsr
Your setup looks good, but duplicates usually happen from a partition mismatch.
I suggest these fixes:- Check your Power Query filter - use < not <=:
[PartitioningDateColumn] >= RangeStart and [PartitioningDateColumn] < RangeEnd- Enable "Only refresh complete periods" in your incremental refresh settings
- Verify data types match - RangeStart, RangeEnd, and PartitioningDateColumn should all be DateTime
- Do one full refresh to reset partitions, then check if the next incremental refresh still duplicates
The issue is likely that when FIELD_UPDATEDTIME changes, Power BI fetches the row but doesn't remove it from the old partition.
Check your refresh history in the service, is it actually running "Incremental" or falling back to "Full"?
Did it work? 👍 A kudos would be appreciated
🟨 Mark it as a solution to help spread knowledge 💡 - lbendlin
Note that any meta data change and re-publishing from Power BI Desktop to the service will result in the destruction of all partitions and will necessitate the re-filling of these on the next refresh.
To avoid that use XMLA tools like ALM Toolkit to selectively update the meta data, and use scripts to selectively refresh individual partitions. Optionally you can bootstrap (for example via Tabular Editor).
- cengizhanarslan
This symptom (incremental refresh creates duplicates / partitions not behaving) almost always comes down to one of these causes:
1) Your “stable partition column” is not truly stable (or not at day-grain)
Even if it looks immutable, duplicates appear if:
the column is datetime and values change slightly (timezone / rounding / late corrections)
you’re partitioning by something that can be re-derived differently (ETL logic change)
rows can be re-written with a different partition date (backfill logic)
Quick check: pick a few duplicated IDs and see if they land in different partitions (different PartitioningDateColumn values). If yes, the partition key isn’t stable in practice.
Fix: partition on a truly immutable “created date” or “ingestion date” that never changes.
2) Detect data changes is pointing to a column that can go backwards / null / non-monotonic
“Detect data changes” expects something that increases when a row changes. If FIELD_UPDATEDTIME can be:
null sometimes
updated with delayed timestamps
written in local time inconsistently
then Power BI can re-process partitions in unexpected ways.
Fix: ensure FIELD_UPDATEDTIME is always populated, consistent timezone (UTC), and only moves forward per row.
Hi julsr,
Thank you for reaching out to the Microsoft Fabric Forum Community, and special thanks to DataVitalizer , lbendlin and cengizhanarslan for prompt and helpful responses.
Just following up to see if the Response provided by community members were helpful in addressing the issue. if the issue still persists Feel free to reach out if you need any further clarification or assistance.
Best regards,
Prasanna Kumar- julsr
Thank you everyone for the insights! Using a right partition and ensuring that all the data is covered by the time window on the Incremental Refresh settings solved the problem
