0 Kudos

Only first 1000 rows loaded to model

Status: Needs Info
by LukeWalker Frequent Visitor on ‎07-23-2018 02:54 PM

I have no clue what is causing this and I cannot reliably reproduce it (and unfortunately cannot share my PBI file). However, sometimes Power BI will only load the first 1000 rows of a table to the model, like a Power Query preview, but the model itself actually will only contain the first 1000 rows. Can anyone reproduce? It seems to occur when there's some sort of merge function in the query, but I'm still clueless as to what is causing this. Refreshing the data model will not fix it.



Status: Needs Info
by JenM Regular Visitor
‎08-08-2018 07:41 AM - edited ‎08-08-2018 07:41 AM

I downloaded and installed 2.61.5192.541 64-bit (August 2018) yesterday - still had the problem. (I had been using the July version.)


Anyone know how to acquire the June version? Is there an archive we can go to?

by RuudPolly Visitor
on ‎08-08-2018 07:59 AM

Because I frequently ask to downgrade, our IT department always keeps the previous version in their archive.

However in this case I just add the Table.Buffer function as mentioned earlier in this thread. For me this seems to have solved my issue.

by JenM Regular Visitor
on ‎08-08-2018 08:01 AM

Found an archive here.


The June release (2.59.5135.781 64-bit (June 2018)) works for me - but this obviously isn't a long-term solution... I hope this gets fixed soon!

by musicbydannyd Member
on ‎08-09-2018 07:58 AM

FYI, after installing August desktop app AND August on-premises gateway, no more issues for me.

by chaz2jerry Regular Visitor
‎08-30-2018 06:38 PM - edited ‎09-04-2018 08:18 PM

I have same issue with the PBI Service data refresh (using on-prem gateway).  When I do the refresh on Desktop, all the data is pulled in.  But when I refresh using the on-prem gateway via the PBI Service, only 1000 rows are pulled in for a Query (which contains merge operation as described by the MS issue note).  The on-prem gateway may be using an older version (not Aug release), so I will try upgrading the gateway to see if it works.  My Desktop is latest Aug version.


EDIT: I have upgraded both Desktop and Gateway in my gateway machine (which does the auto-refresh operation), and the problem has eventually gone away after doing a couple refreshes.

by JRHans09 Regular Visitor
on ‎11-19-2018 03:04 PM

Note, I am using PBI Oct 2018 version.


I am fairly new to PBI and think I have a similar or the same issue. I am trying to apply a filter through editing the query and removing records that are imported and causing problems in the dashboard. I know that I can remove the records inside of PBI Desktop by applying a Report level filter, but I would rather remove the records altogether, if possible.


The issue seems to be related to the fact that the records resides beyond the 1000 row limit in the PBI Preview. Any ideas if the 1000 row preview is causing the problem, or any other reason why the M code does not affect the data that is imported?


Here is my code for removing the records, which still appear in the dashboard (unless I apply the Report Level Filter).


= Table.SelectRows(#"Filtered RowsVNHInvoices", 
each [#"cih-invoice"] <> "VNS501249"
or [#"cih-invoice"] <> "VNS501253" or [#"cih-invoice"] <> "VNS501254"
or [#"cih-invoice"] <> "VNS501256" or [#"cih-invoice"] <> "VNS501257"
or [#"cih-invoice"] <> "VNS501258" or [#"cih-invoice"] <> "VNS501259"
or [#"cih-invoice"] <> "VNS501260" or [#"cih-invoice"] <> "VNS501263"
or [#"cih-invoice"] <> "VNS501264" or [#"cih-invoice"] <> "VSG500541")

Thanks for the help.

by LukeWalker Frequent Visitor
on ‎11-19-2018 03:08 PM

@jr I'd have to see some data to know, but this bug was fixed in the October 2018 release. 

Idea Statuses