in this video you learn how to save hours of time using power query and first we'll merge
multiple Excel files into one then we're going to clean up and transform the data using power query
and finally we'll upload it back into Excel where we'll analyze it using pivot tables and best part
is none of this requires any coding yeah so let's get into it yeah let's suppose that we
work as analysts for a beverage company and each week we receive orders from the retailers who've
ordered from us and you can see right here that we have three different retailers and each of
these is going to contain the data set with what they've ordered so our goal is to First
merge this into one Excel file then clean it up and finally analyze it so let's take a look at
the first one looking at the data set you can see that the contact here probably doesn't need the
retailer right next to it so we have that to the side and then for the region and state we should
probably separate these into different columns and also if we have the price per unit and the unit
sold we can probably calculate a column like the revenue and maybe add a few more columns as well
so to do this we're going to close out of this Excel file and just open a brand new one
once that's open we'll go over to data then under get data to the site here we're gonna want from
file and more specifically we want a whole folder so we're gonna go over to from folder here I have
the retailers saved as a separate folder so I'm just going to be able to open that and you can see
here under this preview that we've got the three Excel files that we want to merge into one so
we're gonna go down towards bottom where it says to combine and we want to combine and transform
the data because we want to make some changes there then let's just click over the data and
this is where we'll be able to preview everything so this is looking good I'm just gonna hit on OK
this opens up what's known as a power query editor and if we go over to that First Column The Source
name only click on that drop down you can see that we now have all three Excel files into one
so let's get started transforming this data and first we can remove this column so we're
just going to right click and hit on remove then for the contact we said that we didn't
quite need the retailer part so we just want the name which is Anna in this case and for this what
we can do is head over to the transform tab to the side and then we're gonna go over to extract
specifically we want to extract a text before a delimiter which is going to be the space for
us anything that's after the space we want to remove so we're just going to put the space
there so just one space and we're gonna hit on OK and now you can see that we have Anna and if
we scroll lower down it changes the steward and there's no retailer after that if you want to go
back on any of these steps you can actually just go over to applied steps on the side and if you
hover over and click on the previous one you can see that that goes back if we click on the latest
you'll notice that that changes it to the new one similarly here you'll notice that we have
the order date and the payment date so basically we could calculate the number of days it takes
us for an order to get paid for that we just need to select the payment date column hit the control
key and also select the order date and this time we want to go to add column because we want a new
column that's going to tell us the number of days it takes for payment and then under date over to
the side we're just gonna click on the drop down and go to subtract days awesome wow now you can
see that it takes us 30 days to receive payment sometimes that's higher all the way to 60s and
it's back to 30s over here so maybe these high ones are worth worth Consulting further now we
can rename this column just by double clicking on it and call this something like this to payment
and just hit enter there while we're over here let's also work on creating a revenue column
which is just going to be the price multiplied by the quantity so we've got the price per unit
here and the unit sold which is a quantity so we'll just select both under add column again
we're gonna go over to standard and we want the multiplication that's going to give us the revenue
let's double click on the top here and rename this something like Revenue and just hit enter
there then for the revenue and the price per unit we can probably change this to currency
format so we just gotta click on the button here on these icons and just change it to currency
same thing goes with the price per unit just click on that icon there and change that to currency
finally we've got the region and the state over here which we want to separate into two
separate columns so we're just going to click on it and from here we're gonna go over to transform
and then we want to split the column and we're going to split it specifically by
these two dashes so by a delimiter that's going to be not just one dash but two
and then we just need to hit on OK there and you'll notice that we now have the
region separated and the state to the side so let's call this just region
and then we're gonna call this one over here just the state and hit enter there once we've imported
and transformed the data in power query one thing we can do to visualize it is use power bi which
is the industry-leading business intelligence tool used by 97 of Fortune 500 companies if you
want to learn how to use it you can check out our brand new power bi for business analytics course
in our all-inclusive curriculum we start with data cleaning and transformation using power query then
we get into Data visualization tools followed by docs or data analysis Expressions which is what
you would use to build formulas in power bi then to simulate real work scenarios we'll practice
using two extensive case studies one will focus on building a profit and loss dashboard from scratch
on Nike while the other will focus on visualizing mcdonald's's European restaurant operations
so if you're looking to level up your power bi you can check out the link in the description
below alright back the video now that we've merged and transformed all this data and we like the way
it looks we can go back to excel by going over to home and from here just hit close and load that's
kind of load it up into an Excel file so you can see right now that we have this Excel table and
if we click on that drop down for retailers you'll see that we have all three showing so it's merged
the Excel file correctly and you can see that the formats here all look correct as well to
analyze it we can head over to insert and then just click on pivot table we're okay with this
range and we're simply going to hit on OK and so now we're able to do things like grabbing
all three retailers and putting them on the rows and then we can go ahead and select the revenues
seeing that under values we can even go ahead and split up by beverage brand I could put that
under columns and here we can see what that looks like suppose we're happy with this but our manager
tells us hey I forgot to give you one extra data set is that okay so the problem now is that we've
imported all of these we've changed the formats and everything and yet we have one more data set
so we're gonna have to go back but actually using power query there is a way to work around that
so going back to the computer folder and you can see that right now we have this fourth one
called retailer Walmart now we need to see if we can add it over here all we actually need
to do here is go to pivot table analyze and just hit on refresh all and you'll notice that we now
have the fourth row which is going to be Walmart which is the one that we just imported now the
reason for this is because using power query it's just detecting everything that's inside
of this folder and because we added a fourth file to the folder that's being detected and accounted
for here if we go over to the Retailer's data click on that drop down you'll see that we also
have Walmart in here that's an overview of how power query can save you hours of time if you
want to learn how to automate further check out this video over here or check out this link over
here to take our power bi course hit the like And subscribe and I'll catch you in the next one