1

2

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

3

4

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

5

Not available prior to Excel 2010Downloadable add-in for Excel 2010/2013Comes 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

11

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

24

25

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

28

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 2010Downloadable add-in for Excel 2010Comes preinstalled with 2013 and 2016

31

Features – Excel-likeRibbonFormula barData area - Column names along top with filtersMeasure areaTab at bottom is table/alias nameCharacter 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 table3. 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 functions2. Probably one of the most useful functions for educators.3. CALCULATE:

Supercharged SUMIFSAllows 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 Universityhttp://www.stetson.edu/administration/institutional-research/data-reports.php

University of Texas at San Antoniohttp://www.utsa.edu/ir/content/oir_interactive-students.html

45

46

47