Sign in or start a free trial to avail of this feature.
1. Power Query Overview and Installation
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.
- Allows you to import and transform large datasets, before loading them into Excel or Power Pivot
- Office 365/Excel 2016: All versions are compatible
- Office 365/Excel 2016: Power Query is named Get and Transform and is found on the Data tab
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.