How to use Power Query - Microsoft Excel Tutorial

welcome to Teachers Tech my name is Jamie and  it's great to have you here today I want to show   you how to use power query in Microsoft Excel  now if you've never used power query before   it is a game changer it can make cleaning your  data so much simpler but you can do more than   clean your data you can transform your data you  can add columns and you can combine worksheets   there's so many different things you can do so  this is a beginner's tutorial in power query   of your complete newbie this is where you  can start I'm going to have some data for   you to download and follow along with me so  that you can start feeling confidently using   power query right away let's get started  with this tutorial today on teachers Tech

what is power query inside Microsoft Excel so  this is an editor that allows you to clean your   data so it's easier to work with so let me give  you a little example what we're going to do today   in this tutorial so this is the data that we're  going to be working with in cleaning it and so   some examples what we might want to do is maybe  where it says sales person here is to split it   into two columns rather than using it in just the  one maybe if I look at the date here I don't want   to have this time on the end so I want to be able  to modify that there's a lot more things I can do   in power query I can add more columns now the  nice thing is if I get new data entered in I   can refresh through Power query and it updates  everything so it's a great way to just keep up   to date once you have it all set up now there's  three different steps with power query we're going   to get the data and there's a numerous different  ways that you can get the data I'm going to be   working with a couple different ways I'm going  to show you how to pull data from a website and   then we're going to be working with the where the  worksheets that I have in the document down below   then you're going to transform the data so this  is where you're going to clean the data and then   you're gonna maybe even add to it if you want  more formulas in there or you want more columns   to add different things so that it's easy to work  with and then you hit load and it loads it back   into Excel so those are the three steps you're  going to get the data you're going to transform   the data and then you're going to load it back  let's get started with our first example here   with power query if you want to follow along with  today's activity the link to this worksheet is   down below in the description where you can click  on it and download it and follow along with me so

first of all where do you find power query how  do you get to the power query editor well where   you're going to find it is if you go up top to the  tabs and look for data here and then when we look   down into the ribbon this area right through here  this get and transform data is where we're going   to be getting to the power query editor where  we're going to be getting our data so remember I   said the first step is to get our data so before  we start any editing in the power query editor

