Power BI Desktop: Query Not Fully Loading

[IamTDR]

I am currently using the latest Power BI Desktop verison (July-2018) and noticed something odd.  I am importing from a table from SQL and then using the power query editor to clean my data.  Once the data is cleaned is shows about 400 records.  I then proceed to Close and Load data and then only 13 records are available in table.  When I refresh and flip back and forth between the query editor and the table in data view the number of records dont match. 

I noticed this the other day with another report and after closing desktop and refreshing data a few times it just seemed to fix itself.

Anyone else notice this?  I am concerned as my report that end users are using is not showing all records.  Strangely the report has been refreshing daily without issue.

Could this be a bug with the July version?

[itayrom]

I stumbled upon this issue as well. It happened to me in two reports (identical in all aspects but some source tables for the data), one with ~25m records and the other with ~48m. Both reports import their data from SQL Server.

In an effort to find out what's causing it, I activated the tracing & crash dump logging options in the diagnostics settings, and came up with the following:

1. The source for the problem seems to be an issue with Microsoft.Mashup.Container.NetFX40 and/or Microsoft.Mashup.OleDbProvider.

2. When the issue occures, it is also logged to the windows application log. Some entries mentioned "OLE DB or ODBC error: Exception from HRESULT: 0x80040E4E", suggesting the connection to the server was cancelled.

3, Initially, I encountered some entries in one of the logs of which the specified action was "Engine/IO/Db/Microsoft SQL/ReportFoldingFailure".

4. Failing to find any useful info on the web, I decided it would not hurt to check if the issue was related to query folding. I therefore modified my queries so that query folding would not occur. It resulted in the issue being resolved in the smaller report, and replaced with new error in the larger one, with the following error message:

"Non-negative number required.
Parameter name: value"

5. Inspecting the logs generated from the larger report, I found thousands of entries referring to a "FileSystemAccessHelper/TryIgnoringAccessExceptions" action. Most of the exceptions were of type System.IO.EndOfStreamException, but a few were of type System.IO.IOException, specifying some cached files could not be accesses because they're open by another process.

6. Realizing this problem is bigger than me, I decided to try and get around it by creating the final form of the data table on the SQL Server side, thus removing the heavy workload (Which was mostly merge operations) from the mashup engine. This resulted in a successful report refresh.

Basically, it seems like we got a faulty mashup engine with the July 2018 release.

[musicbydannyd]

@itayrom, great minds think alike... I too, am "doing it on the database". Still, this issue is obviously not normal. Will post here if support comes up with a resolution.

[Anonymous]

Found an interesting tidbit while trying to see if the issue was somehow caused by me.

First thing I noticed is the number rows appended is equal to 5000 which I found weird.

I didn't pay much attention to it, but to make a long story short, I am appending 5 tables and thought maybe it only loads the first 1000.

I tested it and it does only pick up the first 1000 rows of each table.

Maybe someone more advanced can shed some light on why that is, I am just a beginner when it comes to this.

Thanks

[itayrom]

It seemed to me that those 1000 rows we get from each query where this issue occurs is its preview data.

[musicbydannyd]

Nope... if you look at bottom of "Data" section (not "Report" or "Relationships") in PBI Desktop, you'll see how many rows have been loaded for each query. I've have some with millions, but the "bad" table loads exactly 1,000.

[itayrom]

I meant the 1000 rows loaded from each query and displayed in the query editor's preview.

[musicbydannyd]

Understood. Still, I have gotten more that 1,000 rows in editor preview... if I'm patient enough to scroll. 

[musicbydannyd]

Still yet to receive response from MS support, but reverting back to June 2018 version of desktop app solves issue (which renders all pbix files edited after July install incompatible).  Now it's wait & see if there are any scheduled refresh issues this evening as our enterprise gateway is July version. Ugh.

[musicbydannyd]

New version of desktop was posted yesterday, which fixes data load issue, but republishing reports to service still shows incomplete data. 

[musicbydannyd]

Sorry, forgot link to new version: https://www.microsoft.com/en-us/download/details.aspx?id=45331

[IamTDR]

Downloaded and installed the latest July 2018 Power BI desktop.  This did resolve my issue on the desktop version.  I will now wait to see what happens over night for when this report is scheduled to be refreshed.

Thanks for letting me know about the update.

[musicbydannyd]

I have latest version as 2.61.5192.541, released yesterday...

[MikeHawryluk]

My issue is exactly the same.  I built a simple diagnostic and the table that fails loads exactly 1000 rows when it fails.  I have the new August release of desktop which works fine 100%.  It's when I load to the service that it fails approximately 50-75% of the time.  On prem gateway is July release.

Datasource is Postgresql.

One other has complained in the issues section of the site here: https://community.powerbi.com/t5/Issues/AWS-hosted-Postgres-Connectivity-Issues/idi-p/483047

[musicbydannyd]

@MikeHawryluk, thanks for link to other thread... I see MS has a presence there... will post my experiences shortly.

[musicbydannyd · Accepted solution]

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

[Anonymous]

Looks like it is working ok now.

[MikeHawryluk]

Same for me...I just checked this morning and everything is A.O.K.

[Anonymous]

Yea, the individual tables shows the correct amount of rows, but once you combine them you only get 1000 of each.

[musicbydannyd]

My table-in-question is also loading exactly 1,000 rows... I notified the support contact I was working with and am awaiting a response.

[musicbydannyd]

I am experiencing this as well... I also have a query to load from SQL server and when refreshing from Desktop, only a few rows (from January 2016) are loaded. Went to query editor to troubleshoot, refreshed preview, and things seemed to go awry during a split column step. Refreshing preview while selecting this step loads random other dates. Weird.