I´m working with Power query excel add in and I´m having a quantity discrepancy between the detailed data and group by results.
I have two queries:
Query 1: “All Systems” that includes all PO lines. Loaded it gives me 662k lines.
Query 2: “Group by” it has the same data (all steps are exactly the same) but at the end I´m removing non necessary columns for faster load and grouping by certain criteria. When I summarize the PO Line count, the total lines is 813k
If I make a deeper analysis, the Group by is only adding lines to a certain "group by" category:
| Pivot from Group by Table | |
| Row Labels | Sum of PO Lines |
| Catalog | 8665 |
| Free Text | 298582 |
| G-Code | 284 |
| Material Master | 400019 |
| Services | 105600 |
| Grand Total | 813150 |
| Pivot from All Systems | |
| Row Labels | Count of PO_Item |
| Catalog | 8665 |
| Free Text | 298483 |
| G-Code | 283 |
| Material Master | 248991 |
| Services | 105599 |
| Grand Total | 662021 |
Any idea why the Group by behaves this way? Even if I apply a filter to the data before the grouping, it will bring the same amounts of lines as no filter ever happened. Now that I thinking of it, there is a previous filter applied that might be affecting the only category with discrepancies.
Thank you!
Hi Ileana,
I'm a bit confused because you say there are 662k lines in query 1, which I presume is not grouped, but the grouped query 2 has 813k lines. Surely it would have less lines as it's now grouped?
Are you able to share the file so we can help troubleshoot?
Mynda
