Hi everyone, this is my first post and I could really use some help please.
Our business is set up as 2 separate companies. Our ERP system has 2 sets of tables, 1 for each company. Some products are manufactured in Company 1, sold to Company 2 for a profit, and then sold on by Company 2 to its customers for additional profit. I'm trying to bring data from both companies together to calculate consolidated gross margin by product, customer, month etc.
I'm at the final step where I'm trying to merge 2 queries to bring all the required data together to perform the consolidated gross margin calculations. There appear to be no errors in either of the queries to be merged. The merge (a Left Outer join) works fine but when I try to expand the merged table I get the following error message:
DataSource.Error: Microsoft SQL: Divide by zero error encountered.
Details:
DataSourceKind=SQL
DataSourcePath=bcc-appsrv1;efacdb
Message=Divide by zero error encountered.
ErrorCode=-2146232060
Number=8134
Class=16
State=1
I understand this error is caused when a denominator is 0, but I can't see how this could happen when there is no division in the query, unless I'm missing something.
I added an additional date filter to try to identify the cause of the error. When I filter the data from 15/04/23 (UK date format so 15th April 23) to today, the table expands with the correct data as expected, but returns the error when I filter from 14/04/23 to today. Strangely, the error is not caused by data from 14/04/23. I can successfully expand data from 01/04/23 to 03/11/23, but not from 01/04/23 to 04/11/23.
The problem is not the second query that I'm merging in as I get the same results described for the various date ranges above when I tried merging a simple date query.
I've attached my code for the query that is giving the error in case this helps. (It's probably not the most efficient as I'm relatively new to Power Query and am just a self-taught non-expert!)
Thanks in advance for any help or suggestions.
Tony
I trust you have done what you can but it's difficult to diagnose the problem without seeing the data. How do 'dbo_invoiceitems' and 'invoices' look like before you do any transformations to them. So, the raw data how it's being pulled from SQL. I don't work with SQL so don't really know what goes on at that end, but seeing what comes in to PQ could help.
Hi Riny,
Thank you for taking the time to reply. Since my OP, I've continued further analysis to identify the root cause, by filtering the data by dates to return data with less than 1000 rows.
This morning I have found errors returning earlier in the code so I now know that the merge is not the problem, it was just identifying that a problem existed. I don't fully understand the PQ functionality, but I now assume it only identifies a problem if it appears in the first 1000 rows.
I haven't figured out why I'm getting the errors yet, but if I need help am I better to start a new topic that would better describe the problem or just continue in this thread?
For now, I don't want to waste other peoples' time trying to help with this, as the problem is not as I described originally. I don't know how best to do this but hopefully this message will help 🙂
Thanks again,
Tony
Understood! Thanks for the feedback. In PQ there are indeed many instances where you see that, for example, a preview is based on the first 1000 records, but you can go into a table at any point and just keep scrolling down. It doesn't stop at 1000 rows.
For example (on a Mac now so it looks a bit different on a PC). You'll see column statistics based on 1000 rows but I can scroll down beyond row row 1000.
Perhaps you see some data with 'NaN' (not a number) or the 'infinite symbol' in the raw data before you do the merge. If scrolling is too much to ask, then temporarily load the tables to an Excel sheet before you merge them so that you can more easily sort and filter them to spot errors. Just some ideas.