let's get some data and I want to show you how  to get data from the web because this is a handy   feature how we can pull tables from a website  and put it into Excel or we can even transform   it with the power query editor so I'm going to go  ahead and just get some data from a website   and I'm just going to flip over to show you what  website I'm going to grab and it's going to be uh   this one right here so this is the uh the top  I'll put the links to this in the uh in one of   the tabs too so you can copy paste it over or  you can search for something else as well but   let's say I want to take this information here you  can see it's in a table format from the web uh top   movies lifetime adjusted with inflation I'm just  gonna copy this over so I'm just gonna go Ctrl C   and I'm going to go back to my Excel now if I go  get data you can see from other sources I have   from the web right here there's also the shortcut  right up here and I'll just click on this one at   this point now all I have to do is just paste  it in so I'm just going to go Ctrl V and hit OK   so what's going to happen now is it's going  to load up it's going to ask me what to load   specifically because it goes through the page  and sees different tables so you can see on this   page it found two different tables if I click on  this first one you can see that there's nothing   there that I don't really want that information  but on table 0 here this is what I would see it   even gives me a web view over here of what I'd  be seeing on it so to make sure that you have   the right thing now I could go hit transform it  would bring it into the editor I don't want to do   that yet I'll show you that how to transform with  the other data that we'll be working with but I'm   going to drop down and go low 2 just to show you  the different ways we'll do it on two different   websites so I'll hit load two and I'm going to  get prompted with another question about where   to import the data so you can see select where  do I want it to go to I wanted to go in the table   a pivot table report pivot chart only create  connection or an existing worksheet or a new   worksheet so you could pick what you want I'm just  keeping these the same and I'm going to hit okay   and now it's making the connection if we look  over to the right we have table zero and as I   hover over this information this pops up in here  you can see I have some I can do some things down   here with delete and edit I want to point out we  have a brand new tab down here called table zero   so if I go over to the queries and connections  I don't want this called table zero anymore if I   go ahead and just rename it so if I right click  and just say top movies because we don't want   things to be named with just random uh random  ones you know we can make our connection just   so it's easier to see we can name this in other  places too in the editor and I'll show you that   later same thing with the tab down below maybe I  probably don't want this as that so I'm just going   to be calling this I'll call this top movies as  well so I better spell everything correctly here   so top movies just like that and you can move  the tab around so I'm going to do this one more   time for you I'm going to go grab some more data  I'm going to go and grab it from this site again   I'll put the link and this is just Super Bowls  list of Super Bowl champions and I'm gonna grab   this link here Ctrl C for copy go back to Excel  and now I'm going to go to my data again and get   data where am I getting it from I'll go from this  one this time from the web so I click it I'm going   to get to my where I paste my URL hit OK and now  this is going to load up with the same but you'll   see on this one there's a lot more tables on here  so I can take a look at the different tables that   they have through here to see what you might  want I'm just going to choose this one right   here and I'm going to go ahead and just hit  load this time and this time it will create a   another tab you can see down below since  that already had a name on it named it   and the same thing with the if I look over at the  queries and connections it says Super Bowl wins   by team because that was already labeled from the  website now from the website you can like so this   is loaded into Excel I can go into the editor as  well I'll show you really quickly what we can do   here but as soon as we bring this in through the  through Power query look at what top we have this   query up here and I can go to edit and this will  open the power query edit and we'll get more into   this uh in the next example with the sales data  that we're working with but if I hit edit this is   where it opens up uh so that you can see there's  some more information in here so we have our power   query editor but I'm not working in this yet this  is where you do your transformation so I'm just   going to hit close and load uh that's the first  step I just want to show you how easy it is to   bring that data in from the web so next what we're  going to be doing though is bringing it in from a   table or a range with the sales data that we have  down below and just to point out those links that   I just took from are here on the tab if you  look for links from the web you can copy paste   them over but we're moving on over to our sales  data to bring this into the power query editor

so let's bring in more data now and we're going  to bring in this range so this is just the range   it's not a table it actually will convert it to  a table as we bring it into Power query so we   do need to select this now so what is it I want  well it's going to be this right through here   so we select the range go back up to data and  this is where we can just click this so mine is   a little bit squished up you just look for that  icon here but this is the bring it from a table   or range right before I do that I want to point  out and I didn't mention before look at all the   different types of data that you could bring in  in this tutorial we're only going to bring in   two types here but I just want to point out  there's so many different ways that you can   bring data in and then transform it in the power  query editor but we're going to be bringing it in   on this time just from this range that's on this  on this sheet so I'm going to just click it and   notice it says create table now out so it's going  to be creating the table my table has headers yes   it does you can see shoe type sales person order  date and sales all we have to do now is hit OK and

it's going to load up the power query editor if  I look at the very top you can see it says table   five power query editor here and right away  before we start doing any changes to this any   transformation I want to give you a little walk  around here let's start over on the left hand   side here and my navigator pane is already open  yours might be closed so you just have to click   this right here and this might be what yours looks  like I'm just going to pop this open expand it and   you can see the different queries that we already  have in the power query editor here if I click on   the what website once all that information is in  here already and you can see how I can switch to   the different queries on it to transform any of  them and as we add more will show up here   now let's move over to our query settings on the  other side on the right hand side and I just want   to point out this applied steps because every  time I make a change you're going to see steps   added here when a steps get gets added they're  going to have this choice to exit to delete it   by just hitting this X as well and if I right  click on this you can see I have more options as   well too but we'll come back to this as we add  more I'll point out to the different steps that   we have happening here okay so uh this is where  where we could change the name we can change your   name in a few different places so if I was going  to change the name maybe I'll turn this into a p   Cube so PQ is going to stand for power query and  I'm just going to say PQ sales data like that so   as I changed it here in the properties it changed  it over here as well PQ sales data I like not   leaving them just as table five so we can see make  sure we're on the right tables if I right click I   could renamed it over here and you can see how I  have those options as well okay let's move up and   take a look at some of the tabs because we'll be  going through the different ones today on not in   all in detail but we'll be touching on a bit of  them so under the Home tab this is where we're   going to be using this one a lot and I just want  to point out this close and load because every   time we need to bring it back to excel remember  this is the third step I talked about earlier   we need to close and load it and then it loads  it back into Excel but I'll be showing that as   we go along so in here we're going to start our  Transformations if you take a quick look at the   rivet here you can see how we can change our data  type we could merge our queries and do different   things under this Home tab in this ribbon but when  we get into more detailed Transformations we have   this transform Tab and we're going to be using  some of the different functions in here like   split column and a few other things to be showing  you what else you can do but in other tutorials a   dive a little bit deeper into the different  a lot more Transformations add column we'll   be using this one we'll talk about how we can add  columns from examples or custom columns to our   data remember it's just not about cleaning our  data we can add more things to this to make it   a stronger report in this all connects once we  have all these steps applied to it we don't   have to redo them even as we add more data to our  original sales data table it will all get updated   when we refresh it and I'll show you how to do  that now just looking at view we have some more   options here you can see how we can go to the  advanced editor as well but I'm not going to be   doing that today so what I'm going to do right now  is go back to home and just click the close and   load and you're going to see how it loads it back  into Excel so I click close And load and look at   the bottom we have PQ sales data I don't have to  change the name here because I changed it before   and when I closed it created the new tab if I  look at my queries connections I can see it over   here as well now I want to point out that we still  have an original sales data here it doesn't change   the original file on it so this will stay the same  until I add more to it this is what at the end of   this tutorial I'm going to add a couple of more  exam samples and then you'll see how power query   can just adapt with the new information and then  put it through all the different queries that it   has so this is our first step here we have our  query set up with our PQ sales data now we're   going to start transforming it let's open up our  power query editor again and we can do this in a

couple different ways so right now we're just in  Excel if I want to go back if I click anywhere   inside of this here this table as soon as I do  that if I look up top I get a couple extra tabs   so I have table design and query so I could go to  query here and then look I have edit so as soon as   I click edit this brings me back into the power  query editor I'm just going to close this one   more time because I want to show you one more way  you can do this so I'm going to hit close and if   I look over at the query and connections over here  at PQ sales data if I just double click on this it   opens it up as well so just a couple different  ways to get to the editor once you've already   loaded it once into Excel so you can go back and  then keep on making transformations to it now one   of the first things we want to transform so I  just want to point out the things that I want   to transform first is if I look at the titles  or the color column headings here I don't want it   to be just one word you can see how it says shoe  type and I actually want to change this from sales   person to sales rep here too so to do this very  simply just double click in here and I'm just   going to be giving it a space here and this one  I'm going to double click in it and I'm going to   just change this to sales rep like that order date  I'm going to change the spacing in it and notice   under each one if I look at the columns so you  can see how it identifies what it is whether it   be text this is going to be a date here and with  sales here I'm going to change it from sales and   because I'm going to do something later this is  going to be total sales right here so now those   are just some changes that we made some really  quick ones if I go now to go and close and load   if I watch what happens over here this will get  updated so now I have those spaces you can see   I have sales rep order dates and total sale so we  made our first transformations to this I'm going   to go ahead and load this back up again just by  this method double clicking now I want to point

out if we go over to our query settings on the  applied steps look at this it says renamed columns   if I wanted to go back a step all I have to do is  exit this and this would go back and put them back   as they were before so easy to go back with the  steps this will remember all the different steps   that we are how we're transforming this data  let's do a couple more things here let's look

at these two columns here we have order date and  total sales I want to change this right here to   be only I don't want it to see how it says 12 am I  don't want it to be that way so what I can do if I   select this column just like this and if we go and  stay under the Home tab and look in the ribbon uh   see how it says date and time and I drop down look  at my different options I can go date time date if   I just click date like this it's now it's modified  it doesn't have the time on it and I can change it   even to something like this to currency so if I go  to this where it says total sales I know this is a   dollar amount and it's just right now decimal  number drop down we can go currency here so I   can go currency and you can see now it's being  identified as that let's close and load this and   see what we have how it's looking in Excel now  close and load and we just wait a moment for it   to get updated so uh you can see now that we have  our order date different might not look different   we could I could format it at dollar signs I'm not  worried about that but it is being recognized as a   currency at that point now all right so those are  the first Transformations that we did let's open   open it back up again I'll go this time we  click in it we go back to our query go to   edit open back up and then let's try a few more  transformations how I want to clean this now is

