1. Power Query Overview and Installation

Overview

Like Power Pivot, Power Query is not available in every version of Excel. Learn which versions are compatible and how to access Power Query in this lesson.

To explore more Kubicle data literacy subjects, please refer to our full library.

Summary

  1. Lesson Goal (00:04)

    The goal of this lesson is to learn what Power Query is, and how to access it in Excel.

  2. Understanding Power Query (00:35)

    Analysts often spend a lot of time importing, cleaning, transforming, and filtering data before they actually analyze it. Power Query provides a dedicated environment for performing these tasks before importing datasets into Excel or Power Pivot. Power Query allows you to create queries, which are used to transform data and load it into Excel.

    There are several advantages to using Power Query to transform data. The first advantage is that the transformation takes place in a dedicated environment, allowing you to separate data transformation from data analysis. The second advantage is that queries can be easily modified or repeated for new data sets.

  3. Accessing Power Query (01:57)

    Power Query is included in all versions of Excel 2016. In Excel 2016, Power Query is known as Get and Transform, and the relevant options are found in the Data tab of the ribbon.

Transcript

In this course, we're going to look at Power Query. Power Query helps users load large or complex data into Excel. In the following lessons, we'll use Power Query to import a data set from a pharmaceutical company spread across various different files. Through this example, we'll see how Power Query can be used to get this data into a format that can be easily analyzed in Excel.

But first, let's answer a more basic question. What is Power Query? It's often said that a data analyst spends most of their time importing, cleaning, transforming, and filtering data and much less time actually analyzing it. If you've worked with data from complex sources such as databases or even webpages, you may have spent long periods of time transforming it into a useful format before doing any analysis in Excel. Power Query simplifies this process. Using Power Query, you can create queries which transform and load data into Excel. The first significant advantage of Power Query is that all of the data transformation takes place before the data is imported. This means that you can do the job of cleaning and transforming data in a dedicated, easy-to-use environment, instead of doing it in the worksheet. The other main advantage is that the query is easily modified or even repeated. If the source data is updated, you don't need to go through all of the steps again. Instead, you simply refresh the query and your data set updates automatically. So how do you access Power Query? If you use any version of Excel 2016, the good news is that Power Query is already available to you. Better still, it's not an add-in, so there's nothing to download or install. In Excel 2016, Power Query is actually known as Get and Transform, and it's found under the Data tab.

Over this course, we'll see how these powerful options are used in practice.

Now that you have access to Power Query, we can begin using it. We'll start with how to connect data in the next lesson.