• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Distinct Count in Pivot Tables

P

PaulFogel123

Member
Hello,
I have an unusual request: I use Distinct Count in Pivot Tables as a field, like this example:

1744135869514.png

It works great, but using the Distinct Count function requires using the Data Model, which means the underlying data is stored in the file housing the Pivot Table. This is bloating my file size way too much.

I import the source data through Power Query and have explored using the Distinct Count feature from the Statistics tab. However, it produces a single value for the entire column, not a distinct count for each row, as in the example above.

Is there a solution within Power Query, or maybe the source file?

Paul
 
Please attach a file that we can review and test our theory on. Without seeing the source and the PQ to determine exactly what you need may prove onerous. Also, then mock up what you want your solution to look like. It is not necessary to attach the entire file, but perhaps only 15 -20 original source records.
 
I've attached a smaller version of the file with the source data in another tab in the same file so you'll have easy access. The data is imported into Excel and loaded into a Pivot Table on the Pivot tab.

The Distinct Count field is very useful for this use, but it requires that the Data Model be checked. This stores the underlying data, and the file becomes massive. Any ideas around this?
Paul
 

Attachments

  • test table.xlsx
    720.9 KB · Views: 3
I built the PT in Power Pivot directly without going into Power Query. Your sample file with Power Query is 721 kb. My file is a bit smaller at 689kb. Not a significant difference, but I don't think you need PQ to put the table into the Data Model. Hope this helps. If I remove the Pivot Table in my example, the file is still 689kb for the source data alone. So I did not find any bloat in the file by using power pivot directly.
 
Alan,

Thanks for working on this.

The original source data table is about 150MB, and there are others of equal size, each with its own Pivot Tables needing Distinct Count. Using the data model to get Distinct Count saves tons of data within the Excel destination file and bloats the file. Because of their size, these source tables must reside in separate files. I'm unsure how to create a Pivot Table with Power Pivot without using Power Query.

A possible solution is to create the Pivot Table with Distinct Count in the source data file and then import only that Pivot Table with Power Query. The trick here is to import it by specifying the sheet, not the table, since tables don't work within Pivots. Power Query only picks up data from the Pivot when you specify the sheet. I can then import it as a table since it's already in the layout I'd want. That wouldn't bloat the destination file at all.

What do you think of this?

Paul
 
Highlight the source table.
Select Insert
Select Pivot Table
From Table or Range
Click the box for the Data Model.
then do your Pivot Table in the Data Model.
No need to bring into Power Query
 
You must log in or register to reply here.
Back
Top