Introduction

Introduction to PowerQuery Course Introduction
5 minutes
Share the link to this page
Copied
  Completed
A brief overview of the course content.

Transcript

Hello, my name is Philip trick and welcome to my course introducing the get and transform tool in Excel. The getting transformed tool was first introduced with Excel 2010, where it was a separate add in called Power Query. Much literature online still refers to the tool is Power Query, and I will occasionally fall into that trap myself, but I'll do my best to correct myself if I do. The kitten Transform tool is the main driver behind excels self service business intelligence engine. The general concept behind getting transform is to allow Excel to interact with different data sets in a database like manner, while simultaneously making the acquisition of data from a variety of data sources easier than it has ever been before. If you work with data regularly, or even semi regularly, knowing how to use this tool can completely transform form your ability to manage and utilize a huge variety of data.

The tool can be found within the Data tab under the get and transform section. This section may seem small, but we'll spend hours learning about the query editor and environment sitting behind these commands. Looking at the buttons, I can tell you right now that only the very first chapter will spend much time at all using these commands. After which we'll be into the real meat of the course. Let me show you why. Under the new query command, and under other sources, if I select blank query, you'll see that I get a whole new window completely different from Excel with its own ribbon and its own set of commands.

This is where most of our course will be spent editing and designing queries. But for this first chapter, we're just going to look how to get the data and create them. With that in mind, why don't we define what a query is exactly? At the broadest level? a query is simply a question asked of some data. Consider a simple request the sun For sells a one through a 10.

This is a query where you've asked the data as to what the some of that underlying information is. You haven't modified the data in any way, but yet you've generated an informational output. The kitten transform queries are structured commands that are run in a very specific order to generate is specific informational outcome, with their design intended to facilitate mixing different data sources together in order to generate more complex results that can be otherwise challenging or even impossible to do with an excel formula structure. For this course, I will cover most of the standard commands what they do and how to use them. As part of this, I will rely strictly on the interface and the supporting wizards. However, this will only scratch the surface of the tool.

The advanced editor can allow all kinds of different and complex features not supported by the wizards, but it comes with its own language to learn as well within chapter one I'm going to discuss connecting to a handful of standard data sets, including access SQL Server, comma separated value files and some other workbooks. With this, we'll use the get and transform tool to create tables that display the outputs of the various data sources within Excel without any alterations or calculations on top of them. In chapter two, I'll introduce some of the more standard commands that are available. This will include removing columns, renaming and reorganizing columns, applying filters, creating sums, counts, and other aggregations, merging data sets, and finally how to do simple data pivots. All of these features will focus on taking a dataset and basically restructuring the data without any significant calculations. For chapter three, we'll move into more complex transformations of the data.

We'll start by discussing some of the data types and conversion of those data types. From there, we'll look at some very simple data transformations such as splitting and formatting text fields, applying basic math commands to numeric fields, and some date transformation commands for handling date and time information. These basic commands will serve as a baseline that I'll use to introduce you to managing the data types and preparing data for use elsewhere by more complex means. In chapter four, we'll use our clean data to build some more custom column calculations. These calculations function very similarly to Excel formulas. However, they have their own unique language and functions and their own approach to interpretation.

We will rely significantly upon the transformations from chapter three in order to prepare our data and introduce how to perform calculations across multiple columns, learn about some of the functions that are available, and show you how to find the formulas you need through the appropriate formula references. We will finally conclude the course in chapter five where we will bring the tools from chapters one through four together to demonstrate some different ways to apply this tool to real world problems. We'll look at some ways to address issues such as creating a singular useful data set from multiple diverse data sets, creating an interactive data table that's linked to pivot charts for live, updating, and finally how to use these queries to catch and handle data errors nearly automatically. Thank you for joining my course. And without any further delay, let's get started.

Sign Up

Share

Share with friends, get 20% off
Invite your friends to LearnDesk learning marketplace. For each purchase they make, you get 20% off (upto $10) on your next purchase.