I was able to import data into the Excel Powerpivot Data Model (did I describe the correctly?)
Now I need to run a query against that data to provide a subset of columns and rows.
How do I do that?
For example:
Data Model: ColA, ColB, ColC, ColD, ColE, ColF
SubQuery Result set would contain ColA, ColF.
What I need to do is generate a report by the department of people who have not completed their training.
The Department codes look something like this. It is hierarchical.
DIV1 - Head
DIV12 - Department
DIV122
DIV124
DIV125
DIV126
DIV127
DIV128
DIV18 - Department
DIV184
DIV186
DIV187
DIV2 - Department
DIV28
DIV2G
DIV2H
DIV2J
DIV2K
DIV2M
DIV2N
DIV2P
DIV2Q
DIV2R
DIV2S
DIV3 - Department
DIV33
DIV36
DIV3A
DIV3E
DIV3G
DIV3H
DIV3J
DIV3K
DIV3L
DIV4 - Department
DIV45
DIV5 - Department
DIV52
DIV53
DIV54
DIV55
DIV56
Hi Troy,
From Power Pivot window, you can create a Pivot Table report (this will be a power pivot table, not a regular excel pivot table).
In this pivot table, you can add only what columns you want to see. If you need calculations based on that table data, you can add calculated fields (measures), using DAX formula language.
Catalin
Catalin,
I am not following. Can you show me what you are describing?
The Pivot Table that I try to create wants to create a summarized table. I want a subset of the rows and columns from the data model.
I need to filter out some columns and rows from the original raw result set while keeping the original result set.
I guess a better way to describe my goal is that I want to create a view of the underlying datamodel.
Kind of like in a database (like MS SQL) there is a table that could be 100 column wide and 1 million records deep. So in MS SQL, I would create a view that would only select 10 column wide and maybe 1000 rows deep (subset of the original record set).
Thanks,
Hi Troy,
I think this post explains what Catalin was suggesting:
https://www.myonlinetraininghub.com/excel-pivot-tables-to-extract-data
Mynda
Hi Troy,
If you can upload a sample file with data, I will show you how you can do what you want.
The view you are describing is in fact a filtered database based on criterias. You can extract a sample from the source data even from Power Query, by selecting the columns you want to keep. If you want to keep a specific number of rows, you will use "Keep Top Rows" and specify a number, or, you will apply a filter on the relevant column to display only data that meets that criteria. Of course, you can apply filters on more than 1 column, depending on what you want to see. It's exactly the same thing you're doing in SQL (SQL is a query language, Power Query is a tool that uses another query language).
What I want to say is that you don't have to strip the database to keep only a specific set. By using a pivot table, you will be able to apply the same criteria you applied in SQL with slicers.
The best course order for a better understanding should be:
1.Power Query
2. Pivot Tables
3. Power Pivot
4. Dashboards
Power pivot and dashboards without pivot tables knowledge is not recommended (especially power pivot, if you create a power pivot data model, the only output is a pivot table report...)
Cheers,
Catalin
