Essential Power Query for Management Consultants

We will start by talking about Excel Tables. Excel Tables will help you save a lot of time in Power Query. Additionally, the output from Power Query will be returned to an Excel file usually in the form of an Excel Table or a Pivot Table. Therefore, it’s crucial to understand how Excel Tables work and what interesting features you can use to present the data. In this section, we will look at the basic characteristics of Excel Tables.

Let’s look at the nature of Excel Tables. We will see how it differs from the cells and ranges that we usually use in Excel. This will help you later use it in practice.

Let's see how you can create Excel Table fast in a few steps

Let's check some additional features that Excel Tables offer

Excel Tables consist of different elements. This enables us to create functions referring only to a specific part of the Excel Table.

Now let’s see how the concept of Excel Tables will impact functions. Since we don’t use cell addresses or ranges, we will have to alter the way we build functions.

Let's create a few functions based on Excel Tables

Every Excel Table has a name. Let's see how you can change it and how it will impact things in Excel

Let's see how you can change the look of the Excel Table

During most consulting projects you will be using the essential features of Power Query. Since we believe in the 80/20 rule we will start by discussing those essential features.

Let’s see what we mean by a Query and how it is used in analyses done in Excel.

For the Power Query to operate properly, it has to first convert the input data set into Proper Tables. Let’s see what we mean by Proper Tables.

Let's try to do a simple query in Power Query

Let's try to do a simple query in Power Query

Let's see how we can edit queries

Let's see how you can copy an existing query in Power Query

You can perform a lot of interesting actions in Power Query. Let’s briefly look at them and how they are organized.

You can perform a lot of interesting actions in Power Query. Let’s briefly look at them and how they are organized.

Let's see how you can add columns if you are dealing with data that are time or date

Let's see how you can add columns if you are dealing with data that are numbers or texts

Let's see how you can create a conditional column. It will be similar to using the IF function in Excel

Let's see how you can create a custom column.

Let's see how you can create a column from examples. Power Query will use AI to guess the relationships and create IF functions.

Let's see how you can duplicate a column and create an index column

Let's look at essential transformations you can do in Power Query

Let's see how you can Upload data when the input data are in the output file

Let's see how you can Upload data that requires transformation to a Proper Table

Let's see how you can Change in the Source

Let's see how you can delete a query

Power Query codes the actions performed as a part of a query as a list of actions. This has certain important implications. Let's see the logic behind this approach and its implications

Let's see how you can edit the steps in Existing query

Let's see how you can edit the steps in Existing query

Hi

It's time to practice. We will calculate the revenues of e-commerce by departments and months. To do that you will have to clean the data. We will use for that Power Query. We will also do the calculations directly in Power Query.

Let's move directly to the next lecture in which I will show you the data that you will need to solve the case study

Take care

Asen

In this lecture, we will solve the previously introduced case study.

In this lecture, we will solve the previously introduced case study.

Imagine that you are working for a DIY (Home Improvement) chain of stores. You just got data from the firm and you have to estimate certain things. Unfortunately, data are not ideal.

In this lecture, we will solve the previously introduced case study.

In this lecture, we will solve the previously introduced case study.

In this lecture, we will solve the previously introduced case study.

Power Query enables you to transform data set that looks like a Pivot Table and is not a Proper Table into a Proper Table in a few seconds. This is done by using the Unpivot option. Let's see the logic behind this.

Let's see how you can unpivot tables in practice

Let’s see in what situations, it’s worth using the Power Query during consulting projects. We will look at the main use cases.

Power Query has some advantages over other ways to play with data. Let’s look at the main benefits.

As we have mentioned, Power Query will be very useful in improving the quality of data. In this section, we will go through a few case studies devoted to cleaning data.

Imagine that you are working for a chain of clinics. They have sent you revenue data. You were asked to change in Power Query data so that it can be presented in the form of a Proper Table.

In this lecture, we will solve the previously introduced case study.

In this lecture, we will solve the previously introduced case study.

In this lecture, we will solve the previously introduced case study.

You got from the customer data on their Revenues, COGS, and Gross Margin. Using Power Query change the data into a Proper Table.

In this lecture, we will solve the previously introduced case study.

In this lecture, we will solve the previously introduced case study.

Imagine that you are working for a chain of convenience stores. They have sent you data on product sales. Clean the data and unify the product names.

In this lecture, we will solve the previously introduced case study.

In this lecture, we will solve the previously introduced case study.

In this lecture, we will solve the previously introduced case study.

In certain situations, to get to the report you want you will have to merge data from different queries. In this section, we will see how this can be done

Power Query has two ways in which you can combine data from different tables. Let’s see how they differ.

In Power Query, we will merge data in a totally different manner than we do in regular Excel. Let’s remind ourselves first how you were doing different types of mergers in Excel. We have 3 different types of data merging in Excel. Now let’s discuss what will happen if you want to use some rules to connect the 2 tables based on 1 column. Again, we will look at an example

Let's look at how we can merge data in practice in Power Query.

Let's look at how we can merge data in practice in Power Query.

Imagine that you are working for a chicken meat producer. You got data on fuel consumption for their fleet for 4 selected months. Using Power Query merge and analyze the data.

In this lecture, we will solve the previously introduced case study.

In this lecture, we will solve the previously introduced case study.

In this lecture, we will solve the previously introduced case study.

In this lecture, we will solve the previously introduced case study.

In this lecture, we will solve the previously introduced case study.

Power Query has different options when it comes to merging 2 queries. So far we have used 2 of them. Let’s see how we can interpret the rest of the options. Let’s first briefly look at definitions. After that, I will show you what every type of merger entails in practice. We will use an example of a firm that is comparing different types of projects.

Imagine that you are working for a chain of drugstores. You were asked to compare projects planned and executed in the last year.

In this lecture, we will solve the previously introduced case study.

In this lecture, we will solve the previously introduced case study.

In this lecture, we will solve the previously introduced case study.

In this lecture, we will solve the previously introduced case study.

In this lecture, we will solve the previously introduced case study.

In this lecture, we will solve the previously introduced case study.

In many situations, you will have to combine data that have the same structure but are spread over different sheets or files. To achieve this, we will use the Append option in Power Query.

Imagine that you are working for a chain of clinics. You got data on revenues. Every year is in a separate sheet. Combine the data into 1 Proper Table using Power Query.

In this lecture, we will solve the previously introduced case study.

In this lecture, we will solve the previously introduced case study.

Imagine that you are working for a chain of clinics. You got data on revenues. Every year is in a separate file. Combine the data into 1 Proper Table using Power Query.

In this lecture, we will solve the previously introduced case study.

In this lecture, we will solve the previously introduced case study.

In this lecture, we will solve the previously introduced case study.