cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Advocate IV
Advocate IV

Re: Power BI Desktop: Query Not Fully Loading

MS support has isolated a "NaN" error in a line chart from a measure which uses a "SUMX" formula... do you see anything like this on your end?

Highlighted
Advocate IV
Advocate IV

Re: Power BI Desktop: Query Not Fully Loading

Also, something funky going on with a merge step in my query... table 1 has 1,755,000+ rows and table 2 has 71 rows, yet merge showing table 2 matches ALL rows in table 1.

Highlighted
Resolver I
Resolver I

Re: Power BI Desktop: Query Not Fully Loading

Nope.  No SUMX at all.

Highlighted
Advocate IV
Advocate IV

Re: Power BI Desktop: Query Not Fully Loading

Any "Merge Queries" steps?

Highlighted
Resolver I
Resolver I

Re: Power BI Desktop: Query Not Fully Loading

Yes.  I did use a merge query.  However, I use them alot and my other reports have yet to have an issue

Highlighted
Resolver II
Resolver II

Re: Power BI Desktop: Query Not Fully Loading

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.

Highlighted
Helper I
Helper I

Re: Power BI Desktop: Query Not Fully Loading

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

 

Highlighted
Advocate IV
Advocate IV

Re: Power BI Desktop: Query Not Fully Loading

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.

Highlighted
Resolver II
Resolver II

Re: Power BI Desktop: Query Not Fully Loading

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

Highlighted
Advocate IV
Advocate IV

Re: Power BI Desktop: Query Not Fully Loading

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.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors
Users online (717)