Category: Excel

Data Model, Development Tips, Excel, How To, PivotTable, PowerPivot, Report

Use hierarchies in PivotTables

Ashwini Tripathi

Recall from my previous post Create a hierarchy in PowerPivot Data Model, We will continue from where we left our earlier post.

Now we have a Sports hierarchy and Locations hierarchy, we can add them to PivotTables or Power View, and quickly get results that include useful groupings of data.

Prior to creating hierarchies, you had to add individual fields to the PivotTable, and arrange those fields how you wanted them to be viewed.

In this post we will use the hierarchies created in the previous post to quickly refine our PivotTable.

Open the Excel workbook which we used in our previous post and Insert PivotTable as shown in below screen.
ExcelData-17

  • Add field in the PivotTable Medal from Medal Table in the FILTERS area, and Count of Medal from Medal Table in the VALUES area. Your nearly empty PivotTable should look like the following screen.

ExcelData-18

  • From the PivotTable Fields area, drag SDE from the Events table to the ROWS area. Then drag Locations from the Hosts table into the COLUMNS area. Just by dragging those two hierarchies, your PivotTable is populated with a data, all of which is arranged in the hierarchy we defined in the previous steps. Your screen should look like the following screen.

ExcelData-19

  • You can expand any of those Sports in the PivotTable, which is the top level of the SDE hierarchy, and see information in the next level down in the hierarchy (discipline). If a lower level in the hierarchy exists for that discipline, you can expand the discipline to see its events. You can do the same for the Location hierarchy, the top level of which is Season, which shows up as Summer and Winter in the PivotTable.

ExcelData-20

ExcelData-21
By dragging those two hierarchies, you quickly created a PivotTable with interesting and structured data that you can drill into, filter, and arrange.

I will come up with more details and other options on this topic in my upcoming posts. Till then keep learning and practicing.

Data Model, Development Tips, Excel, How To, PowerPivot, Report

Create a hierarchy in PowerPivot Data Model

Ashwini Tripathi

We will be using the Excel workbook we used in our earlier posts. Open the Excel file, you can find the link for download in my earlier posts or from blog Menu.

SharePath

Most Data Models include data that is inherently hierarchical. The Olympics data is also hierarchical. It’s helpful to understand the Olympics hierarchy, in terms of sports, disciplines, and events.

For each sport, there is one or more associated disciplines (sometimes there are many).

And for each discipline, there is one or more events (again, sometimes there are many events in each discipline).

The following Table illustrates the hierarchy.

ExcelData-13

In this post we will create two hierarchies within the Olympic data. Then use these hierarchies to see how hierarchies make organizing data easy in PivotTables and in Power View in upcoming posts.

Create a Sport hierarchy

In Power Pivot, switch to Diagram View. Expand the Events table so that you can more easily see all of its fields.

ExcelData-14

  • Press and hold Ctrl, and click the Sport, Discipline, and Event fields. With those three fields selected, right-click and select Create Hierarchy. A parent hierarchy node, Hierarchy 1, is created at the bottom of the table, and the selected columns are copied under the hierarchy as child nodes. Verify that Sport appears first in the hierarchy, then Discipline, then Event.

ExcelData-15

  • Double-click the title, Hierarchy1, and type SDE to rename your new hierarchy. You now have a hierarchy that includes Sport, Discipline and Event. Your Events table now looks like the above screen.
  • Still in Diagram View in Power Pivot, select the Hosts table and click the Create Hierarchy button in the table header, as shown in the following screen.

ExcelData-16

  • An empty hierarchy parent node appears at the bottom of the table.
  • Type Locations as the name for your new hierarchy.
  • There are many ways to add columns to a hierarchy. Drag the Season, City and NOC_CountryRegion fields onto the hierarchy name (in this case, Locations) until the hierarchy name is highlighted, then release to add them.
  • Right-click EditionID and select Add to Hierarchy. Choose Locations.
  • Ensure that your hierarchy child nodes are in order. From top to bottom, the order should be: Season, NOC, City, EditionID. If your child nodes are out of order, simply drag them into the appropriate ordering in the hierarchy. Your table should look like the above screen.

Your Data Model now has hierarchies that can be put to good use in reports. In the upcoming posts we will learn how these hierarchies can make our report creation faster, and more consistent.

Stay tuned for more details, will come up with usage of hierarchy in my upcoming post.

Till then keep learning & practicing.

Data Model, Development Tips, Excel, How To, PowerPivot, Report

Extend the Data Model using calculated columns

Ashwini Tripathi

Recall from my previous post Add a relationship using Diagram View in Power Pivot

Which I have left with below note. I will be continuing from where I left my previous post.

It’s nice when the data in your Data Model has all the fields necessary to create relationships, and mash up data to visualize in Power View or PivotTables. But tables aren’t always so cooperative, so in today’s post will describe how to create a new column, using DAX that can be used to create a relationship between tables.

Open the Olympics Excel sheet which we used in our previous post.

ExcelData-7
Delete the column EditionID from sheet Medals & Hosts Table/Sheet.
ExcelData-8

After deleting the Column EditionID from Table/Sheet Select Update All in Ribbon from PowerPivot Tab. This will update the column in Data Model.

Since we are going to learn creating relationship on calculated fields.

In Hosts, we can create a unique calculated column by combining the Edition field (the year of the Olympics event) and the Season field (Summer or Winter). In the Medals table there is also an Edition field and a Season field, so if we create a calculated column in each of those tables that combines the Edition and Season fields, we can establish a relationship between Hosts and Medals. The following screen shows the Hosts table, with its Edition and Season fields selected
ExcelData-9

Select the Hosts table in Power Pivot. Adjacent to the existing columns is an empty column titled Add Column. Power Pivot provides that column as a placeholder. There are many ways to add a new column to a table in Power Pivot, one of which is to simply select the empty column that has the title Add Column.

In the formula bar, type the following DAX formula. The CONCATENATE function combines two or more fields into one. As you type, AutoComplete helps you type the fully qualified names of columns and tables, and lists the functions that are available. Use tab to select AutoComplete suggestions. You can also just click the column while typing your formula, and Power Pivot inserts the column name into your formula.

=CONCATENATE([Edition],[Season])

When you finish building the formula, press Enter to accept it.

Values are populated for all the rows in the calculated column. If you scroll down through the table, you see that each row is unique – so we’ve successfully created a field that uniquely identifies each row in the Hosts table. Such fields are called a primary key.

Let’s rename the calculated column to EditionID. You can rename any column by double-clicking it, or by right-clicking the column and choosing Rename Column. When completed, the Hosts table in Power Pivot looks like the following screen.
ExcelData-10

Next let’s create a calculated column in Medals that matches the format of the EditionID column we created in Hosts, so we can create a relationship between them.

When you created a new column, Power Pivot added another placeholder column called Add Column. Next we want to create the EditionID calculated column, so select Add Column. In the formula bar, type the following DAX formula and press Enter.

=CONCATENATE([Year],[Season])

Rename the column by double-clicking CalculatedColumn1 and typing EditionID.
ExcelData-11

Next let’s use the calculated columns we created to establish a relationship between Hosts and Medals.

In the Power Pivot window, select Home > View > Diagram View from the ribbon.

Drag the EditionID column in Medals to the EditionID column in Hosts. Power Pivot creates a relationship between the tables based on the EditionID column, and draws a line between the two columns, indicating the relationship.
ExcelData-12

Now you can see the relationship between Host & Medal table is established on calculated field EditionID in both the tables.

Stay tuned for more details on this topic. I will come up with next step in this series in my upcoming post.

Till then keep learning & practicing.

Data Model, Development Tips, Excel, How To, PowerPivot, Report

Add a relationship using Diagram View in Power Pivot

Ashwini Tripathi

Open the Workbook which we prepared in the last exercise in our previous post.

Add Excel Sheet/Table to the PowerPivot Data Model

  • In the Power Pivot window, in the View section, click Diagram View.
  • Use the slide bar to resize the diagram so that you can see all objects in the diagram. Rearrange the tables by dragging their title bar, so they’re visible and positioned next to one another. Notice that all tables are unrelated to each other, we will create relationship among these tables.

After adding relationship the Diagram will Look as below.

ExcelData-6

  • Let start creating the relationship.
  • Sports->SportID click and drag to Disciplines->SportID and release. You will see the relation is built between these two tables on SportID.

Similarly follow to create other relations too as shown in below image.
ExcelData-5

You notice that both the Medals table and the Events table have a field called DisciplineEvent. Upon further inspection, you determine that the DisciplineEvent field in the Events table consists of unique, non-repeated values.

The DisciplineEvent field represents a unique combination of each Discipline and Event. In the Medals table, however, the DisciplineEvent field repeats many times. That makes sense, because each Discipline+Event combination results in three awarded medals (gold, silver, bronze), which are awarded for each Olympics Edition the Event is held. So the relationship between those tables is one (one unique Discipline+Event entry in the Disciplines table) to many (multiple entries for each Discipline+Event value).

  • Create a relationship between the Medals table and the Events table. While in Diagram View, drag the DisciplineEvent field from the Events table to the DisciplineEvent field in Medals. A line appears between them, indicating a relationship has been established.
  • Click the line that connects Events and Medals. The highlighted fields define the relationship, as shown in the above screen.
  • To connect Hosts to the Data Model, we need a field with values that uniquely identify each row in the Hosts table. Then we can search our Data Model to see if that same data exists in another table. Looking in Diagram View doesn’t allow us to do this. With Hosts selected, switch back to Data View.
  • After examining the columns, we realize that Hosts doesn’t have a column of unique values. (Assume EditionID Field is not available in the Host & Medal Table, in my case I have already added this Field in my workbook I shared). In such case we’ll have to create it using a calculated column, and Data Analysis Expressions (DAX).

It’s nice when the data in your Data Model has all the fields necessary to create relationships, and mash up data to visualize in Power View or PivotTables. But tables aren’t always so cooperative, so the next post will describe how to create a new column, using DAX that can be used to create a relationship between tables.

We will see how to work with calculated fields in our next upcoming post.

Data Model, Development Tips, Excel, How To, PowerPivot, Report

Add Excel Sheet/Table to the PowerPivot Data Model

Ashwini Tripathi

Download the Excel file from the link provided in previous post.

Import data using copy and paste from Excel sheet or other source for PowerPivot Data Model.

Open the Workbook:

ExcelData-3

Select sheet one by one and Add to Data model of PowerPivot.

ExcelData-4

Make sure all Table/Sheet is added to Data Model of PowerPivot.

I have create a sample Excel File for you which you can down load from the Shared Path using link: http://1drv.ms/1GpZFd4

This file have couple of sheets having data related to Olympics which I have compiled from MS Office support site.

You can find the link on the Menu of my blogsite too to access the shared files.

I will be using this file for couple of walkthrough in my upcoming posts.

Data Model, Development Tips, Excel, PowerPivot

Import data using copy and paste from Excel sheet or other source for PowerPivot Data Model.

Ashwini Tripathi

You can copy & Paste data from an Excel workbook, or import data from a table on a web page, or any other source from which we can copy and paste into Excel. In the following steps, you add the data from a table.

  • Insert a new Excel worksheet, and give it the name desired any meaningful name that describes the data.
  • Select and copy the data, including the table headers.
  • In Excel, place your cursor in cell A1 of the above created worksheet and paste the data.
  • Format the data as a table. You press Ctrl + T to format the data as a table, or from HOME > Format as Table. Since the data has headers, select My table has headers in the Create Table window that appears.

ExcelData-1

  • Name the table. In TABLE TOOLS > DESIGN > Properties locate the Table Name field, and type desired name.

ExcelData-2

  • Format your data using Excel std. formatting tool.
  • Save the workbook.This file have couple of sheets having data related to Olympics which I have compiled from MS Office support site.
  • You can find the link on the Menu of my blogsite too to access the shared files.
  • I have create a sample Excel File for you which you can down load from the Shared Path using link: http://1drv.ms/1GpZFd4

I will be using this file for couple of walkthrough in my upcoming posts.

Development Tips, Excel, How To, Power View, PowerPivot, Report

Power View in Excel 2013

Ashwini Tripathi

Power View is an interactive data exploration, visualization, and presentation experience that encourages intuitive ad-hoc reporting.

Power View is a feature of Microsoft Excel 2013, and of Microsoft SharePoint Server 2010 and 2013 as part of the SQL Server 2012 Service Pack 1 Reporting Services Add-in for Microsoft SharePoint Server Enterprise Edition.

Power View has these features, as part of Power BI for Office 365:

  • Create Power View sheets in Excel and then view them in the Power BI Windows Store app.
  • View Power View in Excel sheets in your browser, without installing Silverlight.

Data sources for Power View

In Excel 2013, you can use data right in Excel as the basis for Power View in Excel and SharePoint.

When you add tables and create relationships between them, Excel is creating a Data Model behind the scenes.

A data model is a collection of tables and their relationships reflecting the real-world relationships between business functions and processes—for example, how Products relates to Inventory and Sales.

You can continue modifying and enhancing that same data model in Power Pivot in Excel, to make a more sophisticated data model for Power View reports.

With Power View you can interact with data:

  • In the same Excel workbook as the Power View sheet.
  • In data models in Excel workbooks published in a Power Pivot Gallery.
  • In tabular models deployed to SQL Server 2012 Analysis Services (SSAS) instances.
  • In multidimensional models on an SSAS server (if you’re using Power View in SharePoint Server).

Recall from my previous post Creating My First Report using PowerPivot, I will be using same Data Model to demonstrate the feature of Power View, also same report in different format.

Let’s design Customer wise Sales Report using Power View.
PowerView-1

I am using the same Workbook which we used for PowerPivot creating Matrix report for Item Vs Customer Sales.

From Insert Tab choose Power View Reports in ribbon.

Remember this workbook already having Data Model with tables Customer, Item, Cust. Ledger Entry & Item Ledger Entry. One which we created during our previous exercise during walkthrough of PowerPivot.

We already have Relationship defined between Customer & Item Ledger Entry (No. -> Source No.), also Item & Item Ledger Entry (No. -> Item No.).
PowerView-2

You can View the Relationship using Relationship from Ribbon Power View Tab.

Once the Power View Sheet is inserted the Power View Tab will be Visible.

This will list the two relations which we created earlier in previous exercise post.

However we will be requiring one more relationship for this report. Add using New button on Manage Relationship window.

Enter the Relation for Customer & Cust. Ledger Entry (No. -> Customer No.).

You can see all the 4 tables are listed in Field List Pane.
PowerView-3
Arrange the Fields from respective tables as shown in above screenshot.
PowerView-4
Arrange the fields as shown in above screenshot.

Design of your report should be similar to one shown in below screenshot.
PowerView-5

Resize the Table area to fit the area and the way you want to represent the data.

When a Customer is selected in Title Area, below two tables show the Item Sales Quantity & Total Value for the selected Customer.

You can add fields in the Filter Pane to slice the data accordingly.

I will come up with more details in my upcoming posts.

Till then stay tuned and keep practicing.

Development Tips, Excel, How To, PowerPivot, Report

Add Slicers to PivotTables in PowerPivot

Ashwini Tripathi

Recall from my earlier post Creating My First Report using PowerPivot in which we created a Items Vs Customer Sales matrix report.

I am going to use same report to demonstrate how we can add slicer to this report.

Slicers are one-click filtering controls that narrow the portion of a data set shown in PivotTables and PivotCharts. Slicers can be used in both Microsoft Excel workbooks and PowerPivot workbooks, to interactively filter and analyze data.
PowerPivot-30

Open the report we created in our previous post.

From Insert Tab select Slicer in the ribbon.
PowerPivot-31

Choose the source Connection/Data model and respond Open.

Select the Customer Table.

Select the Country/Region Code field and OK.
PowerPivot-32

A Slicer will be added to the sheet.

Re-size to fit and drag the Slicer to position at desired location on sheet.

You can give desired Caption to your Slicer by editing the Slicer Caption.

Most important is to select the pivot table on which this Slicer will operate.