that I notice I have some extra rows that I don't  want in this data kind of messes it up a bit if I   look at where it says total sales rep here and it  has this after every uh so often for each one I   want that gone and I'm going to show you a couple  different ways that you can remove this from your   list if I go up to the top of any column I have  this where I can do some sorting and filtering   now what I could do is if I go to look at all  the clicks here if I just click off of this and   hit OK it's taken that out now just like that  if we look over to our applied steps I'm going   to go ahead and just hit X here and you can see  it puts it back so I just want to point that out   how easy it is to go back let's try to sort this  a different way or to filter it so what I'm going   to do is this time add a text filter and I want  it with does not begin with so I'm going to I   click this and all I have to do is so it does not  begin with total so I know everything in this row   right here this begins with total total so  I want to keep everything that doesn't have total   audits so I'm just going to hit OK and you can  see it sorts it that way as well so I   just wanted to show you a couple different ways  there that you can sort your data you remember   you can do this to a different one so if I look  at the sales rep this has everybody selected if   I only wanted certain people so if I could  take off people to make the list to make the   list shorter as well but you can go back and I  can click them on again so I wanted to point out   that feature of sorting so that was removing also  removing rows that don't need to be there so if I   go ahead and just hit close now watch the data  get updated watch these rows that come through   here and there we go it's a little bit cleaner  now let's move on to our next transformation

so for the next transformation what I want to show  you how to do is how to split a column so if we   look at the sales rep one I actually want it to be  first name last name in two different columns here   so I'm going to do this in power query editor  and it's super easy to do so I'm going to go up   and make sure you're under the query Tab and edit  or however you want to get back into the editor   so what column is it that I want to split well  it's going to be this sales rep right here so   I'm selecting this column and if I look right  above it so I'm in the Home tab you can see if   I look into the ribbon I have split column here  so I there is the drop down here and I want to   split it by delimiter right here so I'm going  to go ahead and select that the delimiter is   the space or depending on what is breaking up the  first and second name in this so if I drop down   you can see I know sometimes people have a comma  or colon or semicolon all these things but for   us it's really easy it's just the space when do  I want to have this split at each occurrence   of the delimiter so that's all I need to do I hit  OK and look at that we have two different columns   but I don't want to be called one and two so if  we just go back here really quickly let's make an   adjustment to this and we're going to say first  name here and then we'll go into this one and   then we'll put last name just like this so make  those changes let's go ahead and hit close and   load and you're going to see that this data gets  updated now and then it's going to have the two   different columns for it with the first name and  last name just like that and I do want to point   out if I go back to the original sales data the  tab down below nothing's changed in here yet it's   all the same we would have to add something to  this to make any changes to this okay so we're   getting lots of good Transformations uh happening  to our data here let's go back to the editor and

do some more so now what I want to do is add some  new columns that weren't in the data to begin with   and so we're going to base this upon the date here  so let's go back into our query editor open this   up and now what I want to do is it's going to  be based on the order date here so I am going   to select this right here now look up at  the tabs across we have ADD column let's go from   example this time you can see if we drop down we  have from selection here so that's what I want to   have because I selected the order date and what  do I want to have happen well let's say we take   this order date and I just want the month to come  up with so if I start typing January you can see   it's picked it already so if I choose January  here and hit OK it goes through so it made this   column and it's matching it versus the date too  so maybe I want to add another one let's do it   this one more time and I'm going to go again from  the selection here from selection and this time   just start typing day so I'm going to click in  this cell here and I'm going to type day and you   can see I have some options here so I want it to  be just written and I'm going to select Saturday   and hit OK so now it's put we have our months and  it has actual the day name two as well so that's   some simple ways you can add some columns I'm  going to show you some more ways that you can add   columns even putting a formula in here but let's  go back to our home close and load and see what   we have in Excel now and we have those two extra  columns and I can move these columns around in the   power query Editor to as well and I'll show you  how to do that in a bit so next I want to add a

