Power Query data has less rows than data table

[Powerbyte]

Today I ran into something I've never seen before and should not be possible. I'm wondering if anyone else has seen this as well. I had a PBIX file in import mode with a single table drawing from an SQL view (on premise). The table had a single column with items numbers starting with CW (there where hundreds of rows). When I created the PBIX file and published everything was fine, it showed all rows on the front end table visual as well as in Power Query when I went in via transform data. However this is where it gets weird...I cleaned out the SQL data so that there was only 4 items left that started with CW. When I looked in the published Power BI file it was still showing all hundreds of rows even after a refresh (in workspace and app). A bit confused, I opened the desktop PBIX file and found that when I went to Power Query there was only 4 items left, HOWEVER on the front end table visual there was still the hundreds of rows? It's almost as though the front end table was "remembering" the old data rows even though those rows where no longer available in Power Query. I deleted the file and rebuilt it from scratch and it's now working, but I'm wondering if I'm loosing the plot here. How is it possible for a front end table to "remember" data that was there before but not longer in Power Query?

[DEEPANSHU_B]

I thought , if there is an issue like the loaded file shows column and rows lesser than source. What to do even after checking ETL again. Refresh. 

[v-rongtiep-msft]

Hi @Powerbyte ,

Please check if you disable the option  "Enable Load". "Enable Load" means query results are available for report builder. Otherwise you may use it in your other queries (for example to merge data), but it is not shown in the report builder.

Once you disable the ‘Enable load’ option, the table name turns italic which is an easy way for you to determine whether a table will be loaded or not.

 See: https://radacad.com/performance-tip-for-power-bi-enable-load-sucks-memory-up

Best Regards

Community Support Team _ Polly

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

[TomMartens]

Hey @Powerbyte ,

I never encountered that Power BI remembers things, that are not there.

Maybe here
" ... 
I cleaned out the SQL data so that there was only 4 items left that started with CW. When I looked in the published Power BI file it was still showing all hundreds of rows even after a refresh (in workspace and app). 
..."

... you changed the Power BI Desktop file, but did not publish again?

Sometimes this happens to

• changing the Power BI Desktop without publishing --> workspace content and app are not affected by the changes
• publishing to the workspace but not updating the app --> app content is still not affected by the changes

Maybe one of the two things happened here as well?

Nevertheless, glad you have been able to fix this strange thing.

Regards,

Tom

May be

[Powerbyte]

Thanks for your reply Tom, yes that's correct I had not published the file again because the data was showing as incorrect in the actual PBIX file and I didn't want to publish invalid data. The fault seems to have been from within the PBIX file somehow.