Number of rows loading from query != number of rows shown in table view

[grggmrtn]

I have what I consider to be a relatively simple query with a lot of data. The source is SQL, and my transformations are limited to loading specific columns from the same table, value filters on the columns, nothing too complicated.

When I push close & load PowerBI applies my changes, and cycles through the number of rows it's loading. It goes up to about 45 million in this report, and it cycles through twice.

However, in the table view afterwards, it's only showing 2.7 million rows.

This is the first time I've seen such a discrepancy. Does anyone have any idea why the difference?

[v-lionel-msft]

@grggmrtn,

Can you show your M code in "Advanced Editor"?

Best Regards,
Lionel Chen

[grggmrtn]

It's over 400 lines and in Danish - you sure you want to see it?

[v-lionel-msft · Accepted solution]

Hi @grggmrtn ,

I had understanded what you mean, about your problem, I think this involves the compression mechanism of the original data by Power BI.

You can refer to the articles.

https://docs.microsoft.com/en-us/power-bi/guidance/import-modeling-data-reduction 

https://www.microsoftpressstore.com/articles/article.aspx?p=2449192 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[grggmrtn]

This looks like exactly the information I need. Thanks for your help @v-lionel-msft !

[tex628]

Hi, 

Could you possibly do a count rows as the last step in the query and post the result:


As well as post a picture of the row count once you've loaded:


The issue should be related to some transformation in the query step but this should shred some more light on it! 

Br,
J

[grggmrtn]

Hmm, funny. Both "count rows" and the row count in table view are now the same - 2715211

[tex628]

Go step by step in the transformation stages and do row counts and you should bea ble to identify which step that is resulting in the decrease of rows!

[grggmrtn]

I think my question is more about why PowerBI is loading so many, if the end result is so much smaller, and why it's cycling twice?

[tex628]

I honestly I'm not quite sure. My guess is that it has to do with the way the transformations are structrued but Im really not good enough at PowerQuery to give you a good answer. I'd advice you to create a new post and ask specifically what the "Applying changes" window specifys, how the row count there relates to the amount of actually loaded rows and why it's showing that it's loading the rows twice.

Br,
Johannes