column that will actually calculate a value using  a formula so I'm going to be using a the Builder   the formula Builder inside the power query editor  and it won't be a super complex one so if you've   done formulas before in Excel you should be able  to follow along with this but you can put exactly   what I put and it should work for you as well so  what we're going to do is build a column that will   look at the total sales over here and if it sees  something over five thousand dollars it's going   to give a bonus of 10 percent and if it's not  above five thousand dollars then it's just going   to be zero so they're not going to get any bonus  okay so let's go ahead get back into our editor   and this is going to be adding a column again  so we need to go to add column up top here and   this time it's going to be a custom column so  I'm going to choose custom column and this is   where I'm going to put the custom column formula  right in here but let's change the name of this   group because this is going to be a bonus so  I'm going to call this bonus you can call it   something else if you would like but let's  go ahead and start our formula you can see   the equal sign is already there and so what did I  say I said well if the total sales is going to be   above five thousand dollars so let's just start  with that so total sales I could type this but   what I like to do is just double click or hit  insert on this so choose what you want and hit   insert but before we do that what we want to  do is write if so I'm going to just put if if   and I'm just going to click off this so This goes  away if what if total sales is what greater than   5000 so I'm going to put five thousand what  do I want to have happen so if total cells   is greater than five thousand then so I have  to type the word oops spell it correctly then   I'm going to click off of this so you can see  what we're doing here then total sales I'm going   to put total sales again I'm just double  clicking on it this time then total sales   times so we have to use our times sign here  the star times what well it's 10 percent 0. 1   0 like that so times 10 but we're not  quite done so this is the first part   we have if total sales is greater than  5 000 then total sales times 10 0. 10 else so this is the other case what happens if  it's not above 5000. so I'm going to type else   else it's just zero so I put zero and look down  below it says no syntax errors have been detected   the okay is here if I had an error in here the  ok went show and it would say that I'd have an   error but if we read this one more time you have  total sales is above 5000 then total sales times   10 percent else it's zero so let's go ahead and  just hit okay and now we have this new column   here and again we can change it remember how we  changed it at the beginning to a currency if I   go back to home and then look at the data type  and drop down we can put this back to currency   here we got our dollar sign and that way it if if  you had any of the rounding issues there it will   round it correctly like this take a look at all  the things that we've done in our steps that we   could delete if we wanted to I just want to point  this one more time you can see all the things that   we're doing and every time I close and open again  these stay here so let's go and close this up here   and see what we have here and this will load  up here and we'll get our new column over here so   I just want to point out I'm going to go back  into the editor here and I can see the rounding   is an issue right here so I'm going to go back to  this one and just make a couple changes I'm going

to show you how we can round this so I'm just  going to move over here and if I right click on   this one and if we go to just go to change type  and you can see how we could change it to whole   numbers and everything I'm going to go actually  transform and go round so I'm just going to go to   this and hit round and it's going to ask to  how many zeros and I'm just going to hit OK   so even though we're seeing it over here I'm just  going to make the change here so it's going to   force the round over here so the other thing I do  want to point out we can drag these columns and   I mentioned I can change them before if I wanted  them side by side I can just click and drag this   over and now it's beside total sales and the bonus  is right there so maybe it's easier to see we   close and load uh one more time and then you're  going to be seeing a couple things get updated   here you're going to see the column move here so  we can see they're side by side and then we are   rounded to two decimal places there all right  let's move on to some more Transformations now   so now what I want to do is merge queries and  join tables so I need to go to the categories tab

