Bạn đang xem bài viết Introduction To Power Pivot – Excel Exposure được cập nhật mới nhất ngày 28/05/2022 trên website Hoisinhvienqnam.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất. Cho đến thời điểm hiện tại, bài viết này đã đạt được 31,086 lượt xem.
--- Bài mới hơn ---
Here’s a helpful guest lesson about an incredibly useful Excel add-in called Power Pivot.
Thanks to Nick Williams from Acuity Training for creating this helpful post!!!
Power Pivot is an Excel add-in which can used to perform powerful data analysis and create sophisticated data models. It can handle large volumes of data (millions of rows) from various sources and all of this within a single Excel file.
Power Pivot is basically a SQL Server Analysis Services engine made available using an in-memory process that runs directly within Excel. It is commonly referred to as an Internal Data Model. The most effective way to interact with the Internal Data Model is to use the Power Pivot Ribbon interface.
Once the Power Pivot add-in is installed and available, you can create a Data Model, which is a collection of tables with relationships. Any data you import into Excel or already have in Excel, once added to the data model is available in the Power Pivot window. The Power Pivot Ribbon gives you additional functions over and above the standard Excel Data tab.
The Power Pivot add-in is available in Excel 2010, and is native in Excel 2013 and 2022. However, only the following versions of Excel 2022 support the ‘Power Query’ functionality:
- Excel 2022 – Office 365 ProPlus
- Excel 2022 – Office 365 E3
- Excel 2022 – Office 365 E4 andE5
To give you a feel for where Power Pivot fits in when using Excel for data analysis or visualization, let’s first have a quick look at how Power Pivot fits into the overall Business Intelligence process and how it works with the other BI tools in Excel.
Power Pivot acts as a data model, this means that the first step is to import some data. Unless it is already in your Excel sheet you will need a tool or connector to connect to different types of data sources and fetch your data. This can be a complex subject depending on your data source and is beyond the scope of this article.
After fetching the data, you will probably need to do some cleaning and transformation on it. Both these functions in Excel are carried out by another add-in called Power Query (in Excel 2010 and 2013) / Get & Transform (in Excel 2013).
The final step is creating the Power Pivot data model. This is where we create the relationships between different data tables.
You can create simple measures or Key Performance Indicators (KPIs) in Power Pivot.
Finally one you have all of the required metrics calculated, you can summarize the information in your Power Pivot data model using Pivot Tables and / or Pivot Charts. The combination of multiple Pivot Tables / Pivot Charts with slicers can be used to create a Dashboard
Lets look at how you launch Power Pivot.
To install the Power Pivot add-in:
The green Manage icon launches the Power Pivot window. Calculated Fields and KPIs can be used to create any summarizing calculations. Add to Data Model allows you to add the data table psent in the Excel into Power Pivot. Update All allows you to update all the data connections established with the data model. Settings helps to control other parameters associated with Power Pivot.
Example 1: Adding Data to the Data Model
The first step is to add some data into the data model. Data sources fall into two broad categories. First, it comes via an import from an external data source. Second it is psent in the Excel tables in the current workbook.
For now, let us take an example which shows how to work with Excel tables in the current Excel workbook to Power Pivot. Use the below Excel workbook to follow along:
- Once you have saved the file and opened that, you’ll notice that there are two tabs. One, data tab and the other mapping tab.
- The data tab has the sales info as shown in the screen shot below. The data tab contains detailed data showing sales by country, by product type, by quarter, by sales channel etc. The mapping tab contains that data sub-totaled by quarter, also seen below.
- The mapping tab has a table showing a method of “mapping” or a lookup table that allows you to change from the quarter notation to the year and/or the quarter. This can also be derived using formula in Power Pivot as well!
- First, let’s see how we can add the two tables into the data model. Make sure you have selected the range of the Data table then select the Add to data model button from the Power Pivot Tab:
- You’ll notice a new pop-up window opens. It is the Power Pivot window, it has several options to edit, modify and update the data model
- Go back to the Excel file and Repeat the steps 4 and 5 with the data in the Mapping tab to add that to the data model.
- Please note that for each data added onto the Data Model, a new tab is created in the Power Pivot tab
Now that we have two datasets added to the data model. Let’s look at how we can create a relationship between the two.
- The common field between the two datasets will be used to create the relationship between them
- In this example, Quarter from the Data table is the same as QuarterCode from the Mapping table
- This opens a pop-up where you’ll need to select the main table, the lookup table and the corresponding columns
- In the current example, the Data table is our main table and the common column is The Mapping table is our Lookup Table and the Related Lookup Column is QuarterCode. Hence the following selections needs to be made in the pop-up
- It shows the relationship created, as shown below
You can create a relationship in Diagram View once you get more comfortable with this process.
Now we’ve created a very simple data model let’s look at creating some calculated columns. For more on
Calculated Columns in Power Pivot
In this section, you’ll learn how to create calculated columns in Power Pivot.
First make sure that you are in the Mapping tab of the Power Pivot window
- Now, let’s explore how we can derive the year and quarter using just the Quartercode column in the Mapping tab to keep this example very simple.
- In the default view, you can see there is a column as “Add Column” as the Header of a column which doesn’t contain any data.
- Almost all of the formulas in Power Pivot are the same as they are in Excel.
In this example, first we take the first four characters from the QuarterCode using the LEFT function and then use the VALUE function to convert the string of characters into a number. See this video for more details on working with text functions.
- Next we add another column called “Quarter Derived” and use the following formula
Similar to the formula in the “Year Derived” column, this formula takes the last digit of the string in the QuarterCode column and converts it into a number.
--- Bài cũ hơn ---
Cập nhật thông tin chi tiết về Introduction To Power Pivot – Excel Exposure trên website Hoisinhvienqnam.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!