Table Not Totalling Correctly

[Anonymous]

Hi,

I've seen several other posts with a similar issue, however all posts I found used measures which is not the case with my issue. 

I have found a discrepency between a distinct count and a count of rows. The column used is a reference number column so I expected all rows to contain unique values. The discrepency is minor (2), but it was still unexpected and alarming.

I managed to drill the discrepency to the months of September and December but any further drilling (e.g. for the year) result in the discrepency diseappearing in the table but remaining in the table totals. 

I am bamboozled to say the least. 

As stated before I haven't used any measures just a simple drag and drop to the table and I have no active filters with the exception of the month (filtering in September and December).

Of course I can (and will) just use the final total that is correct but has anyone encountered an issue like this before and know what causes it? 

Thanks in advance for the reply. 

[Anonymous]

Hi @dk_dk ,

Thank you for reminding me about the viewing option in the Power Query Editor. After investigating it deeper I seem to have found more questions then answers.

For context, the table from which I counted rows is made from 3 queries that were appended as a new table (each query is for each fiscal year).

Now, there were indeed duplicates in the appended table however, it turns out there were 3 duplicates and not 2 as it seemed from the initial discrepancy.

3 found duplicates (in appended table):

I looked into the original data source (SharePoint Lists) to resolve this but found no duplicates. Thinking the duplicates must have somehow appeared in the Power Query Editor, I looked at the original tables (before appending) and again found no duplicates in original queries. 

Query 1 (final step before appending):

Query 2 (final step before appending):

Query 3 (final step before appending):

Appended Table (Source step):

The 3 duplicates bear no similarities and all appear to be dated roughly a year after their original counterparts. 

Please let me know if you need any other information or have any other suggestions.

Many thanks

[dk_dk]

Hi @Anonymous ,

In your Appended Table query, are the 3 duplicates valid, expected use cases? If yes, you should not do anything further, and depending on the use case, either use normal count or distinct count in your report. Now you understand why distinct count does not sum up correctly, and you can provide that explanation to any end users should they question the reason.

If the 3 duplicate occurences are not intended, you should remove them (either with the Remove Duplicates step, or by manually filtering out the undesired Reference value from the original queries (such that after appending they would only occur once). If this reference is coming from a source system, where according to business rules duplication should not occur, you should investigate the database/process that contains or creates these references, to avoid duplications happening in the future.

There is not so much else to do in Power BI itself, as the normal count and distinct count measures seem to work as intended, based on the data you have in your query.

Let me know if you have any further questions!

Best regards,

Daniel

[Anonymous]

Hi @dk_dk 

I agree that Power BI is counting as expected, the one thing I don't understand is where the duplicates are coming from and what's causing them to appear. 

The data is sourced from SharePoint List, where after checking, there are no duplicates. There are also no duplicates when importing the individual tables from SharePoint Lists into Power Query. The duplicates only appear after the tables are appended which is all done in Power Query.

Furthermore, something which I didn't make clear earlier is that the only similarity the duplicate rows share is the reference number, with (almost) all other columns having different values (e.g. category, dates). Considering the duplicates are unexpected, appear random and have no clear explanation I am now worried about the validity of the entire appended table. Also there I have no way to guarantee that removing duplicate numbers will remove the rogue entries rather than the original and real ones.

Do you know any potential explanation how the duplicates appear after appending, when these did not exist prior?

Many thanks,

[Anonymous]

@dk_dk Thanks for your contribution on this thread.

Hi @Anonymous ,

For further troubleshooting, can I trouble you for the following information?
1. Could you please some example data(exclude sensitive data) for the table that is involved in the append operation with Text format?
2. The screenshot of the data in the table after append.
3. The formula of metrics which get the count

Best Regards

[Anonymous]

Hi @dk_dk and @Anonymous 

Last week I had thought its impossible for duplicate ref numbers as each tables' ref numbers are followed by distinct letters. However looking at this issue with a fresh pair of eyes I came to realisation that the ref number are manually inputted and lo-and behold there were two incorrect letters in one of the tables.

So thanks again Daniel, couldn't have figured it out without you.

Kind regards
Jerzy

[dk_dk]

Happy to help, and glad you were able to sort it out!

[dk_dk · Accepted solution]

Hi @Anonymous ,

If you can inspect your data in the query editor, under the view tab you can turn on column profile, column distribution and column quality, and see if all the values in the reference number column are really unique.

I would start there. Normally what would cause this discrepancy is that you have the same reference number present in two different months/years. In the normal count they will be counted twice, in the distinct count once. 

You can see that the sum of distinct count does not add up to the total in the second table. That is because it is distinct counting each month/year, as well as the total.



For example with this dummy data:

Month	reference ID
January	a
January	b
February	a
February	c
March	b
March	c

The counts would look like this:

Month	Distinct Count	Count
January	2	2
February	2	2
March	2	2
Total	3	6

Because there are two distinct values in january, two in february, two in march, but only 3 in total because of the duplicate values.

If you want to troubleshoot this you could create a Measure = COUNTROWS(Table), and put the reference column and the countrows measure into a table visual. There should be one or two reference columns where the countrows measure is higher than one, meaning that the column is not unique.

I hope this helps.