How to use Power Query - Intermediate Microsoft Excel Tutorial
How to use Power Query - Intermediate Microsoft Excel Tutorial
This intermediate Microsoft Excel tutorial focuses on learning Power Query. In this step-by-step Excel Power Query tutorial, we’ll show you how to use Microsoft Excel’s powerful data transformation tool. Power Query can help you quickly clean, consolidate, and analyze data from multiple sources.
If you’re new to Power Query, this tutorial is perfect for you. We’ll start with an introduction to the Power Query interface and walk you through connecting to various data sources such as Excel files, text files, and databases. We’ll then demonstrate how to use Power Query’s features, such as merging, filtering, and pivoting, to transform and refine your data.
This tutorial provides easy-to-follow instructions, so you can practice using Power Query along with us. By the end of this video, you’ll have a solid understanding of Power Query and how to use it to streamline your data analysis.
Watch this Excel Power Query tutorial for beginners and start taking your data analysis to the next level. Don’t forget to hit the like button and subscribe to our channel for more Excel tutorials!
Power Query Practice File: https://bit.ly/Power_Query_Practice_Data
0:00 Introduction to Power Query in Excel
0:45 What is Power Query?
2:34 Where do you find Power Query?
3:05 How to import data from the web with Power Query
8:40 How to import data from a range or table in Power Query
9:50 Power Query Editor layout
14:25 Make changes to your headers
16:42 Examining the applied steps
17:06 How to convert data types in the Power Query Editor
18:36 Remove unwanted rows (filtering)
20:39 How to split columns in Power Query
22:45 Create a new column
24:33 Create a new column to find a value
28:33 Rounding and moving column
29:39 How to merge queries and join tables in Power Query
34:21 Add new data, then refresh your query
Intermediate Excel Lesson 1: • Intermediate Microsoft Excel Tutorial…
Learn how to use Power Query in Power BI: • How to Transform Excel Data into a St…
Content
0.42 -> welcome to Teachers Tech my name is Jamie and
it's great to have you here today I want to show
5.52 -> you how to use power query in Microsoft Excel
now if you've never used power query before
11.46 -> it is a game changer it can make cleaning your
data so much simpler but you can do more than
17.94 -> clean your data you can transform your data you
can add columns and you can combine worksheets
23.16 -> there's so many different things you can do so
this is a beginner's tutorial in power query
28.8 -> of your complete newbie this is where you
can start I'm going to have some data for
32.88 -> you to download and follow along with me so
that you can start feeling confidently using
37.86 -> power query right away let's get started
with this tutorial today on teachers Tech
46.2 -> what is power query inside Microsoft Excel so
this is an editor that allows you to clean your
54 -> data so it's easier to work with so let me give
you a little example what we're going to do today
59.04 -> in this tutorial so this is the data that we're
going to be working with in cleaning it and so
64.8 -> some examples what we might want to do is maybe
where it says sales person here is to split it
70.38 -> into two columns rather than using it in just the
one maybe if I look at the date here I don't want
76.74 -> 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
83.1 -> in power query I can add more columns now the
nice thing is if I get new data entered in I
89.76 -> can refresh through Power query and it updates
everything so it's a great way to just keep up
96.6 -> to date once you have it all set up now there's
three different steps with power query we're going
102.18 -> to get the data and there's a numerous different
ways that you can get the data I'm going to to be
106.62 -> working with a couple different ways I'm going
to show you how to pull data from a website and
110.94 -> then we're going to be working with the where the
worksheets that I have in the document down below
116.22 -> then you're going to transform the data so this
is where you're going to clean the data and then
122.1 -> you're gonna maybe even add to it if you want
more formulas in there or you want more columns
128.46 -> to add different things so that it's easy to work
with and then you hit load and it loads it back
133.74 -> into Excel so those are the three steps you're
going to get the data you're going to transform
138.72 -> the data and then you're going to load it back
let's get started with our first example here
143.4 -> with power query if you want to follow along with
today's activity the link to this worksheet is
150.42 -> down below in the description where you can click
on it and download it and follow along with me so
155.58 -> first of all where do you find power query how
do you get to the power query editor well where
161.58 -> 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
168.42 -> down into the ribbon this area right through here
this get and transform data is where we're going
174.66 -> to be getting to the power query editor where
we're going to be getting our data so remember I
179.7 -> said the first step is to get our data so before
we start any editing in the power query editor
187.08 -> let's get some data and I want to show you how
to get data from the web because this is a handy
191.64 -> feature how we can pull tables from a website
and put it into Excel or we can even transform
199.02 -> it with the power query editor so I'm going to go
ahead and just get get some data from a website
205.74 -> 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
211.38 -> this one right here so this is the uh the top
I'll put the links to this in the uh in one of
217.38 -> the tabs too so you can copy paste it over or
you can search for something else as well but
221.82 -> let's say I want to take this information here you
can see it's in a table format from the web uh top
227.88 -> movies lifetime adjusted with inflation I'm just
gonna copy this over so I'm just gonna go Ctrl C
234.66 -> and I'm going to go back to my Excel now if I go
get data you can see from other sources I have
243.48 -> from the web right here there's also the shortcut
right up here and I'll just click on this one at
249.78 -> 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
258.06 -> so what's going to happen now is it's going
to load up it's going to ask me what to load
262.98 -> specifically because it goes through the page
and sees different tables so you can see on this
267.72 -> page it found two different tables if I click on
this first one you can see that there's nothing
272.82 -> there that I don't really want that information
but on table 0 here this is what I would see it
278.58 -> even gives me a web view over here of what I'd
be seeing on it so to make sure that you have
283.44 -> the right thing now I could go hit transform it
would bring it into the editor I don't want to do
288.72 -> that yet I'll show you that how to transform with
the other data that we'll be working with but I'm
294.48 -> going to drop down and go low 2 just to show you
the different ways we'll do it on two different
298.92 -> websites so I'll hit load two and I'm going to
get prompted with another question about where
306.36 -> to import the data so you can see select where
do I want it to go to I wanted to go in the table
311.46 -> a pivot table report pivot chart only create
connection or an existing worksheet or a new
317.64 -> worksheet so you could pick what you want I'm just
keeping these the same and I'm going to hit okay
323.58 -> and now it's making the connection if we look
over to the right we have table zero and as I
330.36 -> hover over this information this pops up in here
you can see I have some I can do some things down
336.6 -> here with delete and edit I want to point out we
have a brand new tab down here called table zero
343.56 -> so if I go over to the queries and connections
I don't want this called table zero anymore if I
349.98 -> go ahead and just rename it so if I right click
and just say top movies because we don't want
355.02 -> things to be named with just random uh random
ones you know we can make our connection just
361.62 -> so it's easier to see we can name this in other
places too in the editor and I'll show you that
366 -> later same thing with the tab down below maybe I
probably don't want this as that so I'm just going
370.92 -> to be calling this I'll call this top movies as
well so I better spell everything correctly here
377.52 -> so top movies just like that and you can move
the tab around so I'm going to do this one more
383.52 -> time for you I'm going to go grab some more data
I'm going to go and grab it from this site again
390.12 -> I'll put the link and this is just Super Bowls
list of Super Bowl champions and I'm gonna grab
395.34 -> this link here Ctrl C for copy go back to Excel
and now I'm going to go to my data again and get
403.86 -> 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
410.4 -> 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
417.9 -> see on this one there's a lot more tables on here
so I can take a look at the different tables that
423.18 -> they have through here to see what you might
want I'm just going to choose this one right
427.44 -> here and I'm going to go ahead and just hit
load this time and this time it will create a
435.72 -> another tab you can see down below since
that already had a name on it it named it
440.46 -> and the same thing with the if I look over at the
queries and connections it says Super Bowl wins
445.98 -> by team because that was already labeled from the
website now from the website you can like so this
452.34 -> is loaded into Excel I can go into the editor as
well I'll show you really quickly what we can do
459.6 -> here but as soon as we bring this in through the
through Power query look at what top we have this
465.48 -> query up here and I can go to edit and this will
open the power query edit and we'll get more into
472.08 -> this uh in the next example with the sales data
that we're working with but if I hit edit this is
479.16 -> where it opens up uh so that you can see there's
some more information in here so we have our power
486.18 -> query editor but I'm not working in this yet this
is where you do your transformation so I'm just
491.04 -> going to hit close and load uh that's the first
step I just want to show you how easy it is to
496.08 -> bring that data in from the web so next what we're
going to be doing though is bringing it in from a
502.62 -> table or a range with the sales data that we have
down below and just to point out those links that
509.16 -> I I just took from are here on the tab if you
look for links from the web you can copy paste
513.66 -> them over but we're moving on over to our sales
data to bring this into the power query editor
521.34 -> so let's bring in more data now and we're going
to bring in this range so this is just the range
526.92 -> it's not a table it actually will convert it to
a table as we bring it into Power query so we
533.04 -> do need to select this now so what is it I want
well it's going to be this right through here
538.26 -> so we select the range go back up to data and
this is where we can just click this so mine is
546 -> a little bit squished up you just look for that
icon here but this is the bring it from a table
551.16 -> or range right before I do that I want to point
out and I didn't mention before look at all the
556.26 -> different types of data that you could bring in
in this tutorial we're only going to bring in
561.48 -> two types here but I just want to point out
there's so many different ways that you can
566.58 -> bring data in and then transform it in the power
query editor but we're going to be bringing it in
572.4 -> on this time just from this range that's on this
on this sheet so I'm going to just click it and
579.18 -> notice it says create table now out so it's going
to be creating the table my table has headers yes
584.88 -> it does you can see shoe type sales person order
date and sales all we have to do now is hit OK and
591.18 -> it's going to load up the power query editor if
I look at the very top you can see it says table
596.76 -> five power query editor here and right away
before we start doing any changes to this any
604.62 -> transformation I want to give you a little walk
around here let's start over on the left hand
611.16 -> side here and my navigator pane is already open
yours might be closed so you just have to click
617.64 -> this right here and this might be what yours looks
like I'm just going to pop this open expand it and
623.16 -> you can see the different queries that we already
have in the power query editor here if I click on
628.32 -> the what website once all that information is in
here already and you can see how I can switch to
635.4 -> the different queries on it to transform any of
them and as we add more more will show up here
641.88 -> now let's move over to our query settings on the
other side on the right hand side and I just want
647.82 -> to point out this applied steps because every
time I make a change you're going to see steps
653.04 -> added here when a steps get gets added they're
going to have this choice to exit to delete it
660.54 -> by just hitting this X as well and if I right
click on this you can see I have more options as
666.66 -> well too but we'll come back to this as we we add
more I'll point out to the different steps that
671.34 -> we have happening here okay so uh this is where
where we could change the name we can change your
678 -> name in a few different places so if I was going
to change the name maybe I'll turn this into a p
684.42 -> Cube so PQ is going to stand for power query and
I'm just going to say PQ sales data like that so
690.66 -> as I changed it here in the properties it changed
it over here as well PQ sales data I like not
698.64 -> leaving them just as table five so we can see make
sure we're on the right tables if I right click I
704.28 -> could renamed it over here and you can see how I
have those options as well okay let's move up and
709.32 -> take a look at some of the tabs because we'll be
going through the different ones today on not in
715.2 -> all in detail but we'll be touching on a bit of
them so under the Home tab this is where we're
720 -> going to be using this one a lot and I just want
to point out this close and load because every
724.86 -> time we need to bring it back to excel remember
this is the third step I talked about earlier
730.02 -> we need to close and load it and then it loads
it back into Excel but I'll be showing that as
735.84 -> we go along so in here we're going to start our
Transformations if you take a quick look at the
741.42 -> rivet here you can see how we can change our data
type we could merge our queries and do different
747.24 -> things under this Home tab in this ribbon but when
we get into more detailed Transformations we have
753.42 -> this transform Tab and we're going to be using
some of the the different functions in here like
759 -> split column and a few other things to be showing
you what else you can do but in other tutorials a
767.04 -> dive a little bit deeper into the different
a lot more Transformations add column we'll
772.14 -> be using this one we'll talk about how we can add
columns from examples or custom columns to to our
779.04 -> data remember it's just not about cleaning our
data we can add more things to this to make it
786 -> a stronger report in this all connects once we
have all these steps applied to it it we don't
792 -> have to redo them even as we add more data to our
original sales data table it will all get updated
797.94 -> when we refresh it and I'll show you how to do
that now just looking at view we have some more
803.16 -> options here you can see how we can go to the
advanced editor as well but I'm not going to be
808.08 -> doing that today so what I'm going to do right now
is go back to home and just click the close and
814.02 -> load and you're going to see how it loads it back
into Excel so I click close And load and look at
820.26 -> the bottom we have PQ sales data I don't have to
change the name here because I changed it before
825.42 -> and when I closed it it created the new tab if I
look at my queries connections I can see it over
832.74 -> here as well now I want to point out that we still
have an original sales data here it doesn't change
839.7 -> the original file on it so this will stay the same
until I add more to it this is what at the end of
846.48 -> this tutorial I'm going to add a couple of more
exam samples and then you'll see how power query
850.74 -> can just adapt with the new information and then
put it through all the different queries that it
856.2 -> has so this is our first step here we have our
query set up with our PQ sales data now we're
862.86 -> going to start transforming it let's open up our
power query editor again and we can do this in a
869.28 -> couple different ways so right now we're just in
Excel if I want to go back if I click anywhere
875.58 -> inside of this here this table as soon as I do
that if I look up top I get a couple extra tabs
882.9 -> so I have table design and query so I could go to
query here and then look I have edit so as soon as
889.86 -> I click edit this brings me back into the power
query editor I'm just going to close this one
895.68 -> more time because I want to show you one more way
you can do this so I'm going to hit close and if
899.82 -> I look over at the query and connections over here
at PQ sales data if I just double click on this it
905.64 -> opens it up as well so just a couple different
ways to get to the editor once you've already
911.88 -> loaded it once into Excel so you can go back and
then keep on making transformations to it now one
919.14 -> of the first things we want to transform so I
just want to point out the things that I want
923.64 -> to transform first is if I look look at the titles
or the color column headings here I don't want it
930.36 -> to be just one word you can see how it says shoe
type and I actually want to change this from sales
936.42 -> person to sales rep here too so to do this very
simply just double click in here and I'm just
942.84 -> going to be giving it a space here and this one
I'm going to double click in it and I'm going to
948.9 -> just change this to sales rep like that order date
I'm going to change the spacing in it and notice
957.3 -> under each one if I look at the columns so you
can see how it identifies what it is whether it
962.64 -> be text this is going to be a date here and with
sales here I'm going to change it from sales and
970.38 -> because I'm going to do something later this is
going to be total sales right here so now those
975.72 -> are just some changes that we made some really
quick ones if I go now to go and close and load
981.96 -> if I watch what happens over here this will get
updated so now I have those spaces you can see
989.52 -> I have sales rep order dates and total sale so we
made our first transformations to this I'm going
996.06 -> to go ahead and load this back up again just by
this method double clicking now I want to point
1002.06 -> out if we go over to our query settings on the
applied steps look at this it says renamed columns
1008.78 -> 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
1015.68 -> as they were before so easy to go back with the
steps this will remember all the different steps
1021.44 -> that we are how we're transforming this data
let's do a couple more things here let's look
1028.04 -> at these two columns here we have order date and
total sales I want to change this right here to
1036.14 -> 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
1042.74 -> select this column just like this and if we go and
stay under the Home tab and look in the ribbon uh
1051.2 -> see how it says date and time and I drop down look
at my different options I can go date time date if
1058.22 -> 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
1064.94 -> even to something like this to currency so if I go
to this where it says total sales I know this is a
1069.8 -> dollar amount and it's just right now decimal
number drop down we can go currency here so I
1076.04 -> can go currency and you can see now it's being
identified as that let's close and load this and
1082.46 -> see what we have how it's looking in Excel now
close and load and we just wait a moment for it
1088.22 -> to get updated so uh you can see now that we have
our order date different might not look different
1094.76 -> we could I could format it at dollar signs I'm not
worried about that but it is being recognized as a
1099.74 -> currency at that point now all right so those are
the first Transformations that we did let's open
1105.44 -> open it back up again I'll go this time we
click in it we go back to our query go to
1110.72 -> edit open back up and then let's try a few more
transformations how I want to clean this now is
1118.52 -> 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
1124.1 -> look at where it says total sales rep here and it
has this after every uh so often for each one I
1132.38 -> want that gone and I'm going to show you a couple
different ways that you can remove this from your
1136.88 -> list if I go up to the top of any column I have
this where I can do some sorting and filtering
1144.02 -> now what I could do is if I go to look at all
the clicks here if I just click off of this and
1150.14 -> hit OK it's taken that out now just like that
if we look over to our applied steps I'm going
1157.64 -> to go ahead and just hit X here and you can see
it puts it back so I just want to point that out
1163.1 -> 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
1168.86 -> to do is this time add a text filter and I want
it with does not begin with so I'm going to I
1177.26 -> click this and all I have to do is so it does not
begin with total so I know everything in this row
1184.94 -> right here this begins with total total total so
I want to keep everything that doesn't have total
1191.18 -> audits so I'm just going to hit OK and you can
see it sorts it that way that way as well so I
1197.24 -> just wanted to show you a couple different ways
there that you can sort your data you remember
1201.08 -> you can do this to a different one so if I look
at the sales rep this has everybody selected if
1207.62 -> I only wanted certain people so if I I could
take off people to make the list to make the
1214.4 -> list shorter as well but you can go back and I
can click them on again so I wanted to point out
1219.14 -> that feature of sorting so that was removing also
removing rows that don't need to be there so if I
1226.64 -> go ahead and just hit close now watch the data
get updated watch these rows that come through
1233.24 -> here and there we go it's a little bit cleaner
now let's move on to our next transformation
1240.38 -> so for the next transformation what I want to show
you how to do is how to split a column so if we
1246.08 -> look at the sales rep one I actually want it to be
first name last name in two different columns here
1253.22 -> so I'm going to do this in in power query editor
and it's super easy to do so I'm going to go up
1260 -> and make sure you're under the query Tab and edit
or however you want to get back into the editor
1265.64 -> so what column is it that I want to split well
it's going to be this sales rep right here so
1271.1 -> I'm selecting this column and if I look right
above it so I'm in the Home tab you can see if
1275.9 -> I look into the ribbon I have split column here
so I there is the drop down here and I want to
1283.1 -> split it by delimiter right here so I'm going
to go ahead and select that the delimiter is
1289.82 -> the space or depending on what is breaking up the
first and second name in this so if I drop down
1296.42 -> you can see I know sometimes people have a comma
or colon or semicolon all these things but for
1301.64 -> us it's really easy it's just the space when do
I want to have this split at at each occurrence
1308.18 -> of the delimiter so that's all I need to do I hit
OK and look at that we have two different columns
1315.32 -> but I don't want to be called one and two so if
we just go back here really quickly let's make an
1321.14 -> adjustment to this and we're going to say first
name here and then we'll go into this one and
1328.22 -> then we'll put last name just like this so make
those changes let's go ahead and hit close and
1335.6 -> load and you're going to see that this data gets
updated now and then it's going to have the two
1341.54 -> different columns for it with the first name and
last name just like that and I do want to point
1347.42 -> out if I go back to the original sales data the
tab down below nothing's changed in here yet it's
1354.14 -> all the same we would have to add something to
this to make any changes to this okay so we're
1359.9 -> getting lots of good Transformations uh happening
to our data here let's go back to the editor and
1365.48 -> do some more so now what I want to do is add some
new columns that weren't in the data to begin with
1371 -> and so we're going to base this upon the date here
so let's go back into our query editor open this
1378.98 -> up and now what I want to do is it's going to
be based on the order date here so I am going
1384.86 -> to select select this right here now look up at
the tabs across we have ADD column let's go from
1393.26 -> example this time you can see if we drop down we
have from selection here so that's what I want to
1399.56 -> have because I selected the order date and what
do I want to have happen well let's say we take
1405.14 -> this order date and I just want the month to come
up with so if I start typing January you can see
1411.5 -> it's picked it already so if I choose January
here and hit OK it goes through so it made this
1418.52 -> column and it's matching it versus the date too
so maybe I want to add another one let's do it
1423.92 -> this one more time and I'm going to go again from
the selection here from selection and this time
1433.04 -> just start typing day so I'm going to click in
this cell here and I'm going to type day and you
1438.86 -> can see I have some options here so I want it to
be just written and I'm going to select Saturday
1444.68 -> 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
1451.1 -> some simple ways you can add some columns I'm
going to show you some more ways that you can add
1455.78 -> columns even putting a formula in here but let's
go back to our home close and load and see what
1462.26 -> we have in Excel now and we have those two extra
columns and I can move these columns around in the
1468.56 -> 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
1475.28 -> column that will actually calculate a value using
a formula so I'm going to be using a the Builder
1482.18 -> the formula Builder inside the power query editor
and it won't be a super complex one so if you've
1487.58 -> done formulas before in Excel you should be able
to follow along with this but you can put exactly
1491.66 -> what I put and it should work for you as well so
what we're going to do is build a column that will
1498.44 -> look at the total sales over here and if it sees
something over five thousand dollars it's going
1505.16 -> to give a bonus of 10 percent and if it's not
above five thousand dollars then it's just going
1511.4 -> to be zero so they're not going to get any bonus
okay so let's go ahead get back into our editor
1519.5 -> and this is going to be adding a column again
so we need to go to add column up top here and
1525.8 -> this time it's going to be a custom column so
I'm going to choose custom column and this is
1531.56 -> where I'm going to put the custom column formula
right in here but let's change the name of this
1537.02 -> group because this is going to be a bonus so
I'm going to call this bonus you can call it
1541.16 -> something else if you would like but let's
go ahead and start our formula you can see
1545.78 -> the equal sign is already there and so what did I
say I said well if the total sales is going to be
1552.62 -> above five thousand dollars so let's just start
with that so total sales I could type this but
1558.5 -> what I like to do is just double click or hit
insert on this so choose what you want and hit
1564.86 -> insert but before we do that what we want to
do is write if so I'm going to just put if if
1572.42 -> and I'm just going to click off this so This goes
away if what if total sales is what greater than
1581.84 -> 5000 so I'm going to put five thousand what
do I want to have happen so if total cells
1587.9 -> is greater than five thousand then so I have
to type the word oops spell it correctly then
1593.84 -> I'm going to click off of this so you can see
what we're doing here then total sales I'm going
1598.76 -> to put total sales again I'm just double
clicking on it this time then total sales
1604.64 -> times so we have to use our times sign here
the star times what well it's 10 percent 0.1
1611.72 -> 0 like that so times 10 but we're not
quite done so this is the first part
1615.74 -> we have if total sales is greater than
5 000 then total sales times 10 0.10
1625.28 -> else so this is the other case what happens if
it's not above 5000. so I'm going to type else
1630.44 -> else it's just zero so I put zero and look down
below it says no syntax errors have been detected
1639.32 -> the okay is here if I had an error in here the
ok went show and it would say that I'd have an
1644.54 -> error but if we read this one more time you have
total sales is above 5000 then total sales times
1652.1 -> 10 percent else it's zero so let's go ahead and
just hit okay and now we have this new column
1660.5 -> here and again we can change it remember how we
changed it at the beginning to a currency if I
1665 -> go back to home and then look at the data type
and drop down we can put this back to currency
1671.42 -> here we got our dollar sign and that way it if if
you had any of the rounding issues there it will
1678.02 -> round it correctly like this take a look at all
the things that we've done in our steps that we
1684.02 -> could delete if we wanted to I just want to point
this one more time you can see all the things that
1688.7 -> we're doing and every time I close and open again
these stay here so let's go and close this up here
1694.7 -> and see what we have here and this this will load
up here and we'll get our new column over here so
1702.5 -> I just want to point out I'm going to go back
into the editor here and I can see the rounding
1707.12 -> is an issue right here so I'm going to go back to
this one and just make a couple changes I'm going
1713.24 -> to show you how we can round this so I'm just
going to move over here and if I right click on
1719.24 -> this one and if we go to just go to change type
and you can see how we could change it to whole
1726.26 -> numbers and everything I'm going to go actually
transform and go round so I'm just going to go to
1732.38 -> this and hit round and it's going to ask to
how many zeros and I'm just going to hit OK
1738.86 -> so even though we're seeing it over here I'm just
going to make the change here so it's going to
1743.12 -> force the round over here so the other thing I do
want to point out we can drag these columns and
1748.94 -> I mentioned I can change them before if I wanted
them side by side I can just click and drag this
1754.52 -> over and now it's beside total sales and the bonus
is right there so maybe it's easier to see we
1761.54 -> close and load uh one more time and then you're
going to be seeing a couple things get updated
1767.66 -> here you're going to see the column move here so
we can see they're side by side and then we are
1772.7 -> rounded to two decimal places there all right
let's move on to some more Transformations now
1778.94 -> so now what I want to do is merge queries and
join tables so I need to go to the categories tab
1786.08 -> at the very bottom here and this is hasn't been
put into Power query editor yet this is already a
1793.16 -> 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
1799.16 -> the very beginning we're going to get it from
table range right here and this is really all we
1804.68 -> 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
1810.02 -> be called that so I am going to rename this I'm
going to call this PQ categories like this or just
1814.7 -> PQ uh yeah we'll go category like here's just so
we can see uh the difference between it and it's
1821.42 -> we put it through Power query and I'm just going
to go ahead and hit close and load and then at the
1827.06 -> very bottom we're going to get a new tab right
there that says PQ category so that's all we're
1834.08 -> going to actually do with this one and then we're
going to go back to our PQ sales data so what we
1840.08 -> want to be able to do is add a category to this
so you can see I have shoe type here but each of
1847.88 -> these shoes are in a category and that comes from
this over here so it's going to come from you can
1853.4 -> see the shoe type is here and then the category
is here so I want to be able to add that to a
1859.28 -> whole new table by joining them together and it's
going to create a whole new tab so we're going to
1865.22 -> go to our PQ sales data so and then we're going
to go up to our query and then edit this and take
1872.72 -> a look under the Home tab here if you look across
the ribbon we have merge query so I'm just going
1880.34 -> to drop down on this and I want to merge queries
as new so select that and what you're going to get
1887.42 -> are a couple different options from the top and
bottom two different two different selections to
1892.82 -> make so two different tables so the first one is
correct we're going to take the PQ sales data and
1898.22 -> then drop down on this other one and we're going
to pick PQ category so we have two different ones
1904.34 -> but we're going to try to join them but we need
what's going to be the common thing it's going
1909.08 -> to be this column and this column here so shoe
type and shoe type and if we look down below this
1916.34 -> selection matches 15 of 15 rows from the first
table so we know it's connecting correctly so
1922.1 -> this should be a great match and I'm just going
to go ahead and hit OK to this so this is going
1928.52 -> to load up and if I move over here all the way I
should have this new category called it's called
1935 -> PQ category here but the problem is look at it
all says table here and what's happening is I
1941.18 -> have to just change it in under my uh sorting
here see how it's selected both shoe type and
1947.42 -> category well I don't want this to be shoe type
I already have shoe type I want it just to be
1952.04 -> category listed so I hit OK and now I have all
the categories but I probably don't want this at
1959.48 -> the end so I'm just going to drag this over uh
to the very beginning because I think that the
1965.9 -> category should come first so I have this category
then shoe type now I'm going to change the name
1972.32 -> here and I'm just going to call this category and
delete this like this so I could put shoe category
1981.02 -> and change it so I have my category now and I
can see that the Oxford's in the dress and then
1988.58 -> who sold it from here so let's go ahead and close
this and see what we have and what you're going
1994.52 -> to notice is at the very bottom you're going to
get a new tab that gets created called merge one
2001.42 -> now this didn't change anything in the PQ sales
data so this is great you could do a lot of table
2008.08 -> joining tables and merge queries and it's not
going to affect all the work that you did before
2013.06 -> you're creating a whole new tab now you might not
want to call this merge one on this one so even if
2020.02 -> I was calling it PQ category you could name change
this to something that you would especially if you
2027.04 -> have a lot that you could tell them apart so
just like I've showed you before how to change
2030.34 -> the names I'm not going to worry with about this
because I only have the one in this example and
2035.2 -> so these are all the different Transformations
that I I want to show you in this beginner's
2039.88 -> tutorial just to get used to started to show you
how powerful power Query in Excel can be but I do
2046.96 -> want to show you one more thing and that's if we
go back to our original sales data we can put more
2053.86 -> in here now and refresh it and it's going to come
through on our other on our power queries that
2060.88 -> we've already created so I'm back on the original
sales data table and just below here I'm going to
2067.12 -> enter in a new record here so this will be I'm
just going to use the type of shoe will be a
2074.56 -> derby so we'll type Derby the seller sales person
will be Rachel and I'll put a date in here so I'll
2082.24 -> make it in the seventh month here so I'll make
it into July and I'm just going to pick a day
2088.24 -> in July it'll be July 2nd and an amount so I'll
put this one above five thousand dollars because
2094.48 -> then you can see that the bonus it will still
do the equation that we did and we'll add one
2100.06 -> more here so this is going to be a sandal here
and it will be by Joey and we'll put a date in
2108.82 -> again here we'll make this in July and this will
be on the 4th and this will be below so we'll just
2116.5 -> do a couple like this so we've added a couple new
entries now nothing's changed yet yeah if we click
2122.98 -> inside the table and go up to table design up top
I wanted to point this out where it says refresh
2130.72 -> if we go drop down and just click refresh all and
then look it's loading up all through these things
2137.38 -> we can see Excel working now it's all done let's
go see if we have anything extra now of these in
2143.8 -> our PQ sales data so if I click on it and look
at this we have these two added right here that
2152.2 -> we just added to our original sales data and
I uploaded when I refreshed it and it even did
2158.62 -> the power query here at the formula that we put
in and gave the bonus so you can see how super
2166.84 -> powerful this can be because you can just add more
data to your tables and everything updates when
2174.1 -> you refresh it you don't have to rebuild this
all again I'll just move over to my merge one
2180.16 -> also and you can see the updates are also there
so I hope you like this walkthrough of how to
2188.32 -> use power query in Microsoft Excel so this has
been a beginner's tutorial I hope this gets you
2193.66 -> started in understanding the power behind power
query and why you want to be using this so thanks
2201.52 -> for watching this week on teachers Tech I'll see
you next time with more Tech tips and tutorials
Source: https://www.youtube.com/watch?v=MHIV0bYryiw