at the very bottom here and this is hasn't been  put into Power query editor yet this is already a   table so all I'm going to have to do is just click  init and go to data and just like what we did at   the very beginning we're going to get it from  table range right here and this is really all we   have to do uh you can see it loads up here again  I'm going to rename this I just don't want it to   be called that so I am going to rename this I'm  going to call this PQ categories like this or just   PQ uh yeah we'll go category like here's just so  we can see uh the difference between it and it's   we put it through Power query and I'm just going  to go ahead and hit close and load and then at the   very bottom we're going to get a new tab right  there that says PQ category so that's all we're   going to actually do with this one and then we're  going to go back to our PQ sales data so what we   want to be able to do is add a category to this  so you can see I have shoe type here but each of   these shoes are in a category and that comes from  this over here so it's going to come from you can   see the shoe type is here and then the category  is here so I want to be able to add that to a   whole new table by joining them together and it's  going to create a whole new tab so we're going to   go to our PQ sales data so and then we're going  to go up to our query and then edit this and take   a look under the Home tab here if you look across  the ribbon we have merge query so I'm just going   to drop down on this and I want to merge queries  as new so select that and what you're going to get   are a couple different options from the top and  bottom two different selections to   make so two different tables so the first one is  correct we're going to take the PQ sales data and   then drop down on this other one and we're going  to pick PQ category so we have two different ones   but we're going to try to join them but we need  what's going to be the common thing it's going   to be this column and this column here so shoe  type and shoe type and if we look down below this   selection matches 15 of 15 rows from the first  table so we know it's connecting correctly so   this should be a great match and I'm just going  to go ahead and hit OK to this so this is going   to load up and if I move over here all the way I  should have this new category called it's called   PQ category here but the problem is look at it  all says table here and what's happening is I   have to just change it in under my uh sorting  here see how it's selected both shoe type and   category well I don't want this to be shoe type  I already have shoe type I want it just to be   category listed so I hit OK and now I have all  the categories but I probably don't want this at   the end so I'm just going to drag this over uh  to the very beginning because I think that the   category should come first so I have this category  then shoe type now I'm going to change the name   here and I'm just going to call this category and  delete this like this so I could put shoe category   and change it so I have my category now and I  can see that the Oxford's in the dress and then   who sold it from here so let's go ahead and close  this and see what we have and what you're going   to notice is at the very bottom you're going to  get a new tab that gets created called merge one   now this didn't change anything in the PQ sales  data so this is great you could do a lot of table   joining tables and merge queries and it's not  going to affect all the work that you did before   you're creating a whole new tab now you might not  want to call this merge one on this one so even if   I was calling it PQ category you could name change  this to something that you would especially if you   have a lot that you could tell them apart so  just like I've showed you before how to change   the names I'm not going to worry with about this  because I only have the one in this example and   so these are all the different Transformations  that I want to show you in this beginner's   tutorial just to get used to started to show you  how powerful power Query in Excel can be but I do   want to show you one more thing and that's if we  go back to our original sales data we can put more   in here now and refresh it and it's going to come  through on our other on our power queries that   we've already created so I'm back on the original  sales data table and just below here I'm going to

enter in a new record here so this will be I'm  just going to use the type of shoe will be a   derby so we'll type Derby the seller sales person  will be Rachel and I'll put a date in here so I'll   make it in the seventh month here so I'll make  it into July and I'm just going to pick a day   in July it'll be July 2nd and an amount so I'll  put this one above five thousand dollars because   then you can see that the bonus it will still  do the equation that we did and we'll add one   more here so this is going to be a sandal here  and it will be by Joey and we'll put a date in   again here we'll make this in July and this will  be on the 4th and this will be below so we'll just   do a couple like this so we've added a couple new  entries now nothing's changed yet yeah if we click   inside the table and go up to table design up top  I wanted to point this out where it says refresh   if we go drop down and just click refresh all and  then look it's loading up all through these things   we can see Excel working now it's all done let's  go see if we have anything extra now of these in   our PQ sales data so if I click on it and look  at this we have these two added right here that   we just added to our original sales data and  I uploaded when I refreshed it and it even did   the power query here at the formula that we put  in and gave the bonus so you can see how super   powerful this can be because you can just add more  data to your tables and everything updates when   you refresh it you don't have to rebuild this  all again I'll just move over to my merge one   also and you can see the updates are also there  so I hope you like this walkthrough of how to   use power query in Microsoft Excel so this has  been a beginner's tutorial I hope this gets you   started in understanding the power behind power  query and why you want to be using this so thanks   for watching this week on teachers Tech I'll see  you next time with more Tech tips and tutorials