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
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.
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.
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.
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?
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.