query

How Power Query Will Change the Way You Use Excel



Sharing buttons:

Would you like a free tool to automate your repetitive

or complex tasks?

Think about the last incredible boring work

you had to in Excel.

Or what about the last complicated task

you spent hours researching the right formula for?

While you were wasting a lot of time looking for a solution,

Get & Transform, also known as Power Query

could do it for you within minutes.

It's like someone is there waving at you,

please use me I'm going to do it for you really fast.

and you just ignore it because you're too busy

or you don't even know it's there.

Let's go over what Power Query can do for you.

There are three main benefits.

Number one importing data from different sources.

Power Query allows you to get data from different sources.

For example, from another workbook, a text file,

a folder or from SharePoint.

You can connect to different databases,

the web, online services or even from your Outlook email.

And in all these cases, you can work with big data,

you're not restricted to the number of rows in Excel.

Number two, transforming and cleaning the data

so you can extract meaningful information.

Power Query comes with an incredible data

transformation engine.

You can transform and shape data

by simply clicking a few buttons.

For example, you can compare two datasets

and find the differences between them.

You can split data from one column into multiple columns.

You can extract numbers from text,

you can calculate hours worked,

calculate age, and merge different columns into one column.

Number three, loading your data as a basis for reports.

You can choose to load your data to a table,

a pivot table, or load to the data model.

Now since Power Query and Excels data model work together,

you can also create relationships

and pivot tables based on multiple tables.

And here's a bonus benefit that's not really talked about.

Generally, when you learn something,

the more effort you put in, the more you get out.

Like that concept applies to most things in life.

But it's not the same with learning Power Query.

If you just put in a little effort, just learn a little bit,

you get so much in return.

This is the difference with learning Power Query,

and learning other skills like VBA.

And that's why I created this course,

to give you the tools to really

make a difference at your workplace.

I'm going to take you from Power Query beginner to pro

and don't worry if you've never used

or even heard of Power Query before,

you're going to learn to use it like an expert

right from the start.

But it also made sure that even season's Power Query users

are going to find new tools and exciting features

in the advanced sections,

where we're going to dive in to Power Query's M language.

Still not convinced about Power Query?

Let me give you a concrete example.

Let's imagine you've started a new role at a company,

and your first task is to provide

high level sales reports to management.

This is the first data set that you're given.

So let's just take a look at how big it is.

It's pretty big, you need to create two reports.

Sales by group customer name,

and sales by customer category.

But customer group name and category

aren't in your data file, you only have an ID.

Unfortunately, the way the customer IDs come in,

is in this format.

It's sitting together with the invoice ID,

then it's the customer ID, then it's a transaction ID.

So somehow, you're going to have to extract that information,

and then look up the names and categories

in the customer master.

And that's the other problem.

To download for the customer master file is a text file.

Let's assume we didn't know about Power Query,

how would we solve this?

I'd probably contact the IT department

and ask them to find a way to get me the customer master

as an Excel file and not a text file.

They're going to need to figure out how to get that.

Once I get the Excel version,

I'm going to add the customer master sheet as a separate tab

to my invoice data file.

For the first pivot table report,

I need to get customer group by sales amount.

Now to get that I need the customer name,

to get the customer name, I need the customer ID.

This means I need to figure out

how to extract the customer ID

from the middle of this invoice transaction number.

This is a bit of a difficult task,

so I probably do some research online

to find out which formulas I can use here.

Then I had add a helper column

to find the location of the first dash

another helper column for the location of the second dash.

Next, I'm going to look into how I can use the mid function

to grab the customer ID,

write the VLOOKUP formula to get the customer name.

Unfortunately, I get errors for VLOOKUP.

And after some research, I realized I have leading spaces

in the customer ID, I better put the mid function

inside the trim function.

While that doesn't solve the problem,

it looks like the values are seen

as text instead of numbers.

So let's use the value functions instead.

Okay, that seems to work.

Now we need to figure out how to get the customer group.

Now this is going to need the left function

together with trim and ease error to get it to work.

Finally, let's calculate the sales amount.

That's a lot of thinking, trying, researching and testing.

In the meanwhile, my spreadsheet is getting slower as well.

But whatever, I'm finally ready for the first pivot table.

Let's insert that and select the fields I need.

Update the formatting and sorting and I'm finally done.

Saving the workbook may take some time,

but I deserve to go for a coffee now anyway.

But wait a second, wasn't there a second pivot table?

Damn, there was.

Okay, here it needs the customer category.

This means another VLOOKUP to get the category.

But now, I need to expand my pivot source

to include the new column.

Let's create a second pivot table.

(gentle music)

Put in the fields we need and we're finally done.

That's a lot of work.

Now, let's check out the Power Query way.

Let's keep our reports separate to the source data.

So let's open a new Excel file, go to data,

get data from workbook.

Browse for the file and transform data.

Let's extract the customer ID.

We don't need the other information,

so let's just go to transform, extract,

text between delimiters and type in the delimiter

that's customer ID.

Change it to a whole number.

Let's get total sales, multiply quantity with price,

keep what we need, and create a connection to this report.

Now let's import the master file, which is a text file.

(gentle music)

Let's add a second column for customer group

by removing the information from the brackets.

Let's create a connection.

Now we can merge these and create our pivot table.

The common column is the customer ID column.

Let's just keep the information we need

from the customer file and now load it as a pivot table.

Arrange our fields and we're done.

(gentle music)

Let's set up the second pivot table,

exchange our fields and all tasks are done.

Isn't it amazing how easy this was, and check this out.

Let's go back to the first version

with a complex Excel formulas,

let's check out the size.

It's 41 megabytes.

What's the size of the Power Query version?

Five megabytes.

The old school methods gives me a file

that's eight times bigger.

The size is not the only argument in favor of Power Query.

In fact, for me, the best part

is the simplicity that it comes with.

I don't have to worry about writing complex formulas.

Because even if I'm good with formulas,

I end up spending a good chunk of time

trying to figure out how to get them right.

Plus, with Power Query, I have a connection

to the source files.

Whenever I get updated information,

I just have to refresh my pivot tables,

and my job is done.

And that's only a small part of what Power Query can do.

Your job doesn't have to be boring or complicated.

You don't want to miss out on this.

Come join me in my course, and prepare to be amazed.

(gentle music)