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)
