Excel – PowerPivot And PowerBI


Excel PowerPivot

What are Excel Power Pivots and Excel Power BI

PowerPivot, a free Excel Add-in since version 2010 and Power BI which is essentially PowerPivot’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.

The History

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.

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.

Introduction

BCh 1 - Excel PowerPivot Iconoth PowerPivot and PowerBI can be downloaded free from the Microsoft website. PowerBI downloads as a desktop icon seen opposite, PowerPivot as an Excel Add-in.

 

Ch 1 - 2 - Excel PowerPivot & Power BI Com Add-INUntitledTo install the add-in click on File, Options and then on AddIns from the navigator on the left. Change the Manage drop down at the bottom of the screen to COM Addins 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.

Ch 1 - 3 - Excel PowerPivot Main Window

This is the PowerPivot main window.

Ch 1 - 4 - Excel PowerPivot Main Window

This is the opening window for Power BI

Ch 1 - 5 - Excel Power BI - Opening Window

NEXT - Obtaining Data From Excel

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.

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 PowerBI 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.

  1. From the PowerPivot window, click Home , From Other SourcesCh 2 - 10 - Excel PowerPivot Data From Other Sources
  2. In the dialog box that appears, scroll down and select Excel File.Ch 2 - 11 - Excel PowerPivot Table Import Wizard
  3. Click Next
  4. Browse to the Excel file and tick “Use first for as column headers” (assuming your data has headers).Ch 2 - 12 - Excel PowerPivot Table Import Wizard 2
  5. Click Next
  6. Select the required named range.Ch 2 - 13 - Excel PowerPivot Sources TableIn 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

Ch 2 - 14 - Excel PowerPivot Table Import Wizard 3

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

  1. Select a single cell within your data and from the PowerPivot ribbon, click Create Linked Table.

 

  1. 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.

  1. From the PowerPivot windows click Home , From Other Sources, From TextCh 2 - 16 - Excel PowerPivot Import From Text

Ch 2 - 17 - Excel PowerPivot Table Import Wizard 4

  1. Use the Browse button to locate the text file.
  2. Use the Friendly connection name to assign the name of the table that PowerPivot will use.
  3. Set the Column Separator appropriately to match the structure or your text file.
  4. Ensure “Use first row as column headers” is set appropriately for your text file.
  5. If any columns are not required they can be disabled using the checkboxes at the top of each column.
  6. Click Finish. The import confirmation will appear, click Close.

NEXT - Obtaining Data From Access

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

  1. From Excel PowerPivot window select Home , From Database , From Access

Ch 2 - 1 - Excel PowerPivot DB Access

2. Set the Database name to the file location of the *.accdb or *.mdb file.

Ch 2 - 2 - Excel PowerPivot Database Connector Image3. If the database is password protected enter the Username and Password. Selecting “Save my password” will remember this for next time.

4. Click Next.

Ch 2 - 3 - Excel PowerPivot Database Connector - Step 2

5. Unless you are familiar with SQL, leave the first option selected and click Next.

Ch 2 - 4 - Excel PowerPivot Table Import Wizard

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.

Ch 2 - 5 - Excel PowerPivot Preview Selected Table Image

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.

Ch 2 - 6 - Excel PowerPivot Table Import Wizard Final Screen

10. A summary window should appear when the import has finished. Click Close.

Obtaining Data From Access - Excel PowerBI

  1. From PowerBI select Home, Get Data, More, Access DatabaseConnect

Ch 2 - 7 Excel PowerBI Database Access Get Screen

  1. Having selected your database tick the tables you wish to import and click Load.

CH 2 - 8 - Excel PowerBI Navigator Screen Image

NEXT - Excel PowerPivot - Data Types and Date Tables

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.

Ch 3 - 1 - PowerPivot Data Type Menu

Data Types Definitions

Data TypeData Type in DAXDescription
Whole NumberA 64 bit (eight-bytes) integer valueNumbers 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 NumberA 64 bit (eight-bytes) real numberReal 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/FALSEBooleanEither a True or False value.
TextStringA 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.
DateDate/timeDates and times in an accepted date-time representation.Valid dates are all dates after January 1, 1900.
CurrencyCurrencyCurrency 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/ABlankA 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 Power Pivot 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.

Ch 3 - 2 - PowerPivot Data Table Example

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:

 

Ch 3 - 3 - PowerPivot Example Data Table With Months

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.

Ch 3 - 4 - Excel PowerPivot Design Tab

 

If asked you may also need to specify which field contains the unique date.

 

Ch 3 - 5 - Excel PowerPivot Select Data Data Image

NEXT - Relationships Between Tables

The data collected through PowerPivot or PowerBI 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 PowerPivotExcel PowerBI (available in PowerPivot via Design
Ch 4 - 1 - Excel PowerPivot Diagram View ImageCh 4 - 2 - Excel PowerBI Manage Relationships Image
The diagram view will appear.

 

Ch 4 - 3 - Excel PowerPivot Diagram ViewImage

Ch 4 - 4 - Excel PowerBI Manage Relationships Dialogue Box
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.

Ch 4 - 3 - Excel PowerPivot Diagram ViewImage

PowerBI - 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:

 

Ch 4 - 5 - Excel PowerBI Edit Relationships Dialogue Box

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

NEXT - DAX Formula And Measures

Both PowerPivots and PowerBI 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:

  1. Click into the blank column, normally titled as “add column”.
  2. Click into the formula bar and type the expression.
  3. 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 - PowerBI

To enter a formula:

  1. Click Modelling, New Column in the Calculations group.
  2. 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]
  3. 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])

Ch 5 - 1 - Excel PowerPivot Measures Image

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 - PowerBI

Click on Modeling, New Measure and start to type your measure, for example Number of Orders:=DISTINCTCOUNT([InvNo]) as above

 

Ch 5 - 2 - Excel PowerBI Measures Image

 

 

NEXT - PowerPivot & PowerBI PivotTables

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

Ch 6 - 1 - Excel PowerPivot New Visual Button

 

Ch 6 - 2 - Excel PowerPivot New Matrix Button

 

It is important to use this button, NOT the PivotTable button on the Insert Ribbon.

Ch 6 - 3 - Excel PowerPivot PivotTable Button

PowerPivot PivotTables

Ch 6 - 4 - Excel PowerPivot PivotTable Field ListPowerPivot 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.

Ch 6 - 5 - Excel PowerPivot Slicer Image

 

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.

PowerBI PivotTables

Ch 6 - 6 - Excel PowerBI PivotTable Field SelectionPowerBI 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.

Ch 6 - 7 - Excel PowerBI PivotTable Field Function Customisation

 

Ch 6 - 8 - Excel PowerBI PivotTable Output

NEXT - PowerPivot PivotCharts & Visualisations

Ch 7 - 1 - Excel PowerPivot PivotCharts Selection ButtonPivotCharts 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.

Ch 7 - 2 - Excel PowerPivot PivotCharts Two Horizontal Charts

 

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.

Ch 7 - 3 - Excel PowerPivot PivotCharts Example

Visualizations (Charts)

Visualisations are the different graphic deliveries of data including the ‘Matrix’ which is the Pivot table equivalent detailed above.

To insert a chart in PowerBI 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.

Ch 7 - 4 - Excel PowerBI Visualisations New Visual Commands

2. A new ghost visual will appear which can be changed into any Visual you require by selecting from the Visualization palette.

Ch 7 - 5 - Excel PowerBI Visualisations New Visualisation Palette

Ch 7 - 6 - Excel PowerBI Visualisations Field Holder Image

 

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.

 

 

 

Ch 7 - 7 - Excel PowerBI Visualisations Completed Image