馃攳
How Power Query Will Change the Way You Use Excel - YouTube
Channel: Leila Gharani
[0]
Would you like a free tool
to automate your repetitive
[3]
or complex tasks?
[4]
Think about the last
incredible boring work
[7]
you had to in Excel.
[9]
Or what about the last complicated task
[12]
you spent hours researching
the right formula for?
[15]
While you were wasting a lot
of time looking for a solution,
[18]
Get & Transform, also
known as Power Query
[21]
could do it for you within minutes.
[23]
It's like someone is there waving at you,
[25]
please use me I'm going to
do it for you really fast.
[29]
and you just ignore it
because you're too busy
[31]
or you don't even know it's there.
[34]
Let's go over what Power
Query can do for you.
[38]
There are three main benefits.
[40]
Number one importing data
from different sources.
[44]
Power Query allows you to get
data from different sources.
[47]
For example, from another
workbook, a text file,
[51]
a folder or from SharePoint.
[54]
You can connect to different databases,
[56]
the web, online services or
even from your Outlook email.
[61]
And in all these cases,
you can work with big data,
[65]
you're not restricted to
the number of rows in Excel.
[68]
Number two, transforming
and cleaning the data
[71]
so you can extract meaningful information.
[74]
Power Query comes with an incredible data
[77]
transformation engine.
[78]
You can transform and shape data
[80]
by simply clicking a few buttons.
[83]
For example, you can compare two datasets
[86]
and find the differences between them.
[88]
You can split data from one
column into multiple columns.
[93]
You can extract numbers from text,
[95]
you can calculate hours worked,
[97]
calculate age, and merge
different columns into one column.
[102]
Number three, loading your
data as a basis for reports.
[106]
You can choose to load
your data to a table,
[108]
a pivot table, or load to the data model.
[112]
Now since Power Query and
Excels data model work together,
[115]
you can also create relationships
[117]
and pivot tables based on multiple tables.
[121]
And here's a bonus benefit
that's not really talked about.
[125]
Generally, when you learn something,
[127]
the more effort you put
in, the more you get out.
[130]
Like that concept applies
to most things in life.
[133]
But it's not the same
with learning Power Query.
[136]
If you just put in a little
effort, just learn a little bit,
[140]
you get so much in return.
[142]
This is the difference
with learning Power Query,
[144]
and learning other skills like VBA.
[147]
And that's why I created this course,
[150]
to give you the tools to really
[151]
make a difference at your workplace.
[154]
I'm going to take you from
Power Query beginner to pro
[157]
and don't worry if you've never used
[160]
or even heard of Power Query before,
[162]
you're going to learn
to use it like an expert
[165]
right from the start.
[167]
But it also made sure that
even season's Power Query users
[170]
are going to find new
tools and exciting features
[173]
in the advanced sections,
[175]
where we're going to dive in
to Power Query's M language.
[178]
Still not convinced about Power Query?
[180]
Let me give you a concrete example.
[184]
Let's imagine you've started
a new role at a company,
[187]
and your first task is to provide
[189]
high level sales reports to management.
[193]
This is the first data
set that you're given.
[195]
So let's just take a
look at how big it is.
[198]
It's pretty big, you need
to create two reports.
[202]
Sales by group customer name,
[205]
and sales by customer category.
[208]
But customer group name and category
[210]
aren't in your data file,
you only have an ID.
[215]
Unfortunately, the way
the customer IDs come in,
[218]
is in this format.
[219]
It's sitting together with the invoice ID,
[221]
then it's the customer ID,
then it's a transaction ID.
[225]
So somehow, you're going to have
to extract that information,
[228]
and then look up the names and categories
[230]
in the customer master.
[232]
And that's the other problem.
[235]
To download for the customer
master file is a text file.
[239]
Let's assume we didn't
know about Power Query,
[243]
how would we solve this?
[245]
I'd probably contact the IT department
[247]
and ask them to find a way
to get me the customer master
[250]
as an Excel file and not a text file.
[252]
They're going to need to
figure out how to get that.
[255]
Once I get the Excel version,
[258]
I'm going to add the customer
master sheet as a separate tab
[261]
to my invoice data file.
[263]
For the first pivot table report,
[265]
I need to get customer
group by sales amount.
[269]
Now to get that I need the customer name,
[271]
to get the customer name,
I need the customer ID.
[275]
This means I need to figure out
[276]
how to extract the customer ID
[279]
from the middle of this
invoice transaction number.
[282]
This is a bit of a difficult task,
[284]
so I probably do some research online
[286]
to find out which formulas I can use here.
[289]
Then I had add a helper column
[291]
to find the location of the first dash
[293]
another helper column for the
location of the second dash.
[297]
Next, I'm going to look into
how I can use the mid function
[301]
to grab the customer ID,
[303]
write the VLOOKUP formula
to get the customer name.
[306]
Unfortunately, I get errors for VLOOKUP.
[309]
And after some research, I
realized I have leading spaces
[312]
in the customer ID, I
better put the mid function
[315]
inside the trim function.
[317]
While that doesn't solve the problem,
[320]
it looks like the values are seen
[321]
as text instead of numbers.
[323]
So let's use the value functions instead.
[329]
Okay, that seems to work.
[331]
Now we need to figure out how
to get the customer group.
[334]
Now this is going to
need the left function
[336]
together with trim and ease
error to get it to work.
[341]
Finally, let's calculate the sales amount.
[344]
That's a lot of thinking,
trying, researching and testing.
[348]
In the meanwhile, my spreadsheet
is getting slower as well.
[351]
But whatever, I'm finally ready
for the first pivot table.
[355]
Let's insert that and
select the fields I need.
[358]
Update the formatting and
sorting and I'm finally done.
[362]
Saving the workbook may take some time,
[364]
but I deserve to go for
a coffee now anyway.
[367]
But wait a second, wasn't
there a second pivot table?
[371]
Damn, there was.
[373]
Okay, here it needs the customer category.
[376]
This means another VLOOKUP
to get the category.
[379]
But now, I need to expand my pivot source
[382]
to include the new column.
[384]
Let's create a second pivot table.
[386]
(gentle music)
[389]
Put in the fields we need
and we're finally done.
[393]
That's a lot of work.
[396]
Now, let's check out the Power Query way.
[398]
Let's keep our reports
separate to the source data.
[401]
So let's open a new
Excel file, go to data,
[405]
get data from workbook.
[407]
Browse for the file and transform data.
[410]
Let's extract the customer ID.
[412]
We don't need the other information,
[413]
so let's just go to transform, extract,
[416]
text between delimiters
and type in the delimiter
[420]
that's customer ID.
[421]
Change it to a whole number.
[423]
Let's get total sales,
multiply quantity with price,
[427]
keep what we need, and create
a connection to this report.
[431]
Now let's import the master
file, which is a text file.
[435]
(gentle music)
[438]
Let's add a second
column for customer group
[441]
by removing the information
from the brackets.
[446]
Let's create a connection.
[448]
Now we can merge these and
create our pivot table.
[451]
The common column is
the customer ID column.
[454]
Let's just keep the information we need
[456]
from the customer file and
now load it as a pivot table.
[461]
Arrange our fields and we're done.
[465]
(gentle music)
[469]
Let's set up the second pivot table,
[471]
exchange our fields
and all tasks are done.
[475]
Isn't it amazing how easy
this was, and check this out.
[479]
Let's go back to the first version
[480]
with a complex Excel formulas,
[483]
let's check out the size.
[484]
It's 41 megabytes.
[487]
What's the size of the
Power Query version?
[490]
Five megabytes.
[492]
The old school methods gives me a file
[494]
that's eight times bigger.
[496]
The size is not the only
argument in favor of Power Query.
[500]
In fact, for me, the best part
[501]
is the simplicity that it comes with.
[504]
I don't have to worry about
writing complex formulas.
[507]
Because even if I'm good with formulas,
[509]
I end up spending a good chunk of time
[511]
trying to figure out
how to get them right.
[514]
Plus, with Power Query,
I have a connection
[516]
to the source files.
[517]
Whenever I get updated information,
[520]
I just have to refresh my pivot tables,
[522]
and my job is done.
[524]
And that's only a small part
of what Power Query can do.
[528]
Your job doesn't have to
be boring or complicated.
[531]
You don't want to miss out on this.
[533]
Come join me in my course,
and prepare to be amazed.
[537]
(gentle music)
Most Recent Videos:
You can go back to the homepage right here: Homepage