Power and Pivot: Get To Know Some Free--and Helpful Excel Tools.

AuthorLenning, Jeff
PositionTech Talk

You've heard the terms "Power BI," "Power Query" and "Power Pivot," but maybe aren't sure what they are. Good news! They are free tools from Microsoft and this column will talk you through them. And, while we're at it, we'll also talk about Pivot Tables and Pivot Charts.

Let's zoom out for a moment for the big picture. Excel is used for many different tasks--analyzing data, calculating journal entries and tracking tasks, among others. But, there's one specific Excel task that's very common: building reports. Specifically, exporting data from some system, and then summarizing it in Excel. The summarized data is a report, and we often present it using a table of numbers or a graph.

Now, imagine that Microsoft created tools that are designed to optimize and automate that process, all the way from importing the source data to preparing the final report (table or graph). And, once you've built the report, it's a one-click refresh next month, and every month thereafter. In a nutshell, that's exactly what these power tools do.

The Players

First, let's understand where each tool fits within the overall process. Power Query retrieves and prepares data from various external sources. Power Pivot organizes multiple data tables and writes the formulas needed in our report. When we want to display a summary report in a table format, we can use a Pivot Table. When we want to display it in a graph, we can use a Pivot Chart. The data flows a bit like Figure 1. The tools can be used in combination, as shown in Figure 1, but they also can be used independently. For example, perhaps you don't need to build a graph and you just need to retrieve and clean source data; no problem, just use Power Query on its own. Here is a bit more about each.

Power Query

Power Query gets and transforms data. Specifically, it retrieves data from various sources--including CSV and Excel files, folders, databases and more. Once connected to a data source, we can apply various transformations which help us clean and prepare the data for use. Examples of transformations include removing unnecessary columns or rows, splitting columns and unpivoting data. Once the data is cleaned and ready, it's time for Power Pivot.

Power Pivot

Power Pivot is what allows us to manage the data model. Data model? Yes, think of it as a place to organize multiple tables and write formulas. Multiple tables? Yes. It's important to realize that Power Query can connect to multiple data sources all in a single...

To continue reading

Request your trial

VLEX uses login cookies to provide you with a better browsing experience. If you click on 'Accept' or continue browsing this site we consider that you accept our cookie policy. ACCEPT