Forum Discussion
Show Details Pivot Table
MarcusEstrada22 Odd indeed. Filtering out an aggregated value doesn't work the same as filtering out a label. But explaining why goes beyond my level of expertise. The reason for answering you questions is that you wrote that you are dealing with hundreds of thousands of rows. I would recommend that you look into using Power Query for this task. It's relatively easy to do this type of analyses on very large data sets and produce exactly the kind of report you need. Once set-up correctly, you can use it over and over again.
Riny_van_Eekelen I haven't used Power Query before, but i was able to upload it into IDEA and perform an extraction based on 3 matching columns and 1 column i added that noted which are offsetting (debit/credit for accounting). I could then rejoin the tables and exclude matches from the original that appeared in the extraction. This ended up working but wanted to avoid adding in another program and having to tailor the data again. Does power query do something similar?
MarcusEstrada22 Power Query is integrated in Excel as from 2016, so it's not an extra program. Am not familiar with IDEA so can't judge if or where PQ is different. What you would do in PQ is connect to your data source (a structured table, named range or sheet in another workbook (or even multiple workbooks). Then, perhaps do some very basic transformations and than Group / Sum, filter out the zeroes and load back into another sheet. Once set-up, all you need to do is press "Refresh" and a summary table will be updated. And all happens within Excel. Since you didn't mention to be working on a Mac, this should work better than using pivot tables.