Information on this page is meant to support our class attendees (Live In-Person, Live Online, see Choosing your training option) and has two sections:
1) Course Prep YouTube Video Playlist: Covers some basic material so we can get to the fun stuff quicker in the class
2) User Interface Cheat Sheet for Excel 2010/2013/2016/Power BI Desktop: The user interface may vary but the concepts remain the same. This helps you navigate the UI across various versions.
Course Prep Video Playlist (more videos to be added soon)
Key Differences between Excel 2010/2013/2016/Power BI Desktop User Interface
(click to jump to that section)
Power BI Desktop User Interface
We would roughly cover the following UI element for each:
Launch Power Pivot
Create New Measure (Calculated Field)
Edit Measure (Calculated Field)
Slicers
Insert a Pivot Table connected to Power Pivot Model
Add Excel Table to Power Pivot
Power Pivot Menu Tabs (Existing Connections)
Excel 2010 User Interface
Launch Power Pivot: PowerPivot > PowerPivot Window
Create New Measure:
(Must be inside a Pivot Table)
PowerPivot > New Measure
PowerPivot Field List > Right Click > Add New Measure
Edit Measure: PowerPivot Field List > Right Click > Edit Formula…
Slicers: PowerPivot Field List has a dedicated are for slicers, where you can just drag and drop the fields. The slicers also auto-arrange.
Slicers auto-arrange in Vertical and Horizontal areas
Insert a Pivot Table connected to Power Pivot Model: (From Excel) PowerPivot > PivotTable
Add Excel Table to Power Pivot: PowerPivot > Create Linked Table
Power Pivot Menu Tabs: This varies slightly across versions, especially where “Existing Connections” is found.
Design > Existing Connections
—————————————————————————————-
Excel 2013 User Interface
Launch Power Pivot: PowerPivot > Manage
Create New Measure (Calculated Field): PowerPivot > Calculated Fields > New Calculated Field
Edit Measure (Calculated Field):
PowerPivot > Calculated Fields > Manage Calculated Fields
Select Measure > Click Edit…
Slicers:
Option1 Insert Single Slicer) Right Click in Field List > Add as Slicer
Option2 Insert Multiple Slicers)
PivotTable Tools > Analyze > Insert Slicer
Select Fields > Click OK
Insert a Pivot Table connected to Power Pivot Model:
1. (From Excel) PowerPivot > Manage (to launch Power Pivot)
2. (Within PowerPivot Window) Home tab > PivotTable
Add Excel Table to Power Pivot: PowerPivot > Add to Data Model
Power Pivot Menu Tabs: This varies slightly across versions, especially where “Existing Connections” is found.Home Tab:
Home > Existing Connections
—————————————————————————————-
Excel 2016 User Interface
Launch Power Pivot: PowerPivot > Manage
or Data > Manage Data Model
Create New Measure: PowerPivot > Measures > New Measure…
or Pivot Table Field List > Right Click a Table > Add Measure…
Edit Measure:
Pivot Table Field List > Right Click a Measure > Edit Measure…
or Power Pivot > Measures > Manage Measures…
Select Measure > Click Edit…
Slicers:
Option1 Insert Single Slicer) Right Click in Field List > Add as Slicer
Option2 Insert Multiple Slicer)
PivotTable Tools > Analyze > Insert Slicer
Select Fields > Click OK
Insert a Pivot Table connected to Power Pivot Model:
1. (From Excel) PowerPivot > Manage (to launch Power Pivot)
2. (Within PowerPivot Window) Home tab > PivotTable
or
1. (From Excel) Insert > PivotTable
2. Click OK (if Power Pivot Data Model exists, that is selected by default)
Add Excel Table to Power Pivot: Power Pivot > Add to Data Model
Power Pivot Menu Tabs: This varies slightly across versions, especially where “Existing Connections” is found.Home Tab:
Home > Existing Connections
—————————————————————————————-
Power BI Desktop User Interface
Launch Power Pivot: You would not really see the name “PowerPivot” anywhere, but the PowerPivot data model absolutely exists. You can see it in “Data” view or “Relationship” view.
Create New Measure: Many ways to create a new measure
a) Home Tab > New Measure
b) Modeling Tab (when active) > New Measure
c) Right Click any Table in Field List > New Measure
This would land you in the “Formula Bar” where you can write out the measure.
Edit Measure: Click on Measure in Field List > Edit in Formula Bar
Slicers:
1. Click on “Report” View
2. Click to add any field to canvas
3. Select “Slicer” as Visualization
Insert a Pivot Table connected to Power Pivot Model: Well, can’t create an Excel pivot table, but can create a simple table element
1. Click on “Report” View
2. Click to fields to canvas
3. Select “Table” as Visualization (this should be the default when adding new elements)
Add Excel Table to Power Pivot: You just bring in any data from Excel the same way…
Get Data > Excel
Edit Queries: There is no “Existing Connections” concept. You just click “Edit Queries” to edit your existing data queries (which is really Power Query).
—————————————————————————————-
Power On!
-Avi Singh