Mark Moore - Mastering Excel: Power Pack Bundle

Mastering Excel

Power Pack Bundle:

Power Query

Power Pivot

Power View

Power Map

Mark Moore

Mastering Excel

Power Query

Mark Moore

Copyright 2016 by Mark Moore. All rights reserved worldwide. No part of this publication may be replicated, redistributed, or given away in any form without the prior written consent of the author/publisher or the terms relayed to you herein.

Follow Along Workbooks

If you want to work along the exercises in this lesson (I strongly recommend this), please go to my website and download the follow-along workbooks. My website is:

http://markmoorebooks.com/power-pack-bundle/

The follow along workbooks you will get in your email contain files for all the lessons in this bundle . Each lesson has its own .zip file to help you manage all the files.

A bit of clarification on how to get the follow-along workbooks. You will input your name and email address. You will receive a confirmation email. Once you confirm, you will receive a second email with the follow-along workbook.

Why do I do this?

I cant package an Excel file with an eBook; Amazon will not allow it. Also, the only thing I do with your email is send you the workbook and periodically send you updates about new lessons that I am working on.

Introduction

Welcome to another Mastering Excel lesson. If you have completed previous lessons, thanks for sticking around. If you are new, I hope you enjoy the lesson. The lessons are easy going, relaxed, no-nonsense, and easy to understand. I try my best to explain complex topics in a simple and entertaining way. My goal is that you will finish reading each lesson and have immediately-applicable skills you can use at work or home.

This lesson will focus on an almost unknown feature that Microsoft packaged in Excel: Power Query. Power Query is pretty awesome. It gives you several tools that you can use to audit and analyze your workbooks. If you find yourself constantly trying to figure out how other peoples Excel files work or what they changed or anything like that, this will be right up your alley. Its the kind of thing that once you see it, youll wonder how you ever lived without it.

What is Power Query?

Microsoft is taking Excel in a new direction. Microsoft has included in Excel some very powerful tools to help users analyze large volumes of data. This analysis is usually called Business Intelligence (BI). Previously, processing and working with these large data sets was limited to experienced technical professionals who used expensive software packages. Microsoft wants to do away with that. They want to bring BI to the average user. They use the term Self Service Business Intelligence to indicate that users can now perform this analysis without calling in their IT departments or external consultants.

Microsofts toolset is called the Excel BI toolkit. It has several different tools:

• Power Query - Used to import data
• PowerPivot - Used to analyze data
• Power View - Used to create presentations
• Power Map - Used to create geographical presentations

This lesson will start with Power Query. Later lessons will continue with the rest of the Excel BI toolkit.

Power Query

This lesson will focus on how to use Power Query to connect to external data sources and manipulate the data so you can use it in Excel. Once the data is in Excel you can build dashboards from it, use it in a Pivot Table, or load it into the other Excel BI tools to create some spectacular presentations.

I want to mention one minor item that more advanced Excel users might be confused about. What about MSQuery? MSQuery is a tool that also comes with Excel. I have a lesson specifically on MS Query. MS Query connects to a data source and returns the data into Excel. In that respect it functions identically as Power Query.

However, Power Query can do much more. Power Query can connect to multiple data sources, join them together and put them into one spreadsheet. Power Query can also perform joins, exclusions, and manipulate the data before it even gets to Excel.

For example, suppose you routinely connect to your sales database and extract the sales data for the current month. With MS Query you will return the data and then work in Excel to split out the full name files into First, Last, Middle. You also have to write several formulas to fix any errors or missing data. You will repeat this process every month. Of course, the more data cleaning you have to do, the more tedious the task is.

Power Query can do all of this data cleaning and manipulation for you. You can set up your data cleaning process so that it executes before the data gets loaded into Excel.

Power BI

You might hear the term Power BI as you learn about Excel BI Toolkit. Power BI is part of SharePoint Online. With Power BI, you can share all the analysis you have performed in Excel with users across your organization. Users can apply filters to the data so they can see only what they need to see.

Another BI related term you will come across is ETL. ETL is an acronym for E xtract, T ransform and L oad. This is the process of connecting to the data (Extract), shape the data into a more functional form (Transform). It can then be loaded into Excel or another presentation tool like Power View or Power Map (Load).

Data Destination

There are two places into which you can load the Power Query data: Excel or the Excel Data Model. If you load the data into Excel, it will appear as a data table that can be refreshed (it will re-connect to the source and retrieve new data). If the rows do not fit into an Excel worksheet, then you load it into the Data Model and you can use Power Pivot on the data. Power Pivot will be covered in the next lesson.

Installing Power Query

Excel 2016

Power Query comes installed in this version of Excel. Power Query can be found in the Data tab, in the Get & Transform section.

Previous Versions of Excel

Unfortunately, not all previous versions of Excel can run Power Query. You must have one of the following versions:

• Microsoft Office 2010 Professional Plus with Software Assurance
• Microsoft Office 2013 Professional Plus - All features are supported
• Microsoft Office 365 ProPlus - All features are supported
• Microsoft Excel 2013 Standalone version - All features are supported
• All other Office 2013 versions will have most of the Power Query features available. Some data connections will not be supported. I will not be using these connections in this lesson.

Internet Explorer - Your PC must have Internet Explorer 9 or later to use Power Query.

You can download Power Query directly from Microsoft at:

https://www.microsoft.com/en-us/download/details.aspx?id=39379

Note that when you expand the Details section, you must download the correct version for your PC. If you have a 64-bit machine, you need to download the 64-bit version of Power Query. A 32-bit computer needs the 32-bit Power Query version.

To install Power Query:

1. Download the appropriate msi file.
2. Close Excel.
3. Double click the downloaded file to open it.
4. Follow the prompts.

After installing Power Query, you will see a new tab in Excel.

There is a lot of new stuff that I am going to cover. Power Query has a lot of functionality. Its not difficult to understand, theres just a lot of it. This is the Power Query ribbon: