Category: excel Power Pivot

Excel Power Pivot – Managing Data Model ”; Previous Next The major use of Power Pivot is its ability to manage the data tables and the relationships among them, to facilitate analysis of the data from several tables. You can add an excel table to the Data Model while you are creating a PivotTable or directly from the PowerPivot Ribbon. You can analyze data from across multiple tables only when relationships exist among them. With Power Pivot, you can create relationships from the Data View or Diagram View. Moreover, if you had chosen to add a table to the Power Pivot, you need to add a relationship as well. Adding Excel Tables to Data Model with PivotTable When you create a PivotTable in Excel, it is based only on a single table / range. In case you want to add more tables to the PivotTable, you can do so with the Data Model. Suppose you have two worksheets in your workbook − One containing the data of salespersons and the regions they represent, in a table- Salesperson. Another containing the data of sales, region and month wise, in a table – Sales. You can summarize the sales – salesperson-wise as given below. Click the table – Sales. Click the INSERT tab on the Ribbon. Select PivotTable in the Tables group. An empty PivotTable with the fields from the Sales table – Region, Month and Order Amount will be created. As you can observe, there is a MORE TABLES command below the PivotTable Fields list. Click on MORE TABLES. The Create a New PivotTable message box appears. The message displayed is- To use multiple tables in your analysis, a new PivotTable needs to be created using the Data Model. Click Yes A New PivotTable will be created as shown below − Under PivotTable Fields, you can observe that there are two tabs – ACTIVE and ALL. Click the ALL tab. Two tables- Sales and Salesperson, with the corresponding fields appear in the PivotTable Fields list. Click the field Salesperson in the Salesperson table and drag it to ROWS area. Click the field Month in the Sales table and drag it to ROWS area. Click the field Order Amount in the Sales table and drag it to ∑ VALUES area. The PivotTable is created. A message appears in the PivotTable Fields – Relationships between tables may be needed. Click the CREATE button next to the message. The Create Relationship dialog box appears. Under Table, select Sales. Under Column (Foreign) box, select Region. Under Related Table, select Salesperson. Under Related Column (Primary) box, select Region. Click OK. Your PivotTable from the two tables on two worksheets is ready. Further, as Excel stated while adding the second table to the PivotTable, the PivotTable got created with Data Model. To verify, do the following − Click the POWERPIVOT tab on the Ribbon. Click Manage in the Data Model group. The Data View of the Power Pivot appears. You can observe that the two Excel tables that you used in creating the PivotTable are converted to data tables in the Data Model. Adding Excel Tables from a Different Workbook to Data Model Suppose the two tables – Salesperson and Sales are in two different workbooks. You can add the Excel table from a different workbook to the Data Model as follows − Click the Sales table. Click the INSERT tab. Click PivotTable in the Tables group. The Create PivotTable dialog box appears. In the Table/Range box, type Sales. Click on New Worksheet. Check the box Add this data to the Data Model. Click OK. You will get an empty PivotTable on a new worksheet with only the fields corresponding to the Sales table. You have added the Sales table data to the Data Model. Next, you have to get the Salesperson table data also into Data Model as follows − Click on the worksheet containing Sales table. Click the DATA tab on the Ribbon. Click Existing Connections in the Get External Data group. The Existing Connections dialog box appears. Click on the Tables tab. Under This Workbook Data Model, 1 table is displayed (This is the Sales table that you added earlier). You also find the two workbooks displaying the tables in them. Click Salesperson under Salesperson.xlsx. Click Open. The Import Data dialog box appears. Click on PivotTable Report. Click on New worksheet. You can see that the box – Add this data to the Data Model is checked and inactive. Click OK. The PivotTable will be created. As you can observe the two tables are in the Data Model. You might have to create a relationship between the two tables as in the previous section. Adding Excel Tables to Data Model from the PowerPivot Ribbon Another way of adding Excel tables to Data Model is doing so from the PowerPivot Ribbon. Suppose you have two worksheets in your workbook − One containing the data of salespersons and the regions they represent, in a table – Salesperson. Another containing the data of sales, region and month wise, in a table – Sales. You can add these Excel tables to the Data Model first, before doing any analysis. Click on the Excel table – Sales. Click the POWERPIVOT tab on the Ribbon. Click Add to Data Model in the Tables group. Power Pivot window appears, with the data table Salesperson added to it. Further a tab – Linked Table appears on the Ribbon in the Power Pivot window. Click on the Linked Table tab on the Ribbon. Click on Excel Table: Salesperson. You can find that the names of the two tables present in your workbook are displayed and the name Salesperson is ticked. This means the data table Salesperson is linked to the Excel table Salesperson. Click Go to Excel Table. Excel window with worksheet containing Salesperson table appears. Click the Sales worksheet tab. Click the Sales table. Click Add to Data Model in the Tables group on the Ribbon. The Excel

Excel Power Pivot – Quick Guide ”; Previous Next Excel Power Pivot – Overview Excel Power Pivot is an efficient, powerful tool that comes with Excel as an Add-in. With Power Pivot, you can load hundreds of millions of rows of data from external sources and manage the data effectively with its powerful xVelocity engine in a highly compressed form. This makes it possible to perform the calculations, analyze the data, and arrive at a report to draw conclusions and decisions. Thus, it would be possible for a person with hands-on experience with Excel, to perform the high-end data analysis and decision making in a matter of few minutes. This tutorial will cover the following − Power Pivot Features What makes Power Pivot a strong tool is the set of its features. You will learn the various Power Pivot features in the chapter − Power Pivot Features. Power Pivot Data from Various Sources Power Pivot can collate data from various data sources to perform the required calculations. You will learn how to get data into Power Pivot, in the chapter − Loading Data into Power Pivot. Power Pivot Data Model The power of Power Pivot lies in its database- Data Model. The data is stored in the form of data tables in the Data Model. You can create relationships between the data tables to combine the data from different data tables for analysis and reporting. The chapter − Understanding Data Model (Power Pivot Database) gives you the details about the Data Model. Managing Data Model and Relationships You need to know how you can manage the data tables in the Data Model and the relationships between them. You will get the details of these in the chapter − Managing Power Pivot Data Model. Creating Power Pivot Tables and Power Pivot Charts Power PivotTables and Power Pivot Charts provide you a way to analyze the data for arriving at conclusions and/or decisions. You will learn how to create Power PivotTables in the chapters − Creating a Power PivotTable and Flattened PivotTables. You will learn how to create Power PivotCharts in the chapter − Power PivotCharts. DAX Basics DAX is the language used in Power Pivot to perform calculations. The formulas in DAX are similar to Excel formulas, with one difference − while the Excel formulas are based on individual cells, DAX formulas are based on columns (fields). You will understand the basics of DAX in the chapter − Basics of DAX. Exploring and Reporting Power Pivot Data You can explore the Power Pivot Data that is in the Data Model with Power PivotTables and Power Pivot Charts. You will get to learn how you can explore and report data throughout this tutorial. Hierarchies You can define data hierarchies in a data table so that it would be easy to handle related data fields together in Power PivotTables. You will learn the details of the creation and usage of Hierarchies in the chapter − Hierarchies in Power Pivot. Aesthetic Reports You can create aesthetic reports of your data analysis with Power Pivot Charts and/or Power Pivot Charts. You have several formatting options available to highlight the significant data in the reports. The reports are interactive in nature, enabling the person looking at the compact report to view any of the required details quickly and easily. You will learn these details in the chapter − Aesthetic Reports with Power Pivot Data. Excel Power Pivot – Installing Power Pivot in Excel provides a Data Model connecting various different data sources based on which the data can be analyzed, visualized, and explored. The easy-to-use interface provided by Power Pivot enables a person with hands-on experience in Excel to effortlessly load data, manage the data as data tables, create relationships among the data tables, and perform the required calculations to arrive at a report. In this chapter, you will learn, what makes Power Pivot a strong and sought after tool for analysts and decision makers. Power Pivot on the Ribbon The first step to proceed with Power Pivot is to ensure that the POWERPIVOT tab is available on the Ribbon. If you have Excel 2013 or later versions, the POWERPIVOT tab appears on the Ribbon. If you have Excel 2010, POWERPIVOT tab might not appear on the Ribbon if you have not already enabled the Power Pivot add-in. Power Pivot Add-in Power Pivot Add-in is a COM Add-in that needs to be enabled to get the complete features of Power Pivot in Excel. Even when POWERPIVOT tab appears on the ribbon, you need to ensure that the add-in is enabled to access all the features of Power Pivot. Step 1 − Click the FILE tab on the Ribbon. Step 2 − Click Options in the dropdown list. The Excel Options dialog box appears. Step 3 − Follow the instructions as follows. Click Add-Ins. In the Manage box, select COM Add-ins from the dropdown list. Click the Go button. The COM Add-Ins dialog box appears. Check Power Pivot and click OK. What is Power Pivot? Excel Power Pivot is a tool for integrating and manipulating large volumes of data. With Power Pivot, you can easily load, sort and filter data sets that contain millions of rows and perform the required calculations. You can utilize Power Pivot as an ad hoc reporting and analytics solution. The Power Pivot Ribbon as shown below has various commands, ranging from managing Data Model to creating reports. The Power Pivot window will have the Ribbon as shown below − Why is Power Pivot a Strong Tool? When you invoke Power Pivot, Power Pivot creates data definitions and connections that get stored with your Excel file in a compressed form. When the data at the source is updated, it is refreshed automatically in your Excel file. This facilitates the usage of the data maintained elsewhere but is required for study time-to-time study and arriving at decisions. The source data can be in any form − ranging from a text

Excel Power Pivot – Useful Resources ”; Previous Next The following resources contain additional information on Excel Power Pivot. Please use them to get more in-depth knowledge on this. Useful Video Courses Excel – A Step by Step Complete Course 56 Lectures 6 hours TELCOMA Global More Detail Excel Makro ve VBA Eğitimi-1 37 Lectures 6.5 hours Volkan Yurtseven More Detail Excel Pivot Tables – Crash Course Most Popular 18 Lectures 47 mins EdSolver Learning More Detail Learn MS Excel, Data Analysis, and Financial Analysis 50 Lectures 3 hours Tayana Grubisic More Detail Excel Analytics – Data Analysis with Pivot-Tables and Charts 47 Lectures 2.5 hours Blink Data More Detail Learn microsoft excel course for data analysis Zero to hero 50 Lectures 2 hours ADITYA More Detail Print Page Previous Next Advertisements ”;

Excel Power Pivot – Hierarchies ”; Previous Next A hierarchy in Data Model is a list of nested columns in a data table that are considered as a single item when used in a Power PivotTable. For example, if you have the columns − Country, State, City in a data table, a hierarchy can be defined to combine the three columns into one field. In the Power PivotTable Fields list, the hierarchy appears as one field. So, you can add just one field to the PivotTable, instead of the three fields in the hierarchy. Further, it enables you to move up or down the nested levels in a meaningful way. Consider the following Data Model for illustrations in this chapter. Creating a Hierarchy You can create Hierarchies in the diagram view of the Data Model. Note that you can create a hierarchy based on a single data table only. Click on the columns − Sport, DisciplineID and Event in the data table Medal in that order. Remember that the order is important to create a meaningful hierarchy. Right-click on the selection. Select Create Hierarchy from the dropdown list. The hierarchy field with the three selected fields as the child levels gets created. Renaming a Hierarchy To rename the hierarchy field, do the following − Right click on Hierarchy1. Select Rename from the dropdown list. Type EventHierarchy. Creating a PivotTable with a Hierarchy in Data Model You can create a Power PivotTable using the hierarchy that you created in the Data Model. Click the PivotTable tab on the Ribbon in the Power Pivot window. Click PivotTable on the Ribbon. The Create PivotTable dialog box appears. Select New Worksheet and click OK. An empty PivotTable is created in a new worksheet. In the PivotTable Fields list, EventHierarchy appears as a field in Medals table. The other fields in the Medals table are collapsed and shown as More Fields. Click on the arrow in front of EventHierarchy. Click on the arrow in front of More Fields. The fields under EventHierarchy will be displayed. All the fields in the Medals table will be displayed under More Fields. As you can observe, the three fields that you added to the hierarchy also appear under More Fields with check boxes. If you do not want them to appear in the PivotTable Fields list under More Fields, you have to hide the columns in the data table – Medals in data view in Power Pivot Window. You can always unhide them whenever you want. Add fields to the PivotTable as follows − Drag EventHierarchy to ROWS area. Drag Medal to ∑ VALUES area. The values of Sport field appear in the PivotTable with a + sign in front of them. The medal count for each sport is displayed. Click on the + sign before Aquatics. The DisciplineID field values under Aquatics will be displayed. Click on the child D22 that appears. The Event field values under D22 will be displayed. As you can observe, medal count is given for the Events, that get summed up at the parent level − DisciplineID, that get further summed up at the parent level − Sport. Creating a Hierarchy based on Multiple Tables Suppose you want to display the Disciplines in the PivotTable rather than DisciplineIDs to make it a more readable and understandable summarization. In order to do this, you need to have the field Discipline in Medals table that as you know is not. Discipline field is in Disciplines data table, but you cannot create a hierarchy with fields from more than one table. But, there is a way to obtain the required field from the other table. As you are aware, the tables − Medals and Disciplines are related. You can add the field Discipline from Disciplines table to the Medals table, by creating a column using the relationship with DAX. Click data view in Power Pivot window. Click the Design tab on the Ribbon. Click Add. The column − Add Column on the right side of the table is highlighted. Type = RELATED (Disciplines [Discipline]) in the formula bar. A new column − CalculatedColumn1 is created with the values as Discipline field values in the Disciplines table. Rename the new column thus obtained in the Medals table as Discipline. Next, you have to remove DisciplineID from the Hierarchy and add Discipline, which you will learn in the following sections. Removing a Child Level from a Hierarchy As you can observe, the hierarchy is visible in the diagram view only, and not in the data view. Hence, you can edit a hierarchy in the diagram view only. Click on the diagram view in the Power Pivot window. Right click DisciplineID in EventHierarchy. Select Remove from Hierarchy from the dropdown list. The Confirm dialog box appears. Click Remove from Hierarchy. The field DisciplineID gets deleted from the hierarchy. Remember that you have removed the field from hierarchy, but the source field still exists in the data table. Next, you need to add Discipline field to EventHierarchy. Adding a Child Level to a Hierarchy You can add the field Discipline to the existing hierarchy – EventHierarchy as follows − Click on the field in Medals table. Drag it to the Events field below in the EventHierarchy. The Discipline field gets added to EventHierarchy. As you can observe, the order of the fields in EventHierarchy is Sport–Event–Discipline. But, as you are aware it has to be Sport–Discipline-Event. Hence, you need to change the order of the fields. Changing the Order of a Child Level in a Hierarchy To move the field Discipline to the position after the field Sport, do the following − Right click on the field Discipline in EventHierarchy. Select Move Up from the dropdown list. The order of the fields changes to Sport-Discipline-Event. PivotTable with Changes in Hierarchy To view the changes that you made in EventHierarchy in the PivotTable, you need not create a new PivotTable. You can view them in the existing