cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sahaya Frequent Visitor
Frequent Visitor

Non-negative number required error while appending tables

Hello,

 

I'm trying to append 2 large datasets (22M & 30M) of same table format in Power Bi using Append Queries option. But I'm recceiving the below error. Not sure what this error means. Please assist.

append error.PNG

17 REPLIES 17
Community Support Team
Community Support Team

Re: Non-negative number required error while appending tables

Hi @Sahaya,

 

Could you please show the Details message?

 

In addition, Please check if there is any negative number which is not expected in your value.

 

If you still need help, please share the dummy table structure of your tables, so that I could have test in my environment.

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
marouf Frequent Visitor
Frequent Visitor

Re: Non-negative number required error while appending tables

I am getting the same error.

 

I am running the exact same structure datasets (created duplicate and changed the date range in one of the datasets). When I refresh each of the datasets seperately, it refreshes with no issues, but when I am appending it, thats when I am coming across this issue. This is the first time I came across this. Does this have anything to do with the latest Power BI Desktop update?

 

Unexpected error: Non-negative number required.
Parameter name: value
Details:
Microsoft.Mashup.Evaluator.Interface.ErrorException: Non-negative number required.
Parameter name: value ---> System.ArgumentOutOfRangeException: Non-negative number required.
Parameter name: value ---> System.ArgumentOutOfRangeException: Non-negative number required.
Parameter name: value
at System.IO.FileStream.set_Position(Int64 value)
at Microsoft.Mashup.EngineHost.Services.StreamSharer.MoveTo(SubStream subStream, Int64 position)
at Microsoft.Mashup.EngineHost.Services.StreamSharer.ReadAt(SubStream subStream, Int64 position, Byte[] buffer, Int32 offset, Int32 count)
at Microsoft.Mashup.EngineHost.Services.StreamSharer.SubStream.Read(Byte[] buffer, Int32 offset, Int32 count)
at System.IO.BufferedStream.Read(Byte[] array, Int32 offset, Int32 count)
at Microsoft.Mashup.EngineHost.Services.TransformingPagedStream.ReadPage(Int32 page, Byte[] buffer)
at Microsoft.Mashup.EngineHost.Services.PagedStream.SeekToPage(Int32 page)
at Microsoft.Mashup.EngineHost.Services.PagedStream.Write(Byte[] array, Int32 offset, Int32 count)
at Microsoft.Mashup.EngineHost.Services.WriteBehindPersistentCache.AsyncCommitStorage(String key, TemporaryStorage storage)
at Microsoft.Mashup.EngineHost.Services.WriteBehindPersistentCache.AsyncCommitStorage(Object obj)
at Microsoft.Mashup.Evaluator.EvaluatorThreadPool.EvaluatorThread(Object state)
at Microsoft.Mashup.Evaluator.SafeThread2.<>c__DisplayClass15.<CreateAction>b__14(Object o)
--- End of inner exception stack trace ---
at Microsoft.Mashup.Evaluator.EvaluationHost.<>c__DisplayClass7.<TryReportException>b__6()
at Microsoft.Mashup.Common.SafeExceptions.IgnoreSafeExceptions(IEngineHost host, IHostTrace trace, Action action)
at Microsoft.Mashup.Evaluator.EvaluationHost.TryReportException(IHostTrace trace, IEngineHost engineHost, IMessageChannel channel, Exception exception)
at Microsoft.Mashup.Evaluator.EvaluationHost.TryHandleException(Exception exception)
at Microsoft.Mashup.Evaluator.SafeThread2.HandleException(Exception e)
at Microsoft.Mashup.Evaluator.SafeThread2.<>c__DisplayClass15.<CreateAction>b__14(Object o)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart(Object obj)
--- End of inner exception stack trace ---
at Microsoft.Mashup.Evaluator.EvaluationHost.OnException(IEngineHost engineHost, IMessageChannel channel, ExceptionMessage message)
at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.ChannelMessenger.OnMessageWithUnknownChannel(IMessageChannel baseChannel, MessageWithUnknownChannel messageWithUnknownChannel)
at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.Interface.IMessageChannelExtensions.WaitFor[T](IMessageChannel channel)
at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.PreviewValueSource.WaitFor(Func`1 condition, Boolean disposing)
at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.PreviewValueSource.get_SmallValue()
at Microsoft.Mashup.Evaluator.Interface.TracingPreviewValueSource.get_SmallValue()
at Microsoft.Mashup.Host.Document.Analysis.PackageDocumentAnalysisInfo.PackagePartitionAnalysisInfo.SetPreviewValue(EvaluationResult2`1 result, Func`1 getStaleSince, Func`1 getSampled)

marouf Frequent Visitor
Frequent Visitor

Re: Non-negative number required error while appending tables

