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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bechi
Frequent Visitor

Power query row count changes when refreshed

I'm working with a dataset that contains approx 10 million rows and the source is a text file. I'm finding that if I click refresh in Power Query, even if there has been no change to the text file, the row count changes. This happens whether I use Power Query in Excel or in Power BI. The only transformations being done to the file are to connect to the source and use the first row as headers. I've tried adding an index column and I've tried including a sort on one of the columns and the row count still changes. Has anyone experienced this and how did you fix it? 

3 REPLIES 3
bechi
Frequent Visitor

Thanks! So I did a test where I have a text file as my data source and that file has around 1.3 million records in it. I referenced the query bringing in the data source and the only transformation I did in the reference query was to filter the table on a particular field. I then loaded the resulting table into Excel. The first time I did that the table had 25,162 records in it. I then refreshed and, with no changes to the text file, the refreshed table that was loaded has only 25,134 records in it. If I refresh a third time, the table has only 24,780 records in it. The same thing happens if I repeat the experiment in Power BI. The data loaded into the data model changes quantity every time I refresh. 

That definitely should NOT be happening. It is possible your cache is corrupt. Try clearing it in Power BI.

edhans_0-1658504447523.png

If that doesn't help, then I'd either need to see the text file (if it isn't confidential info you can share via a OneDrive link here in the forum) or you should raise a support ticket.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
edhans
Super User
Super User

Power Query does some sampleing and caching. It holds NO data, so will not hold 10M records. A few thousand at best.

The result should be correct once you load it to the data model, or to an Excel spreadsheet - which cannot hold 10M records either.

If you created field with the row count for a table and loaded that single field single record table, it should work as well as it will be guaranteed to process all of the data, but just clicking REFRESH in the PQ window isn't a super reliable indicator of results - isn't designed to be. Remember, the only data in Power Query is your M code. Not a single byte of data is actually stored there. Only what is loaded to Excel or the data model.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors