Excel Power Pivot & Power Query For Dummies
Overview
Learn to crunch huge amounts of data with PowerPivot and Power Query
Do you have a ton of data you need to make sense of? Microsoft’s Excel program can handle amazingly large data sets, but you’ll need to get familiar with PowerPivot and Power Query to get started.
And that’s where Dummies comes in. With step-by-step instructions—accompanied by ample screenshots—Excel PowerPivot & Power Query For Dummies will teach you how to save time, simplify your processes, and enhance your data analysis and reporting. Use Power Query to discover, connect to, and import your organization’s data. Then use PowerPivot to model it in Excel. You’ll also learn to:
- Make use of databases to store large amounts of data
- Use custom functions to extend and enhance Power Query
- Add the functionality of formulas to PowerPivot and publish data to SharePoint
If you’re expected to wrangle, interpret, and report on large amounts of data, Excel PowerPivot & Power Query For Dummies gives you the tools you need to get up to speed quickly.
Read More
About The Author
MICHAEL ALEXANDER (MCKINNEY, TX) is a Microsoft Certified Application Developer (MCAD) and author of several books on advanced business analysis with Microsoft Access and Excel. He has more than 16 years of experience consulting and developing Office solutions. Michael has been named a Microsoft MVP for his ongoing contributions to the Excel community.
Sample Chapters
excel power pivot & power query for dummies
CHEAT SHEET
Microsoft Power Query has its own formula language and its own functions. Here are a handful of Power Query functions that will help you better massage and transform your data. These functions should prove to be some of the most useful in terms of data transformation.Useful Microsoft Power Query text functionsThe Microsoft Power Query functions in the table that follows are helpful text functions to know.
View Cheat Sheet
View Cheat Sheet
HAVE THIS BOOK?
View Book Companion Site
Access practice tests
Additional Book Resources
Articles from
the book
Excel
If this is your first exposure to Power Pivot and Power Query, you're probably a bit overwhelmed by all the features and options available. That's normal. No one is going to become a Power BI expert in one day. The journey toward proficiency in DAX and the M Query Language takes time.
The good news is that you don't have to take this journey alone.
Over the past few years, Microsoft has added countless features to Power Query. It has truly become a rich tool set with multiple ways to perform virtually any action you can think of. This growth in functionality has paved the way to a good number of tips and tricks that can help you work more efficiently with your Power Query models.
When you publish Power Pivot reports to the web, you intend to give your audience the best experience possible. A large part of that experience is ensuring that performance is good. The word performance (as it relates to applications and reporting) is typically synonymous with speed — or how quickly an application performs certain actions such as opening within the browser, running queries, or filtering.
When you link Excel tables to Power Pivot, you first need to convert your data to Excel tables, and then you're ready to add them to the Power Pivot data model. In this scenario, you have three data sets in three different worksheets: Customers, InvoiceHeader, and InvoiceDetails. You want to use Power Pivot to analyze the data in the Customers, InvoiceHeader, and InvoiceDetails worksheets.
You can add a new table to Excel's Internal Data Model in one of two ways. The easiest way is to create a pivot table from the new table and then choose the Add This Data to the Internal Data Model option. Excel adds the table to the Internal Data Model and produces a pivot table. After the table has been added, you can open the Manage Relationships dialog box and create the needed relationship.
You can add as many layers of analysis as made possible by the fields in the source data table. Say that you want to show the dollar sales that each market earned by business segment. Because the pivot table already contains the Market and Sales Amount fields, all you have to add is the Business Segment field.
When linking Excel tables to Power Pivot, you add them to the Power Pivot data model. In this scenario, assume that you have added three data sets in three different worksheets: Customers, InvoiceHeader, and InvoiceDetails. You want to use Power Pivot to analyze the data in the Customers, InvoiceHeader, and InvoiceDetails worksheets.
Excel
In certain instances, you may want to create a pivot table from scratch using Excel's existing Internal Data Model as the source data. Here are the steps to do so:
Choose Insert→PivotTable from the Ribbon. The Create PivotTable dialog box opens.
Select the Use an External Data Source option, as shown, and then click the Choose Connection button.
You can use a combination of pivot tables and Excel data connections to directly interact with the Internal Data Model, without the Power Pivot add-in. This is useful if you're using versions of Excel that don't come supplied with the Power Pivot add-in, such as when you're using Microsoft Office, either Home or Small Business edition.
Excel
One of Excel's most attractive features is its flexibility. Each individual cell can contain text, a number, a formula, or practically anything else the customer defines. Indeed, this is one of the fundamental reasons that Excel is an effective tool for data analysis. Customers can use named ranges, formulas, and macros to create an intricate system of interlocking calculations, linked cells, and formatted summaries that work together to create a final analysis.
Excel
Microsoft Power Query has its own formula language and its own functions. Here are a handful of Power Query functions that will help you better massage and transform your data. These functions should prove to be some of the most useful in terms of data transformation.Useful Microsoft Power Query text functionsThe Microsoft Power Query functions in the table that follows are helpful text functions to know.
The mechanism that allows for the publishing of Excel documents to SharePoint as interactive web pages is Excel Services. Excel Services is a broader term to describe these three components:
Excel Calculation Services: Serves as the primary engine of Excel services. This component loads Excel documents, runs calculations on the Excel sheet, and runs the refresh process for any embedded data connection.
Scalability is the ability of an application to develop flexibly to meet growth and complexity requirements. In the context of Excel, scalability refers to Excel's ability to handle ever-increasing volumes of data.Most Excel aficionados are quick to point out that as of Excel 2007, you can place 1,048,576 rows of data into a single Excel worksheet — an overwhelming increase from the limitation of 65,536 rows imposed by previous versions of Excel.
The Power Pivot Ribbon interface is available only when you activate the Power Pivot Add-In. The Power Pivot Add-In does not install with every edition of Office. For example, if you have Office Home Edition, you cannot see or activate the Power Pivot Add-In and therefore cannot have access to the Power Pivot Ribbon interface.
If you understand the basic structure of a pivot table, it's time to try your hand at creating your first pivot table. You can find a sample file to work with at Dummies.com in the workbooks named Chapter 3 Samples.xlsx and Chapter 3 Slicers.xlsx.Click any single cell inside the data source; it's the table you use to feed the pivot table.
Slicers offer a user-friendly interface with which you can filter a Power Pivot pivot table. It's time to create your first slicer. Just follow these steps:Place the cursor anywhere inside the pivot table, and then go up to the Ribbon and click the Analyze tab. There, click the Insert Slicer icon.This step opens the Insert Slicers dialog box.
Excel
In Excel 2016, Power Query isn't an add-in — it's a native feature of Excel, just like charts and pivot tables are native features. If you're working with Excel 2016, you don't have to install any additional components. You'll find Power Query in Excel 2016 hidden on the Data tab, in the Get & Transform group.
Excel
Linked tables in Power Pivot have a distinct advantage over other types of imported data in that they immediately respond to changes in the source data within the workbook. If you change the data in one of the tables in the workbook, the linked table within the Power Pivot data model automatically changes. The real-time interactivity you get with linked tables is especially helpful if you're making frequent changes to your data.
Because Microsoft Access has traditionally been made available with the Microsoft Office suite of applications, Access databases have long been used by organizations to store and manage mission-critical departmental data. Walk into any organization, and you will likely find several Access databases that contain useful data.
Excel
Whether your data lives in Oracle, Dbase, or MySQL, you can load data from virtually any relational database system. As long as you have the appropriate database drivers installed, you have a way to connect Power Pivot to your data.Open the Power Pivot window and click the From Other Sources command button on the Home tab.
SQL Server databases are some of the most commonly used for the storing of enterprise-level data. Most SQL Server databases are managed and maintained by the IT department. To connect to a SQL Server database, you have to work with your IT department to obtain Read access to the database you're trying to pull from.
The text file is a type of flat file used to distribute data. This type of file is commonly output from legacy systems and websites. Excel has always been able to consume text files. With Power Pivot, you can go further and integrate them with other data sources.Open the Power Pivot window and click the From Other Sources command button on the Home tab.
Excel
Besides the data sources that are most important to a majority of Excel analysts, there are a few more data sources that Power Pivot is able to connect to and load data from.Although these data sources are not likely to be used by your average analyst, it's worth dedicating a few lines to each one, if only to know that they exist and are available if ever you should need them:
Microsoft SQL Azure: SQL Azure is a cloud-based relational database service that some companies use as an inexpensive way to gain the benefits of SQL Server without taking on the full cost of hardware, software, and IT staff.
Power Pivot includes an interesting option for loading data straight from the Clipboard — that is to say, pasting data you've copied from some other place. This option is meant to be used as a one-off technique to quickly get useful information into the Power Pivot data model.As you consider this option, keep in mind that there is no real data source.
Excel
If you need to edit or delete a relationship between two tables in your Power Pivot data model, you can do so by following these steps:
Open the Power Pivot window, select the Design tab, and then select the Manage Relationships command.
In the Manage Relationships dialog box, shown here, click the relationship you want to work with and click Edit or Delete.
The first step in using Power Pivot is to fill it with data. One way is to link Excel tables to Power Pivot. In this scenario, you have three data sets in three different worksheets: Customers, InvoiceHeader, and InvoiceDetails. You want to use Power Pivot to analyze the data in the Customers, InvoiceHeader, and InvoiceDetails worksheets.
To take advantage of the functionality afforded by Excel Services, you must have the proper permissions to publish to a SharePoint site that is running Excel Services. To obtain access, speak with your IT department.After you have access to publish to SharePoint, follow these steps:
Click the File tab on the Excel Ribbon, choose Save As→Other Web Locations, and then click the Browse button.
Excel
You can manually refresh the data connections within your published Power Pivot report by opening the workbook and selecting the Data drop-down menu. As you can see, you have the option of refreshing a single connection or all connections in the workbook. You can use the Data drop-down menu to manually refresh data connections.
Excel
When you load data from an external data source into Power Pivot, you essentially create a static snapshot of that data source at the time of creation. Power Pivot uses that static snapshot in its Internal Data Model.As time goes by, the external data source may change and grow with newly added records. However, Power Pivot is still using its snapshot, so it can't incorporate any of the changes in your data source until you take another snapshot.
You don't need to be an expert database modeler to use Power Pivot. But it's important to understand relationships. The better you understand how data is stored and managed in databases, the more effectively you'll leverage Power Pivot for reporting.A relationship is the mechanism by which separate tables are related to each other.
Excel
A pivot table is composed of four areas. The data you place in these areas defines both the utility and appearance of the pivot table. Take a moment to understand the function of each of these four areas.Values areaThe values area is the large, rectangular area below and to the right of the column and row headings.
Excel
Pivot tables built on top of Power Pivot or the Internal Data Model come with limitations that could be showstoppers in terms of your reporting needs. Here's a quick rundown of the limitations you should consider before deciding to base your pivot table reporting on Power Pivot or the Internal Data Model:
The Group feature is disabled for Power Pivot–driven pivot tables.
For your end users, the Power Pivot Gallery provides an attractive portal that serves as a one-stop shop for all the reports and dashboards you publish. For you, the Power Pivot Gallery enables better management of your Power Pivot reports by allowing you to schedule nightly refreshes of the data within them.Speak with your SharePoint administrator about your organization's SharePoint instance, and ask that person to consider adding a Power Pivot Gallery to the site.
At its core, Power Pivot is essentially a SQL Server Analysis Services engine made available by way of an in-memory process that runs directly within Excel. Its technical name is the xVelocity analytics engine. However, in Excel, it's referred to as the Internal Data Model.Every Excel workbook contains an Internal Data Model, a single instance of the Power Pivot in-memory engine.
Excel
Using Power Pivot and Power Query together can help you create reporting models that are easy to manage and maintain. A reporting model provides the foundation on which the reporting mechanism is built. When you build a reporting process that imports, transforms, shapes, and aggregates data, you're essentially building a reporting model.
The following table contains Excel date functions that help do things like add months, pull out date parts, and get the number of days within a given time period.
Function
What It Does and How to Use It
Date.AddDays
Increments a given date value by a specified number of days. This example returns a date that is seven days from the date in Column1:
Date.
The Microsoft Power Query functions in the table that follows are helpful text functions to know. You can use them to clean and manipulate textual strings.
Function
What It Does and How to Use It
Text.Contains
Returns true if a specified value is found within a given text field. Use this function with the If function to return a value based on a condition:
if Text.
Slicers allow you to filter your pivot table in a way that's similar to the way Filter fields filter a pivot table. The difference is that slicers offer a user-friendly interface, enabling you to better manage the filter state of your pivot table reports.As useful as Filter fields are, they have always had a couple of drawbacks.
SharePoint is Microsoft's premier collaborative server environment, providing tools for sharing documents and data across various organizations within a company's network. Typically deployed on a company's network as a series of intranet sites, SharePoint lets various departments control their own security, workgroups, documents, and data.
A pivot table is a robust tool that allows you to create an interactive view of your dataset, commonly referred to as a pivot table report. With a pivot table report, you can quickly and easily categorize your data into groups, summarize large amounts of data into meaningful analyses, and interactively perform a wide variety of calculations.
Load More Articles
Related Books
0:00
0:00
https://cdn.prod.website-files.com/6630d85d73068bc09c7c436c/69195ee32d5c606051d9f433_4.%20All%20For%20You.mp3
Frequently Asked Questions
No items found.
