The Beginners Guide To Excel Power Pivot And 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.

1. From the PowerPivot window, click Home , From Other Sources
2. In the dialog box that appears, scroll down and select Excel File.
3. Click Next
4. Browse to the Excel file and tick “Use first for as column headers” (assuming your data has headers).
5. Click Next
6. 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

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

2. 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 Text

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

FURTHER READING
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

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

1. From PowerBI select Home, Get Data, More, Access Database, Connect
   

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

FURTHER READING
Data Import Best Practices by SQLBI.com
The video series DAX Friday by Curbal

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 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
FURTHER READING
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:

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 - Power BI

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])

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

FURTHER READING
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

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.

FURTHER READING
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.

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

FURTHER READING
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.