Retrieve, Transform, and Combine Data Using Power Query

This course targets the Business Intelligence (BI) Self Service professional who needs to obtain and shape data from multiple and various sources so that the data can provide useful BI to report viewers. The focus of this course is Power Query.
Course info
Rating
(28)
Level
Intermediate
Updated
Jan 27, 2017
Duration
1h 52m
Table of contents
Description
Course info
Rating
(28)
Level
Intermediate
Updated
Jan 27, 2017
Duration
1h 52m
Description

Information today exists in multiple locations, from a simple text file, to social networks such as Facebook. Retrieving and then combining related information from such disparate data sources in order to provide meaningful insights into the information has historically been a difficult, if not impossible task. In this course, Retrieve, Transform, and Combine Data Using Power Query, you'll see how Power Query provides the cornerstone to Microsoft's PowerBI solution which aims to solve just this problem. First, you'll learn how learn how to retrieve data from various sources, as well as how how to cleanse and shape the data so that you have the data in the format you desire. This is also known as mashing up the data. Next, you'll learn how to combine data from different sources. At this stage, you're able to provide new insights into your data. Finally, you'll learn how to share the queries you've created. By the end this course, you'll know how to use Power Query to access disparate data sources, cleanse and transform the data, and prepare it so that it can be used in super powerful ways to provide new insights into your world of data.

About the author
About the author

Ben is a Microsoft Project, Project Server/Online consultant, and Data Specialist with over 20 years of implementation experience. He has been a Microsoft MVP for nine years, as well as blogging on various project server scenarios, has articles published on the Microsoft Project User Group (MPUG), and is the author of Microsoft Project 2013 Plain & Simple.

More from the author
More courses by Ben Howard
Section Introduction Transcripts
Section Introduction Transcripts

Course Overview
Hi everyone, my name is Ben Howard and welcome to my course called Retrieve, Transform, and Combine Data using Power Query. I'm a principle consultant at Apple Park Limited. Now I first got interested in Power Query several years ago with the introduction of Project Online. It soon became apparent that Power Query provided me with the tools to analyze and visualize the Project Online data using oData, Power Query, Power BI, and Excel and I became an overnight convert to the world of self service BI or business intelligence. And in truth, I haven't looked back since. In this course we're going to look at how to install Power Query in the various versions of Excel and then we'll cover off the following topics. We'll learn how to retrieve data from various sources. We'll learn how to cleanse and shape that data so that we have it in the format that we desire, this is also known as mashing up the data. We'll learn how to combine data from different sources, at this stage we're able to provide new insights into our data. Finally, we'll learn how to share the queries that we've created and finessed. By the end of this course you'll know how to use Power Query to access disparate data sources, cleanse and transform data and prepare it so that it can be used in super powerful ways to provide new insights into your world of data. Before beginning this course you should, of course, be familiar with Excel. I now hope you'll join me on this journey to learn more about Power Query with Retrieve, Transform, and Combine Data using Power Query course at Pluralsight.

Introduction & Concepts
Hello and welcome. My name is Ben Howard and it's a pleasure to be able to produce and present this course which is called Retrieve, Transform, and Combine Data Using Power Query. This is the first module in this course and this module is title Introduction and Concepts. And so of course, we're going to introduce Power Query to you and help you understand the concepts that it has within its technology and also where it fits into the larger power suite of technologies that Microsoft bundle into Excel and beyond. So you might have heard of Power BI. During this course we'll build upon the basic practicalities of installing Power Query and then using it, so that by the end of the course you will be able to retrieve, combine, and therefore successfully and usefully utilize data that exists today in a disparate array of data sources. So those data sources could be your traditional ones such as text files or csv files, Excel files, or even SQL databases, or those data sources could be some of the new data sets out there that we can access such as oData feeds or even data from social media sites such as Facebook. This course will take us on a journey, so let's get started with the first step of that journey by looking at the introduction and the concepts.

Data Retrieval
Hello, my name is Ben Howard and welcome to the second module in this course which is called Data Retrieval. In this module we'll explore Power Query's ability to query and retrieve data from multiple sources and we'll compare Power Query with the incumbent feature within Excel, which is called Get External Data. Where possible, we'll import the same data using both methods, which will allow us to compare and contrast each method for that type of external data. Once we've done that we'll move on to some of the new querying functionality that is only available within Power Query. These demonstrations will really cement your understanding of the pros and cons of both Get External Data and Power Query, though of course whilst I think on, there aren't really any cons to the Power Query functionality. This is going to be a fairly short module, so let's get started. So the agenda is fairly straightforward, we've got a slide that reminds us of a Power Query functionality and then another one comparing Excel's in-built data import capability with Power Query, and then of course we'll jump right into a demo.

