Forum Discussion
Table Not Totalling Correctly
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.
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,
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
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
JerzyHappy to help, and glad you were able to sort it out!