Forum Discussion
Counting Invoices when there are duplicates
I have a data set that has a listing of multiple invoices with the invoice number repeated because the data also had the product that was sold. I need to generate a report, for each month of the yea...
Do you mind sharing your PQ solution? I'm curious why you believe it's the way to go.
I agree with Lorenzo - table the source and add to data model. Base the pivot off the data model and use distinct count.
All,
I'm going to start using Power Query due to the amount of data I am using.
First, you are only seeing a portion of the data. Second, you are only seeing one of the many reports I am using from the data.
Lastly, with one year of data, my file is 20MB.
I did find a solution on Mr. Excel Message Board that I currently am using. While it is nice, it did seem to add a tremendous amount of overhead due to the calculations. In fact, I had to turn calculations off.
I'm going to start using Power Query due to the amount of data I am using.
First, you are only seeing a portion of the data. Second, you are only seeing one of the many reports I am using from the data.
Lastly, with one year of data, my file is 20MB.
I did find a solution on Mr. Excel Message Board that I currently am using. While it is nice, it did seem to add a tremendous amount of overhead due to the calculations. In fact, I had to turn calculations off.
Everything is relative 🙂 One could consider 20 Mb as a large file, someone else as a regular file
My point was only to say that even if you have a large amount of data, Power Pivot could be an option
- I'm uncertain about your comment.
A 20 MB file isn't a large file in your estimation? Fair enough. Just be aware that PowerPivot is designed to deal efficiently with large amount of data
