Forum Discussion
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.
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!