The Power Query Workshop - Part 1

The student will learn how Power Query is self documenting by extracting all the functions available and related information about each function.

In this lecture we build a simple report around the data collected in Part 1

This lecture is to set expectations for this section

We cover some very basic concepts of the M language

We continue our discussion of M basic concepts

We begin this primer with a discussion of the let and in statements

M functions are discussed in this lecture and how to create them

We continue our discussion about M functions and what can be done with them

We take a brief look at variables. Specifically, how to go about naming them.

In this lecture we talk about the importance of data types when working with Power Query and M

Do you think that M can add 0.1 + 0.2 and get the correct result? We show you this isn't the case.

In this lecture we discuss the various options you have for working with dates and times in M.

We discuss how the M language handles NULL values in various situations

In this lecture we discuss a few things about lists and records that are not immediately obvious.

We spend some time discussing tables and how to create them

More discussion on tables and what can be done with them

M has some powerful error handling capabilities and we discuss those in this lecture

Pivoting and unpivoting data is so commonly done when cleaning and shaping data. We cover the basics in this lecture, before you get started with the exercises.

In this exercise you will need to extract information from an encoded column and place the information in their own columns

In this exercise you will be combining multiple tables together to generate the desired output table

In this exercise you will be identifying duplicates in data and then removing them. But it might not be as easy as you think.

In this exercise you will need to extract information from an encoded column and place the information in their own columns

In this exercise you will be required to select a subset of rows from some baseball data based on the values in two fields in the dataset.

Your company has hired an intern to work on Power Query and Power BI projects and he comes to you with a question about combining queries.

Show two ways to keep just three columns in a dataset

Remove all rows from a dataset matching some criteria

In this exercise the student will be working with baseball data and determining the number of players associated with each team and the total number of hits each team had

In a dataset we have a name field in the form of first name, space, last name. We want this changed to last name, comma, space, first name

Your company has hired an intern to work on Power Query and Power BI projects and he comes to you with a question about query join types

Extract the year, month and day from a date field

Add a new column to a dataset that represents the number of days between an order date and ship date

Create three new columns in a dataset that represent the average sales per order date, maximum sales and minimum sales.

Figure out a way to make it easier to visually see if a column of values has the same number of leading characters

For each row in a table create a comma delimited listed of values between a begin and end value

Your company has hired an intern to work on Power Query and Power BI projects and he comes to you with a question about query folding

For each row in a spreadsheet calculate the percentage that value represents of a sales goal

Take a complete set of transformation steps (query steps), extract part of the transformation and store as its own query and then make sure the remaining transformation references the new query.

Import an Excel file containing sales data and determine if the column containing the sales amount contains all valid values. If not, identify the rows containing errors and then remove them.

Given a file that contains names and other values on each row, pull out just the names.

Given a file full of names, remove all duplicates, convert each name to proper case, retrieve the top 1000 names sorted by last name

You will download three Excel files containing sales data. You will create a transformation that read these files, and any other files existing in the folder and combine them into a single table. Then make sure there are no duplicates.

We have a table that, in its current form, is not easy to work with. You will be asked to convert this table into something a little more DAX friendly.

This is very similar to the prior exercise except that we've added one additional line of data which could cause you problems. You will need to figure out why and a solution.

You will have some sales data that needs to be unpivoted. You are asked to unpivot this data using two different methods.

We have a table that, in its current form, is not easy to work with. You will be asked to convert this table into something a little more DAX friendly.

You will be answering a question about decimal numbe types vs. fixed decimal number types and why it matters

You will be developing an M method to delete the last column in any table when you don’t know the name of the column

You will be working with Facebook posts from the Microsoft Press site. A count of certain key words needs to be generated by date.