Select Report Connection, and from preceding window select the PivotTable.
PowerPivot-33
Above is the screenshot with applied filter on Country/Region Code = DE.

Stay tuned to know more options, I will come up with more details in my upcoming posts.

Till then keep practicing.

Excel, PowerPivot

Basics of Power Pivot for Excel – 2013

Ashwini Tripathi

Dear friends, I have published couple of posts on this topic. I will be adding more advanced features and details related to this in my upcoming posts.

For your ready reference below I present Links to those posts.

PowerPivot for Excel

Start the Power Pivot in Microsoft Excel add-in

Troubleshooting: Power Pivot Ribbon Disappears

PowerPivot Creating a Data Model in Excel 2013

Adding more tables to the Data Model using Existing Connection – In PowerPivot

Add relationships to Data Model in PowerPivot

How to add Filter for data retrieval in PowerPivot Data model.

Create a calculated column in PowerPivot

Creating My First Report using PowerPivot

In Excel 2013, PowerPivot and Power View are no longer separate add-ins that need to be downloaded and installed. These add-ins are natively included.

PowerPivot in Excel 2013 is functionally very similar to the PowerPivot add-in for Excel 2010.

PowerPivot is an add-in that lets end users gather, store, model, and analyze large amounts of data in Excel. Power View provides intuitive data visualization of PowerPivot models and SQL Server Analysis Services (SSAS) tabular mode databases.

If you’re unfamiliar with either PowerPivot or Power View, I encourage you to first review my previous post links provided above to understand the basics.

Some parts of the PowerPivot architecture is embedded inside of Excel 2013.

  • The PowerPivot version in Excel 2013 no longer uses a separate PowerPivot Fields list. Instead, the built-in PivotTable Fields list is used. This means that some capabilities from the Excel 2010 add-in (e.g., searching for fields by name, creation of slicers from the field list, surfacing of column descriptions when hovering over a field) are no longer available.
  • Workbooks with PowerPivot models are no longer limited to 2GB in size in Excel 2013. However, the 2GB limit still applies to workbooks that will be published to SharePoint.
  • In Excel 2013, a refresh of a PivotTable or PivotChart will, by default, initiate a refresh of the underlying data connections in the Data Model. This is very different from Excel 2010, where a PivotTable refresh only re-queries the model. The new refresh behaviour can be changed by clicking Connections on the Data tab, selecting Properties, and clearing the Refresh this connection on Refresh All check box.
  • Stay tuned for more information on this topic. Till then keep practicing & exploring.
  • In Excel 2013, a Power View “report” is a worksheet rather than an .rdlx file. There’s no concept of multiple report views. Instead, multiple Power View worksheets can be created within a single Excel workbook.

Stay tuned for more information on this topic. Till then keep practicing & exploring.

Development Tips, Excel, How To, PowerPivot

Troubleshooting: Power Pivot Ribbon Disappears

Ashwini Tripathi

In rare cases, the Power Pivot ribbon will disappear from the menu if Excel determines that the add-in is destabilizing to Excel. This might occur if Excel closes unexpectedly while the Power Pivot window is open. To restore the Power Pivot menu, do the following:

  • Go to File > Options > Add-Ins.

In the Manage box, click Disabled Items > Go
PowerPivot-29

  • Select Microsoft Office Power Pivot in Microsoft Excel 2013 and then click Enable.

If the previous steps do not restore the Power Pivot ribbon, or if the ribbon disappears when you close and reopen Excel, try the following:

  • Close Excel.
  • Point to Start > Run and then type regedit.
  • In Registry Editor, expand HKEY_CURRENT_USER > Software > Microsoft > Office > 15.0 > User Settings.
  • Right-click PowerPivotExcelAddin and then click Delete.
  • Scroll back up to the top of Registry Editor.
  • Expand HKEY_CURRENT_USER > Software > Microsoft > Office > Excel > Addins.
  • Right-click PowerPivotExcelClientAddIn.NativeEntry.1 and then click Delete.
  • Close Registry Editor.
  • Open Excel.
  • Enable the add-in using the steps at the top of this article.