CCPRO 2016 Power Presentation

First Power Query and Power Pivot
Next Power BI – Power Query is the data tool and Power Pivot is the modeling tool within
Power BI
3

While maintaining Excel-like look and features
Can just use point-and-click and can get very advanced with powerful functions
5

Not available prior to Excel 2010
Downloadable add-in for Excel 2010/2013
Comes preinstalled with 2016
6

Can import standard files as well as unformatted text files and folders (See Excelerator BI
blog post “Find Duplicate Files on Your PC with Power BI”
[http://exceleratorbi.com.au/find-duplicate-files-pc-power-bi/]) .
7

In particular Oracle, SQL Server, and SSAS.
8

Can access other DBs via ODBC, but also SharePoint and Exchange.
Here is where Blank Query is so you can build your own query.
And there are others – From Web!
9

Can use From Folder to load multiple files within one folder.
10

1. See the list of files per term.
2. Can click Load to just load this, but typically want to click Edit to do more
transformations.
12

Within Power Query, we can “Expand” the Binary content which are just the individual files.
This is powerful!
13

Excel is often “helpful” like this. Sometimes I keep the Changed Type step and just fix the
fields it did incorrectly or prematurely.
14

Just like that, we now have loaded many files into Power Query in just a few steps.
15

When loading multiple CSVs, it loads the headers for each file as the file is loaded. The first
one is promoted to the headers, but all the rest need to be removed.
16

Every step is recorded and can be changed or moved.
17

This is what was done. Now I remember!
18

Just for simplicity sake and for example.
19

Here are the fall terms (if you noticed earlier, Summer was first).
20

When you close a Query, you can choose Close & Load and accept the default to load the
data into a sheet and not into the data model.
Here we will specify how we want to load it. This is my preference every time.
21

Table will load the data into an Excel sheet (which is specified in the middle section).
Add this to the Data Model will add it to the data model for Power Pivot to use.
22

All done. It even tells us how many rows it loaded. This only shows if you have loaded this
to a sheet or to the data model.
23

You can add a measure or aggregation in a pivot table, but this must be done for each pivot
table and have very limited formulas.
26

Any changes to the number or date format are lost if you remove the field or create a new
pivot table.
How do you count students by term and academic year in the same pivot table?
27

All of these are true of Power Pivot pivot tables with the enhancement of the formulas.
29

These are all fixed in Power Pivot.
30

Not available prior to Excel 2010
Downloadable add-in for Excel 2010
Comes preinstalled with 2013 and 2016
31

Features – Excel-like
Ribbon
Formula bar
Data area - Column names along top with filters
Measure area
Tab at bottom is table/alias name
Character data is left justified, numeric is right justified
NOTE: Filtering here, unlike in Power Query, does not change the filtering in Excel. It is only
available to help review the data while in Power Pivot
From Data imports from Microsoft database sources (USE POWER QUERY!)
32

Hey, this has functions like COUNTA. So does Excel!
33

But Power Pivot has more powerful functions like DISTINCTCOUNT!
34

Just like with Excel Tables, you can add additional columns. Notice the formula is just like
normal Excel.
35

We can create relationships between tables and between fields (hierarchy).
36

Then we just insert a pivot table based on this data model.
37

Looks just like a pivot table…almost.
38

Notice:
1. ACTIVE|ALL.
2. Can add the hierarchy to the table
3. Unique students…wonder what that looks like?
39

Oooooo!
I fixed the format of the numbers to include a comma separator and it shows up in the
pivot table (and it will show up in all pivot tables created with this measure!).
An notice the hierarchy…
40

We can count students by term and academic year!
41

I can add a terms table and create a relationship between both tables. Here I added Terms
from the Terms table (see the Fall/Spring under the Academic Year.
42

You mean I don’t have to redo all those transformations next year? NOPE!
43

1. Regular Excel functions
2. Probably one of the most useful functions for educators.
3. CALCULATE:
Supercharged SUMIFS
Allows filtering (IFs) on any aggregate function (imagine “MAXIFS”,
“MEDIANIFS”, etc.)
4. ALL/ALLEXCEPT can be used to remove filtering to create powerful measures such as
percentages
44

Check these out!
Also, check out:
Stetson University
http://www.stetson.edu/administration/institutional-research/data-reports.php
University of Texas at San Antonio
http://www.utsa.edu/ir/content/oir_interactive-students.html
45

CCPRO 2016 Power Presentation

More Related Content

What's hot

Viewers also liked

Similar to CCPRO 2016 Power Presentation

More from David Onder

CCPRO 2016 Power Presentation