Learn Excel Power Query to Automate Boring Tasks

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

Channel: Kenji Explains

Views: 177,246

Keywords: power query, what is power query, power query for excel, power query for data cleaning, power query tutorial, power query for beginners, learn power query, master power query, power query in 10 minutes, combine multiple excel files, merge multiple excel files, automate using power query, power query automation, power query tricks, power query tips, query, excel pq, cleaning data with power query, what can power query do, power query for newbies, power query class

Id: Al0viFDqNn4

Length: 8min 38sec (518 seconds)

Published: Sun Apr 09 2023