The Beginners Guide To Excel Power Pivot And Power BI
What are Excel Power Pivots and Power BI
Power Pivot, a free Excel Add-in since version 2010 and Power BI which is essentially Power Pivot’s stand-alone replacement encompassing Power Query, Pivot and View, both allow data from diverse sources to be bought together via an in-memory database for reporting purposes.
Both products can manage millions of rows of data, apply calculations and supporting KPIs.
The products provide graphically enhanced reporting options ‘visualizations’ with data presented in a Pivot style, PivotCharts in multiple layouts, for example, two charts side by side for comparison and the ever-popular with management, dashboards to deliver a highly developed Business Intelligence model.
PowerPivot was introduced with Excel 2010 and SharePoint 2010. Excel 2013 saw a new more advanced PowerPivot add-on providing additional functionality such as filtering on import, drag-and-drop relationships and defining of KPI’s. Behind the scenes Excel 2013 has no size limitations whereas Excel 2010 was limited to 2 gigabytes in memory and 4 on disk.
Power BI the most recent development in the family and is a free download sitting on the desktop rather than within Excel, but that can deploy as Excel workbooks so any reports can be shared. Power BI introduces several new and extends some existing features to build on the existing PowerPivot in particular the ability to handle a wider variety of data sources, a wider variety of charts and better mobile device support. It also uses the DAX programming language.
Power BI comes with three different tiers of licencing from free to enterprise level. For full details of the tiers see this article which explains the differences.
About Acuity Training
Want to know more about Acuity Training?
We are on of the UK’s leaders in Excel training. We provide classroom-based IT and professional development training.
Looking for SQL training courses or to learn Photoshop. We’re here to help.
Oh and we offer Excel training in London and Guildford as well. Or if you’d prefer we can come to your offices.
See this link if you are looking for information on our Power BI training courses which run in London and Guildford.
Both PowerPivot and Power BI can be downloaded free from the Microsoft website. PowerBI downloads as a desktop icon seen opposite, PowerPivot as an Excel Add-in.
To install the add-in click on File, Options and then on Add–Ins from the navigator on the left. Change the Manage drop down at the bottom of the screen to COM Add–ins and then click the Go button.
From the dialog box that then appears tick PowerPivot for Excel and OK.
Once the Excel PowerPivot add-in has been installed and enabled, there will be a new ribbon called PowerPivot. The first button on the PowerPivot ribbon opens the PowerPivot window.
This is the PowerPivot main window.
This is the opening window for Power BI
For Power BI this process is the same as for any source and starts with Home, Get Data and is then very similar to the guide for from Access replacing tables for Sheet, Range Names and Table Names.
See this article, if you would like to understand in detail how to import data from SQL server into Power BI.
External data located in a different workbook
If you are importing data from one Excel workbook to another, Excel Tables are not recognised.
If you are importing to Power BI both Range and Table names are recognised.
The safest approach here is to define a Named Range within the Excel source file first noting that range naming an Excel table also will not work as it must first be Converted to Range.
Column headings should be used within your data. Although this step is not essential, it is highly recommended.
- From the PowerPivot window, click Home , From Other Sources
- In the dialog box that appears, scroll down and select Excel File.
- Click Next
- Browse to the Excel file and tick “Use first for as column headers” (assuming your data has headers).
- Click Next
- Select the required named range.In the Source Table column, sheet names are suffixed with a “$” whereas named ranges are not.
7. Optionally use the Preview & Filter button to filter the data or to exclude columns.
8. Click Finish
9. The import confirmation will appear, click Close.
Internal data located in the current workbook
If accessing a local source of data from within the same Excel workbook, it is highly recommended that you Format As Table your data prior to importing the data into the PowerPivot data model and that your data has a header row too.
Make sure the Excel Table has a sensible name as set through Table Tools, Design, Table Name. The following instructions will assume this has been done.
As previously, all Power BI sources are obtained the same way via Get Data, see From an Access Database below
- Select a single cell within your data and from the PowerPivot ribbon, click Create Linked Table.
- The table should now be visible within the PowerPivot window. The table name will have the same name as the Excel Table and show a chain link icon.
From A Text File
When importing from a text file, make sure you are aware of the structure type, ie tab delimited, fixed width, csv etc. Text files can be imported with or without a header row, but use of a header row is recommended. Make sure there is no additional text above the data; the first row should be the header row or the first row of data. Use a text editor such as Windows Notepad should you need to delete unrequired text prior to the import.
- From the PowerPivot windows click Home , From Other Sources, From Text
- Use the Browse button to locate the text file.
- Use the Friendly connection name to assign the name of the table that PowerPivot will use.
- Set the Column Separator appropriately to match the structure or your text file.
- Ensure “Use first row as column headers” is set appropriately for your text file.
- If any columns are not required they can be disabled using the checkboxes at the top of each column.
- Click Finish. The import confirmation will appear, click Close.
Article on importing and working with 50 million data records from masterdataanalysis.com
Article on using Power Pivot to create a pivot table from more than one excel sheet from contextures.com
The first step of using either tool is to gather together the data you require for reporting. Depending on which tool you are using will dictate what data sources you have access too. Only the most common sources are covered in this article.
Obtaining Data From Access - Excel PowerPivot
- From Excel PowerPivot window select Home , From Database , From Access
2. Set the Database name to the file location of the *.accdb or *.mdb file.
3. If the database is password protected enter the Username and Password. Selecting “Save my password” will remember this for next time.
4. Click Next.
5. Unless you are familiar with SQL, leave the first option selected and click Next.
6. Tick the table(s) you wish to import. If you are unsure whether there are related tables that you should be considering click on Select Related Tables.
7. If you wish to filter the data being imported, click the Preview & Filter From here you can use normal Excel style filters.
8. From the same screen it is also possible to exclude columns by unticking them if you have no need for them.
9. Click Finish.
10. A summary window should appear when the import has finished. Click Close.
Obtaining Data From Access - Excel Power BI
- From PowerBI select Home, Get Data, More, Access Database, Connect
- Having selected your database tick the tables you wish to import and click Load.
Data Types - PowerPivot Only
As you import your tables into PowerPivot each column (field) are automatically assigned a data type. This process is normally very reliable but occasionally you may need to re-assign the data type.
To change the data type, select the appropriate column (field) within the PowerPivot window. Then use the Data Type drop down to select the correct data type. This functionality is not required in PowerBI.
Data Types Definitions
|Data Type||Data Type in DAX||Description|
|Whole Number||A 64 bit (eight-bytes) integer value||Numbers that have no decimal places. Integers can be positive or negative numbers, but must be whole numbers between -9,223,372,036,854,775,808 (-2^63) and 9,223,372,036,854,775,807 (2^63-1).|
|Decimal Number||A 64 bit (eight-bytes) real number||Real numbers are numbers that can have decimal places. Real numbers cover a wide range of values:Negative values from -1.79E +308 through -2.23E -308Zero
Positive values from 2.23E -308 through 1.79E + 308
However, the number of significant digits is limited to 15 decimal digits.
|TRUE/FALSE||Boolean||Either a True or False value.|
|Text||String||A Unicode character data string. Can be strings, numbers or dates represented in a text format.Maximum string length is 268,435,456 Unicode characters (256 mega characters) or 536,870,912 bytes.|
|Date||Date/time||Dates and times in an accepted date-time representation.Valid dates are all dates after January 1, 1900.|
|Currency||Currency||Currency data type allows values between -922,337,203,685,477.5808 to 922,337,203,685,477.5807 with four decimal digits of fixed precision.|
|N/A||Blank||A blank is a data type in DAX that represents and replaces SQL nulls. You can create a blank by using the BLANK function, and test for blanks by using the logical function, ISBLANK.|
Data To Avoid
Long text fields like comments – this is because PowerPivot uses Vertipaq to compress the data, which allows it to summarise data in a vertical fashion. Long descriptive text fields will reduce the efficiency of VertiPaq.
The number of significant digits is limited to 15 decimal digits. Numbers with more than 15 decimal digits will result in an in-memory database error.
Valid dates are all dates on or after 1 January 1900.
Currency data type allows values with four decimal digits of fixed precision. Anything more than this and you’ll get an error.
Date Table – PowerPivot only
There is a limitation in PowerPivot not seen in regular PivotTables where is possible to group dates clicking on the date row or column label and then from Pivot Tools, Options (or Analyse), Group Field. In Power Pivot this option is NOT available and instead an Excel sheet providing the groups required is needed.
As this is a frequently required source of data it is recommended that an Excel Workbook is dedicated to this role with the area range named, see External data located within a different workbook.
Date Table Rules
The date table must have at least one column with a contiguous set of dates with any groupings required alongside.
Every year in your date table must contain all of the days for each year. The ‘year’ can be a fiscal year or calendar year.
The month and year columns can easily be created using the =MONTH() and =YEAR() functions. When imported into PowerPivot, the above table will look like this:
We can see here that it is actually date and time. When you see 00:00:00AM after the date it means that the dates are at the day level of precision. If you were to have a DateTimeKey with say, 12:30:00PM, this would mean the values are at the minute level of precision. Values could also be to the hour level precision, or even seconds level of precision. The level of precision in the time value will have a significant impact on how you create your date table, and the relationships between it and your transaction table.
It’s important to note that Days are the smallest unit of time that DAX Time Intelligence functions can work with.
If you intend to aggregate your data to the time level, then your date table will need a date column with the time included. In-fact, it will need a date column with one row for every hour, or maybe even every minute, of every day, for every year in the date range. This is because, to create a relationship between the DateTimeKey column in the transaction table and the date column in the date table, you must have matching values, this can make for a very big date table.
Setting The Date Table
To use the date table correctly within PowerPivots it is necessary to tell PowerPivot which table is being used as the date table.
In the PowerPivot window, select the Date Table. On the Design Ribbon click Mark As Date Table.
If asked you may also need to specify which field contains the unique date.
The data collected through PowerPivot or Power BI is a series of separate tables.
The data is normally linked between these tables, for example a Customer is linked to an Order by a common account number.
To ensure the correct data is matched between tables relationships must be established. The establishing of relationships is often inherited from the original data source or by identifying compatible and like-named fields automatically.
To set or view relationships in PowerPivot click Home, Diagram View and in PowerBI click Manage Relationships.
|Excel PowerPivot||Excel Power BI (available in PowerPivot via Design|
|The diagram view will appear.|
|Existing relationships can be edited by double-clicking on their join line.|
Setting Relationships Between Tables - PowerPivot
In order for PowerPivots to work correctly, all the tables will need to be related to one another.
When relating two tables, the linking fields must contain the same data and be of the same data type.
The field in one table must be unique, known as the Primary Key, whilst the field in the second table is expected to have duplicated data and is known as the Foreign Key.
For example if linking an orders table to a customer table on a CustomerID field, the customers table should be a unique list of customers each with a different customer number.
The orders table is likely to have multiple orders for each customer, so the CustomerID field within this table is likely to have duplicates.
PowerPivot - Relationship Management
To create a relationship in PowerPivot, drag the field from one table carefully over the field of the required field from another table. The relationship line should appear between the tables.
Note that the position of the black lines do not point to the particular linking field, just the table as a whole.
In order to see which fields are linked it is necessary to click on the relationship line. The linked fields are then shown in blue.
Power BI - Relationship Management
In PowerBI click on New or Edit select the first table to be related from the drop-down provided and repeat for the second table to be related:
1. For Cardinality the most common relationship is One to Many (1:*), one Customer to many Orders.
2. Cross filter direction is most commonly Both
Article on table relationships from ExceleratorBI
Article on multiple column relationships from Radacad
Both PowerPivots and Power BI can perform calculations. Calculations typed into either application are referred to as DAX formulae. DAX stands for Data Analysis eXpression.
A formula is more commonly applied to every row of a table whereas a measure is a single measure/aggregate of many rows, for example total sales.
Formula - PowerPivot
To enter a formula:
- Click into the blank column, normally titled as “add column”.
- Click into the formula bar and type the expression.
- After pressing enter you will probably want to rename the column to something meaningful.
When typing an expression you can simply click another column to reference the data within it rather than typing the fieldname. This can be done even if the field is within another table.
Many DAX formulae are similar to Excel: =[Amount]*[Duration] This example formula divides the full invoice Amount by the number of days on the invoice, Duration. This will be applied automatically on every row. There are no cell references in DAX (such as “A1”), instead the field names are used.
DAX contains pre-defined functions as with Excel. Many of these functions are identical or at least similar to Excel. For example, if any invoice does not contain a duration and the invoice is for a flat rate you would need to use the following formula =IFERROR([Amount]/[Duration],[Amount]).
Formula - Power BI
To enter a formula:
- Click Modelling, New Column in the Calculations group.
- In the formula bar type the expression but prefixing it with a name. Note that field names have to be in the format of table[fieldname]. Using the same example as above the formula would be Daily Rate =tblCurrent_Courses[Amount]*tblCurrent_Courses[Duration]
- Once the formula has been entered it will be necessary to tick the calculation from the list of available Fields to the right of the PowerBI interface.
Measures - PowerPivot
The difference in a Measure is that it is entered below the table being measured. For example in a table of order items, to know how many orders were actually placed, taking into consideration there may be many items on a single order, you might enter Number of Orders:=DISTINCTCOUNT([InvNo])
Number of Orders is the name given to the measure, if this aspect of the measure is omitted your measures will be numbered: Measure 1:=DISTINCTCOUNT([InvNo])
Measures - Power BI
Click on Modeling, New Measure and start to type your measure, for example Number of Orders:=DISTINCTCOUNT([InvNo]) as above
Using Measures in Power Pivot from myexcelonline.com
To insert a PowerPivot PivotTable into Excel, click Home, PivotTable button in the PowerPivot window. With Power BI click on Home, New Visual and then change the Visualization to Matrix
It is important to use this button, NOT the PivotTable button on the Insert Ribbon.
PowerPivot PivotTables are quite similar to regular PivotTables so only the main differences are listed here.
- The field list shows all the fields from all the tables. Other than the extra layer of hierarchy, the usage is exactly the same.
- In PowerPivot There are extra sections available for slicers, Slicer Vertical and Slicer horizontal.
- The right mouse click on the selected fields gives slightly different options.
The following PivotTable is the result of the settings in the above screenshot with the addition of year also being placed into the “Slicer Horizontal” section.
The Slicers Vertical and Slicers Horizontal can be used to add fields to automatically position the slicers into placeholders above or to the left of the PivotTable. Slicers can still be used in the conventional way if required, but the placeholders make the job of using and positioning them much easier.
Power BI PivotTables
Power BI PivotTables are quite similar to regular PivotTables although visually quite modernized so it can be difficult to orientate.
Click on New Visual and then from Visualizations click Matrix
The field list shows all the fields from all the tables on the far right of the screen. Other than the extra layer of hierarchy, the usage is exactly the same.
Fields are dragged from the field pane into the Rows, Columns, Values and Visual Level Filters boxes the left of the fields pane.
To customise the function applied to a Value or to customise any field, click on the drop down arrow for that field.
Article on Unpivoting With Power Query by by Excelunplugged
PivotCharts can be applied to PowerPivot PivotTables in the normal way. However, there are some pre-set layouts which are quite useful. To see what’s available click the drop-down on the lower half of the PivotTable button on the PowerPivot Ribbon.
To quickly display two PivotCharts side by side click the lower half of the PivotTable button on the PowerPivot Ribbon then click “Two Charts (Horizontal)”.
Select your location, most likely a new worksheet, then click OK, two blank PivotCharts appear.
Click in the first chart and drag the fields for row, column and value as per a normal PivotTable. Repeat for the second chart.
When using multiple PivotCharts in this way, the slicers control both PivotCharts simultaneously.
Visualisations are the different graphic deliveries of data including the ‘Matrix’ which is the Pivot table equivalent detailed above.
To insert a chart in Power BI as with the Matrix:
1. Click on Home, New Visual Here you can see the new visual sitting to the right of the existing matrix/pivot table.
2. A new ghost visual will appear which can be changed into any Visual you require by selecting from the Visualization palette.
3. Having made your selection drag Fields as with the Matrix/Pivot to the relevant place holders on the right.
Repeat this process for as many charts as you require.
Tips For Designing Great Power BI Dashboards
There we have it. The beginner’s guide to Power BI and PowerPivot. If you’d like to learn more about these powerful technologies with us see here.