The previous webinar outlined how a pivot table in Excel can be useful for summarizing and analyzing data. There are certain limitations with the regular pivot in data modeling and building complex business logic. And hence, we require something more powerful to bind multiple data tables together from various data sources without losing the simplicity of pivot table “drag-drop” features.
It is quite surprising that not many Excel users know the feature that has been part of the regular Excel since Office 2013. So, I thought to create awareness around this simple yet powerful feature present in Excel with the weekly webinar series. Following is the first part of the series:
Download sample data file: SuperStore Sales
Introduction & basic concepts
We briefly discussed the history and basic features of a PowerPivot and how it is different from a regular pivot table. This video also highlights the data modeling concepts supported with PowerPivot.
How add a PowerPivot data model?
With a simple dataset containing five related tables, this video walks you through the steps on how to add and build a data model in Excel. We learn how to enable the PowerPivot feature, add tables to the data model, and create relationships.
Adding a DAX measure and data from other sources
DAX is a powerful language introduced with the tabular model of SQL Server Analysis Services. We use DAX to build complex business logic in Power BI, SSAS Tabular models, and PowerPivot. This video shows how to add a measure in the PowerPivot.
We can add data from multiple sources in a PowerPivot data model using the Get Data feature in the Data ribbon. It is quite helpful, as we can connect with numerous sources without importing data to Excel. Since it is not mandatory to load the data table in Excel to analyze, we can analyze large datasets with billion rows without facing the challenges of reaching the rows limit of an Excel sheet and increasing the file size.
Also, see:
In the next webinar, we will talk about introducing DAX into data modeling.
Join the WhatsApp group BI Simplified to ask questions, share best practices, and get notifications for future webinars.