Worksheet vs. Excel Data Model
Hello, my name is Ben Howard and welcome to the third module in this course which investigates the difference between an Excel worksheet for storing data and the Excel Data Model or just the Data Model or EDM. At the end of this module you'll be able to make your own decision on where to store your data, depending upon the ultimate goal of your Excel file. However, it must be said that if you're going to be doing any type of BI, and if you're using Power Query why wouldn't you be, and you're using the Power Pivot, Power View type of tools, then the Excel data model is a must use feature. So having said that, let's get started with the agenda. The agenda is fairly simple. We'll go and have a look at the pros and cons of both of those data storage options, that is the Excel worksheet and the data model. We'll have a look at Excel 2010 and how you install and use the data model in 2010 versus 2013 and 2016, which in this instance are the same, and then of course we'll dive into a demo. So we'll just do a very quick demo of installing the Power Pivot add-in for 2010 and we'll bring some data into Power Pivot and the data model in 2010, and then of course, we'll do the same in 2013/2016 as well. Then of course we'll have a summary and wrap everything up.

Data Cleansing
Hello, my name is Ben Howard and welcome to the fourth module in this course which investigates the data cleansing features of Power Query. We need this specific ability to cleanse the data because the source data we work with is rarely in the exact format that we want it. For example, we might need to remove records that are not applicable to our criteria, the data might contain headers and we need to be aware of that, the numerical formats of certain fields might need to be changed as, of course, might the date formats. As anybody who has worked with somebody else's data knows, the list of issues we can have with source data can be quite long. So data cleansing is really the generic term for the set of functions provided by Power Query that allows us to transform the source data into a format that we can use. Sometimes this transformation of the data is also known as data shaping. It should be noted the format of the source data never actually changes, what Power Query allows us to do is change the way the data is presented to us within Excel. So let's get started with the agenda then. Like most of my agendas, they're very simple. I'll give an overview of data cleansing and where it sits within the overall Power Query solution. And we'll pick out a few of the key, but by no means only data cleansing and data shaping functions. Once we're done with the slide wear, then of course we'll move onto a demo.

Combining Queries
Hello, my name is Ben Howard and welcome to this, the fifth module in this course where we will investigate how to combine separate queries so that we can bring data from multiple disparate sources into a single repository. We know that single queries are great for retrieving and shaping data from a single data source. But some of the real power of Power Query occurs when we combine these single queries together. Combining queries allows us to join disparate data sources together into a single dataset, which we can then interrogate. So let's get started with the agenda, which like most of my agendas is very simple. We'll briefly remind ourselves where combining queries fits into the overall structure of Power Query and then I'll give an overview of our two options when it comes to combining queries, which are to append queries or to merge them, and obviously we'll review where it is appropriate to use either of the two query types. Once we're done with the slide wear, then of course we'll move on to a demo.

Sharing and Reusing Queries
Hello and welcome, my name is Ben Howard and welcome to this, the sixth module in this course, where we will investigate how to share and reuse the queries we have spent time crafting and finessing. So let's get started with the agenda, which like most of my agendas remains very simple. Firstly, we'll remind ourselves where sharing queries fits into the overall Power Query roadmap within this course and we'll then spend some time discussing our options regarding sharing and reusing queries, specifically within Excel, but we will also discuss a feature of Microsoft Azure called the Azure Data Catalog. Obviously none of my modules would be complete without a few demos to illustrate the features available to us. Let's remind ourselves of the four key tenants of Power Query. We've already covered data retrieval, cleansing, and combining queries, and now we're going to review how to share queries with other users inside our organization.

Summary
Hello, my name is Ben Howard and welcome to this the seventh and last final module in this Power Query course. During the previous six modules we've really only scratched the surface of Power Query and the aim of this module is to provide you with a recap of some of the wonderful and powerful features that you've already seen, as well as to inspire you in your Power Query career. As you know by now, Power Query works under the hood, so to speak, and it seeks no glory in itself, instead it is an enabling tool for reports and data analysis. Because this is a summary module, therefore there are no demos to be done. I will instead show you some examples where people have used Power Query to generate eye catching reports and charts. I'll also provide a slide with the main Microsoft Power Query reference sources. So let's get started with the agenda for this summary module. We'll have a look initially and remind ourselves exactly what is Power Query, I hope you're okay with that by now. We'll have a look at the Personal BI Stack and Power BI to find out where Power Query sits into that stack. We'll remind ourselves of the key Power Query functions of retrieve, cleanse, combine, and then sharing those Power Queries and reusing them. We'll review some example BI reports that have utilized Power Query and finally we'll review some of the Microsoft references and sources.