Would anybody know how to resolve this issue?

CHohnbaum Frequent Visitor
Frequent Visitor

Re: Non-negative number required error while appending tables

I am experiencing the same error when refreshing my dataset, however this dataset has been refreshing without issue for over a year. I just updated to the August PowerBI yesterday and this is the first time I've tried refreshing it with the new update (I was using the July 2018 version previously).

 

I'm also running into a further issue where I can't select the window after the failure and have to kill the task in task manager. It's acting as though there is an extra window open when the failure occurs while I don't have the query editor open, but I can't actually see any extra windows and tabbing between available windows does not show any options available ot me.

 

If I trigger the failure while query editor is open I get the large failure block that looks very similar if not the same to the one pasted in the comments above.

arthurgasparini Frequent Visitor
Frequent Visitor

Re: Non-negative number required error while appending tables

1. One of the ways we found around this bug (it's not an issue) was by changing the order of the Appended Queries, for eg:

 

If you're appending Table 2, 3 and 4 to Table 1 (one being the main) - change the order to append Table 1, 3 and 4 to Table 2. 

 

That solved our issue in one of our reports - we are appending more than 100m rows between 4 different datasets. This is not consistent - sometimes it still gives the error but eventually, we were able to make it through. 

 

2. If the error happens around the same row count - for eg: the error always happens around 19m rows - and you know that on 19m rows your dataset is changing from Table 1 to Table 2, then there might be something wrong a part of your Table 2. This is how we bypassed this bug on another case. In theory, nothing should be wrong with the dataset - we are 100% sure of that - but somehow, somewhere, Power BI is interpreting the data the wrong way. Once we deleted the first millions on Table 2 the query ran as expected.

 

This shouldn't be happening and it appears to be happening a lot - only on large datasets apparently. 

 

3. On another thread this came up:

 

"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 merged operations) from the mashup engine. This resulted in a successful report refresh."

 

We tried this but PowerBI couldn't handle the 200m rows dataset.

 

Hope this helps.

 

baboodiot Frequent Visitor
Frequent Visitor

Re: Non-negative number required error while appending tables

I'm having this issue, too, and *in Power Query within Excel*.  The query worked just fine on as recently as July 24 - now it won't ever refresh correctly, and I get the same error messages shown in other posts above. 

 

Interestingly, it seems to have refreshed the data in the query editor (I can tell because a new season's data is now appearing in the preview), so it seems to be crashing as it tries to load the results - I've never seen behavior like this before!  I've rebooted, pulled down the latest Power BI update (August ?14?), looked for Excel updates (I have Excel 2016 on Windows 7 and found none pending), rebooted again, etc. etc... 

 

This query does, as others have described, involve merged queries.  I have another merged query to the same large table in this same workbook (again, this is primarily an Excel issue for me - I'm posting here because it's identical error language and that seems relevant!), so there are two merged queries pulling different subsets of the same table - the first merge does still run, with about 3m records post-join (in terms of how many rows it says it's loading as it refreshes), with 1,300 rows in the final output.  The merged query that won't run is about 9m records post-join, with about 12,000 rows in the final output to the workbook back when it would successfully run a few weeks ago!

 

 

sbtbrenden Occasional Visitor
Occasional Visitor

Re: Non-negative number required error while appending tables

I too am running into this with only 2 million rows total from two seperate databases.

 

Unfortunately, swapping the order of the imports did not help me. 

 

 

boylec Frequent Visitor
Frequent Visitor

Re: Non-negative number required error while appending tables

Looking at the stack trace it looks like this is an issue with the mashup engine. It looks like it uses a bunch of file streams under the cover to merge queries or something of that nature. In other words, its storing information in files on the hard disk while generating the preview and reading from those files while mashing everything up.

 

This exception appears to be occurring because the value being passed to "set_Position(Int64 value)" - referring to a spot in a filestream, is a negative number.

 

When an integer increases beyond its maximum value, it wraps back to a negative number. For example if the max value of an int was 10, and you tried to increment it up to 11, it would become -10 instead of 11.

 

The actual maximum value of a 64-bit integer (int64) is high.

 

 

 

 

 

 

18,446,744,073,709,551,615 to be exact.

 

This many bits would be, in gigabytes:

18,446,744,073,709,551,615/8/1000/1000/1000

=2,305,843,009.21 GB

 

Guaranteed none of the filestreams the mashup engine is using are large enough to cause that to happen.

 

However, if the value variable is being derived from any other 32-bit integers somewhere along the code path up to that point: The max value of an int32 is 2,147,483,647. In gigabytes thats:

2,147,483,647/8/1000/1000/1000.

= roughly .268 GB or 268 MB.

 

If any of the file stream position calculations are being stored in a 32 bit integer in the PBI code, and the size of that filestream is exceeding 268 MB, there is a good chance it is causing that 'value' integer shown in the stack trace to wrap around to -2,147,483,647. Then by the time it hits that line of code an exception gets thrown, because code-based file reader can't find a negative position in a file.

 

I would experiment with getting rid of columns that you don't need in any of the base data sources being used by the mash up engine in order to try and shrink the size of any file streams. Perhaps it won't reach the 2,147,483,647 bits size and trigger this bug....

 

I know its a bit of a shot in the dark, but that is my best educated guess of what is occurring.

 

Anonymous
Not applicable

Re: Non-negative number required error while appending tables

I am having this same problem with my report. All I'm trying to do is load data into my report from SQL Server using a query. The query runs just fine in Management Studio, but Power BI gives me the error message.

 

Can we please get a fix for the mashup engine?

 

Here is my error message:

 

Unexpected error: Non-negative number required.
    Parameter name: value
    Details:
        Microsoft.Mashup.Evaluator.Interface.ErrorException: Non-negative number required.
    Parameter name: value ---> System.ArgumentOutOfRangeException: Non-negative number required.
    Parameter name: value ---> System.ArgumentOutOfRangeException: Non-negative number required.
    Parameter name: value
       at System.IO.FileStream.set_Position(Int64 value)
       at Microsoft.Mashup.EngineHost.Services.StreamSharer.MoveTo(SubStream subStream, Int64 position)
       at Microsoft.Mashup.EngineHost.Services.StreamSharer.ReadAt(SubStream subStream, Int64 position, Byte[] buffer, Int32 offset, Int32 count)
       at Microsoft.Mashup.EngineHost.Services.StreamSharer.SubStream.Read(Byte[] buffer, Int32 offset, Int32 count)
       at System.IO.BufferedStream.Read(Byte[] array, Int32 offset, Int32 count)
       at Microsoft.Mashup.EngineHost.Services.TransformingPagedStream.ReadPage(Int32 page, Byte[] buffer)
       at Microsoft.Mashup.EngineHost.Services.PagedStream.SeekToPage(Int32 page)
       at Microsoft.Mashup.EngineHost.Services.PagedStream.Write(Byte[] array, Int32 offset, Int32 count)
       at Microsoft.Mashup.EngineHost.Services.WriteBehindPersistentCache.AsyncCommitStorage(String key, TemporaryStorage storage)
       at Microsoft.Mashup.EngineHost.Services.WriteBehindPersistentCache.AsyncCommitStorage(Object obj)
       at Microsoft.Mashup.Evaluator.EvaluatorThreadPool.EvaluatorThread(Object state)
       at Microsoft.Mashup.Evaluator.SafeThread2.<>c__DisplayClass15.<CreateAction>b__14(Object o)
       --- End of inner exception stack trace ---
       at Microsoft.Mashup.Evaluator.EvaluationHost.<>c__DisplayClass7.<TryReportException>b__6()
       at Microsoft.Mashup.Common.SafeExceptions.IgnoreSafeExceptions(IEngineHost host, IHostTrace trace, Action action)
       at Microsoft.Mashup.Evaluator.EvaluationHost.TryReportException(IHostTrace trace, IEngineHost engineHost, IMessageChannel channel, Exception exception)
       at Microsoft.Mashup.Evaluator.EvaluationHost.TryHandleException(Exception exception)
       at Microsoft.Mashup.Evaluator.SafeThread2.HandleException(Exception e)
       at Microsoft.Mashup.Evaluator.SafeThread2.<>c__DisplayClass15.<CreateAction>b__14(Object o)
       at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart(Object obj)
       --- End of inner exception stack trace ---
       at Microsoft.Mashup.Evaluator.EvaluationHost.OnException(IEngineHost engineHost, IMessageChannel channel, ExceptionMessage message)
       at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)
       at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
       at Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)
       at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
       at Microsoft.Mashup.Evaluator.ChannelMessenger.OnMessageWithUnknownChannel(IMessageChannel baseChannel, MessageWithUnknownChannel messageWithUnknownChannel)
       at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)
       at Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)
       at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
       at Microsoft.Mashup.Evaluator.Interface.IMessageChannelExtensions.WaitFor[T](IMessageChannel channel)
       at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.PreviewValueSource.WaitFor(Func`1 condition, Boolean disposing)
       at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.PreviewValueSource.get_SmallValue()
       at Microsoft.Mashup.Evaluator.Interface.TracingPreviewValueSource.get_SmallValue()
       at Microsoft.Mashup.Host.Document.Analysis.PackageDocumentAnalysisInfo.PackagePartitionAnalysisInfo.SetPreviewValue(EvaluationResult2`1 result, Func`1 getStaleSince, Func`1 getSampled)

 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 187 members 2,153 guests
Please welcome our newest community members: