Welcome

Instructions for screen readers: After you go through the tip sheets in this tutorial, you'll learn how to quickly master chart elements. The
instructions for each sheet start in cell A1, and each subsequent step is in cell A2, A3, and so on.
The instructions will indicate which cells to navigate to for use of a feature, or further reading.
To start, press CTRL+PAGE DOWN.

Get to know Power Query

Need to import or paste data into Excel? Would you also
like to clean it up, and keep it up-to-date? Power Query
is the answer, and this tutorial will show you the ropes.

Let's go
Go back to top by pressing CTRL+HOME. To start the tour, press CTRL+PAGE DOWN.
>
Does this happen to you? You paste data into Excel, but then you have to clean it up. For example, in the data below, there ar
Does this happen to you? You paste data into Excel, but then you have to
First, you might want to split this column into two columns: One for first name, and one for last name...
Youitmight
up.also
Forwantexample,
to remove "12:00in AM"
thefrom
data below,
the last there
column, since are a and
it's repetitive couple things you migh
unnecessary.
To to do...
continue with this tutorial, press CTRL + PAGE DOWN. To go to the previous sheet, press CTRL + PAGE UP.

First, you might want to split this column into

two columns: One for first name, and one for
last name...

Product Sales rep Order date

Beer Anne Cho 1/15/18 12:00 AM
You might also want to remove "12:00 AM"
Plums Tim Anders 1/15/18 12:00 AM
from this column, since it's repetitive and
Pears Jan Kotas 1/20/18 12:00 AM unnecessary.
Apples Sam Ioto 1/20/18 12:00 AM

Previous
Feedback
ple, in the data below, there are a couple things you might want to do...
ut then you have to clean
le things you might want
RL + PAGE UP.

remove "12:00 AM"

e it's repetitive and
sary.

Next
You could clean up the data manually, but this can be time consuming...
YouYou
couldcould
fix this byclean up column
adding a new the data manually,
for the last but
names, and then youthis can
could cut and be
pastetime
the lastconsuming...
names into that new c
...You could also reformat the last column to exclude "12:00 AM," but that's a lot of work…
To continue with this tutorial, press CTRL + PAGE DOWN. To go to the previous sheet, press CTRL + PAGE UP.

You could fix this by adding a new column for the last
names, and then you could cut and paste the last names
into that new column...

Product Sales rep Order date

Beer Anne Cho 1/15/18 12:00 AM
Plums Tim Anders 1/15/18 12:00 AM ...You could also reformat this column to
Pears Jan Kotas 1/20/18 12:00 AM exclude
Apples Sam Ioto 1/20/18 12:00 AM "12:00 AM," but that's a lot of work...

Previous
Feedback
etime
the lastconsuming...
names into that new column…

RL + PAGE UP.

mat this column to

e
s a lot of work...

Next
Forget manually cutting, pasting, and reformatting. A better way to clean up data is to use Power Query.
To Forget manually
continue with cutting,
this tutorial, press pasting,
CTRL + PAGE DOWN. Toand reformatting.
go to the previous sheet, pressACTRL
better way
+ PAGE UP. to clean
data is to use Power Query.

Previous
Feedback
ter way to clean up

Next
What is Power Query? It's a special editor that allows you to clean data so that it's easier to work with.
TheWhat is Power
original data Query?
can be found It's
in cells B11 a special
through D15. editor that allows you to clean data so
Then we used Power Query
easier to work with. to create the cleaned up data table found in cells H11 through K15.
To continue with this tutorial, press CTRL + PAGE DOWN. To go to the previous sheet, press CTRL + PAGE UP.

This is the original ... then we used Power

... and now it's clea
data... Query...

Product Sales rep Order date Product

Beer Anne Cho 1/15/18 12:00 AM Beer
Plums Tim Anders 1/15/18 12:00 AM Plums
Pears Jan Kotas 1/20/18 12:00 AM Pears
Apples Sam Ioto 1/20/18 12:00 AM Apples

Previous
Feedback
ou to clean data so that it's
RL + PAGE UP.

... and now it's cleaned up!

First name Last name Order date

Anne Cho 1/15/2018
Tim Anders 1/15/2018
Jan Kotas 1/20/2018
Sam Ioto 1/20/2018

Next
Another great thing about Power Query: If you add new data that's "dirty," Power Query can clean that up too, with just a clic
Another
Cells great
B7 through D12 thing
contain about
data. There Power
are three columns:Query: If you
Product, Sales add
rep, and new
Order data
date. Each that's
column "dirty,
has various valu
Cells G7 through J12 contain data. There are three columns: Product, First name,
Query can clean that up too, with just a click of a button. Try it out below Last name, and Order date. Each column has
TRY THIS: If you see the Enable Content button at the top of Excel, select it (ALT+F, N). Then type your first and last name into
...Then on the Data tab, click Refresh All (ALT+A, R). You'll see that Power Query added the new row for Kiwis, and it cleaned u
To continue with this tutorial, press CTRL + PAGE DOWN. To go to the previous sheet, press CTRL + PAGE UP.
Product Sales rep Order date Product First name Last name
Bananas Anne Cho 1/15/18 12:00 AM Bananas Anne Cho
Plums Tim Anders 1/15/18 12:00 AM Plums Tim Anders
Pears Jan Kotas 1/20/18 12:00 AM Pears Jan Kotas
Apples Sam Ioto 1/20/18 12:00 AM Apples Sam Ioto
Kiwis 4/8/25 12:00 AM Kiwis

...Then on the Data tab, click Refresh All.

TRY THIS You'll see that Power Query added the
If you see the Enable Content new row for Kiwis, and it cleaned up the
button at the top of Excel, click it. data by splitting your first name into cell
Then type your first and last name H12 and your last name into cell I12.
into cell C12.

Previous
Feedback
an that up too, with just a click of a button. Try it out below!
data
Each that's
column "dirty,"
has various Power
values for each row.
Order date. Each
n. Try it out below!column has various values for each row.
your first and last name into cell C12.
row for Kiwis, and it cleaned up the data by splitting your first name into cell H12 and your last name into cell I12.
+ PAGE UP.
Order date
01/15/18
01/15/18
01/20/18
01/20/18
10/09/23

tab, click Refresh All.

er Query added the
and it cleaned up the
ur first name into cell
ame into cell I12.

Next
Why do they call it Power Query? Between the original data, and the clean data, there's a powerful query that links the two.
Why
Cells do they
B7 through calldata.
D11 contain it There
Power Query?
are three columns:Between
Product, Sales the original
rep, and Order date.data, andhasthe
Each column clea
various valu
Cells G7 through J11 contain data. There are four columns:
data, there's a powerful query that links the two. Product, First name, Last name, and Order date. Each column has v
IMPORTANT DETAIL: Between the original data and the clean data, there's a query. The query contains all of the cleaning step
To continue with this tutorial, press CTRL + PAGE DOWN. To go to the previous sheet, press CTRL + PAGE UP.

Product Sales rep Order date Product First name Last name
Bananas Anne Cho 1/15/18 12:00 AM Bananas Anne Cho
Plums Tim Anders 1/15/18 12:00 AM Plums Tim Anders
Pears Jan Kotas 1/20/18 12:00 AM Pears Jan Kotas
Apples Sam Ioto 1/20/18 12:00 AM Apples Sam Ioto

IMPORTANT DETAIL
Between the original data and the clean data, there's a
query. The query contains all of the cleaning steps, so you
don't have to redo them again if new data is added. All
you have to do is click Refresh, and Power Query cleans it
up automatically.

Previous
Feedback
erful query that links the two.
.data, andhasthe
Each column clean
various values for each row.
Order date. Each column has various values for each row.
ontains all of the cleaning steps, so you don't have to redo them again if new data is added. All you have to do is select Refresh (Alt+A, R),
RL + PAGE UP.

Order date
1/15/2018
1/15/2018
1/20/2018
1/20/2018

Next
is select Refresh (Alt+A, R), and Power Query cleans it up automatically.
There are three main steps to using Power Query: Get Data, Transform, and Load.
To There arethisthree
continue with tutorial, main steps
press CTRL to using
+ PAGE DOWN. Power
To go to Query:
the previous Get
sheet, press Data,
CTRL + PAGE Transform,
UP. a
Load.

Get Data Transform Load

Previous
Feedback
Data,
L + PAGE Transform,
UP. and

Next
The first step is Get Data. This is simply the process of getting your data into Power Query. We'll show you how this works in th
To The
continue first step
with this is Get
tutorial, Data.
press CTRL This
+ PAGE DOWN. isTosimply the process
go to the previous sheet, press of
CTRLgetting
+ PAGE UP. your data
Power Query. We'll show you how this works in the videos coming up.

Get Data Transform Load

Previous
Feedback
 show you how this works in the videos coming up.
Lgetting
+ PAGE UP. your data into
os coming up.

Next
Transform is the second step, where you use Power Query to transform, or, clean the data. We'll walk you through this in the
Transform
EXCEL SPEAK: Why the isword,
the"Transform?"
second Well, step,
you where
can do moreyou use
than just Power
clean the data.Query toortransform,
You can add remove columns, o
fi
To clean
continue with this tutorial, press CTRL + PAGE DOWN. To go to the previous
the data. We'll walk you through this in the upcoming videos as we sheet, press CTRL + PAGE UP.

Get Data Transform Load

EXCEL SPEAK
Why the word, "Transform?" Well, you can do more
clean the data. You can add or remove columns, filte
custom calculations, and a lot more.

Previous
Feedback
 l walk you through this in the upcoming videos as well.
ery
can addtoortransform,
remove columns, or,filter, add custom calculations, and a lot more.
L + PAGE UP.
ming videos as well.

orm?" Well, you can do more than just

n add or remove columns, filter, add
nd a lot more.

Next
And finally, Loading is the last step. This is the act of returning the cleaned-up, transformed data back into Excel.
To And
continuefinally, Loading
with this tutorial, is the
press CTRL + PAGElast
DOWN. step. This
To go to is the
the previous actpress
sheet, ofCTRL
returning
+ PAGE UP. the cleane
up, transformed data back into Excel.

Get Data Transform Load

Previous
Feedback
 back into Excel.
Lturning
+ PAGE UP. the cleaned-

Next
And keep in mind: Power Query isn't just for cleaning up pasted data. You can import and transform data from other sources,
And keep in mind: Power Query isn't just for cleaning up pasted data. You
Cells F9 through I14 contain data. There are four columns: Year, Champions, Runners-up, Third place. Each column has various
import
CHECK and
THIS OUT: transform
In this example we useddata
Powerfrom
Query toother
get data sources, like
from Wikipedia's FIFAa file,
World Cupapage.
database, or oe
You can imagine
To page.
continue with this tutorial, press CTRL + PAGE DOWN. To go to the previous sheet, press CTRL + PAGE UP.

Column1Column2 Column3 Column4

Previous
Feedback
m data from other sources, like a file, a database, or even a web page.
pasted data. You can
e. Each column has various values for each row.
papage.
database, or other
You can imagine even usefula sources
web you might pull data from.

CHECK THIS
OUT
In this example we used
Power Query to get
data from Wikipedia's
FIFA World Cup page.
You can imagine other
useful sources you
might pull data from.

Next
Before we show you how to use Power Query, we should warn you that its window, called the Power Query Editor, is large an
Before we show you how to use Power Query, it's worth noting that its
GOOD TO KNOW: You can't use the Excel window while the Power Query Editor is open. However, once you're done transform
To window, called
continue with this tutorial,the
pressPower Query
CTRL + PAGE Editor,
DOWN. To is large
go to the previous and
sheet, pressmay cover
CTRL + PAGE UP. Excel. Thi
expected.

GOOD TO KNOW
You can't use the Excel w
while the Power Query Ed
open. However, once you
done transforming the da
you can close the editor.
point, you'll able to work
Excel window again.

Previous
Feedback
 ower Query Editor, is large and may cover Excel. This is expected.
her,noting that its
once you're done transforming the data, you can close the editor. At that point, you'll able to work in the Excel window again.
Ly+ PAGE
cover UP. Excel. This is

GOOD TO KNOW
You can't use the Excel window
while the Power Query Editor is
open. However, once you're
done transforming the data,
you can close the editor. At that
point, you'll able to work in the
Excel window again.

Next
Excel window again.
In fact, if you have two monitors, we recommend moving the Power Query Editor window to the second monitor so that you c
In fact, if you have two monitors, we recommend moving the Power Quer
Two monitors side by side with Excel opened on one and Power Query editor on the other.
Editor
If you have twowindow tothethe
monitors, put Excelsecond monitor so that you can view Excel at the sa
window in one…
time
... and forPower
put the reference.
Query Editor in the other.
To continue with this tutorial, press CTRL + PAGE DOWN. To go to the previous sheet, press CTRL + PAGE UP.

... and put the Power Query

If you have two monitors, put
Editor
the Excel window in one...
in the other.

Previous
Feedback
e second monitor so that you can view Excel at the same time for reference.
ng the Power Query
ew Excel at the same

L + PAGE UP.

ower Query
r
her.

Next
Start the videos
Start the videos
1. Take a look at the sheets at the bottom of this workbook. You'll see sheets for Import data from web, Categories, and Sales
2. Where are the videos? They'll appear in a web browser after you click the link in step 4.
3. Keep in mind, there are several videos, so make sure to bookmark the page. That way you can return to them in case you ne
1 the
4. Watch
Take a look at the
Power 2 Where are the 3 Keep in mind, there 4
sheets at Query
the videos. videos? They'll are several videos, so
To continue with this
bottom tutorial, press CTRL + PAGE
of this DOWN.
appear To go to the previous make
in a web sheet, sure
press to
CTRL + PAGE UP.
workbook. You'll see browser after you bookmark the page.
sheets for Import click the link in step That way you can
data from web, 4. return to them in case
Categories, and you need to take a
Sales Data ... You'll break.
work with these
sheets while you
watch the videos.

Previous
Feedback
 m web, Categories, and Sales Data ... You'll work with these sheets while you watch the videos.

n return to them in case you need to take a break.

4 Watch the Power
Query videos.
L + PAGE UP.

Next
Copy this URL:
https://en.wikipedia.org/wiki/FIFA_World_Cup
Product Name Category
Apples Produce
Beer Beverages
Coffee Beverages
Green Tea Beverages
Pears Produce
Plums Produce
txt_ProdNam txt_SalesRep dt_OrderDate dbl_Sales
Beer Anne Hellung-Larsen 1/15/2018 0:00 14,000.36
Plums Anne Hellung-Larsen 1/15/2018 0:00 1,050.42
Total Sales Rep 15,050.78
Pears Jan Kotas 1/20/2018 0:00 3,000.51
Apples Jan Kotas 2/20/2018 0:00 5,300.04
Plums Jan Kotas 2/20/2018 0:00 350.13
Total Sales Rep 8,650.68
Coffee Mariya Sergienko 2/27/2018 0:00 9,200.97
Total Sales Rep 9,200.97
Green Tea Anne Hellung-Larsen 3/10/2018 0:00 13,800.89
Total Sales Rep 13,800.89
Coffee Mariya Sergienko 3/24/2018 0:00 5,980.40
Total Sales Rep 5,980.40
Beer Nancy Freehafer 4/5/2018 0:00 12,180.68
Total Sales Rep 12,180.68
Beer Michael Neipper 4/8/2018 0:00 42,000.42
Total Sales Rep 42,000.42
Apples Mariya Sergienko 5/24/2018 0:00 2,000.39
Plums Nancy Freehafer 5/24/2018 0:00 52.50
Coffee Nancy Freehafer 6/7/2018 0:00 2,300.20
Total Sales Rep 4,353.09
Apples Andrew Cencini 6/23/2018 0:00 15,900.61
Pears Andrew Cencini 6/23/2018 0:00 9,000.24
Total Sales Rep 24,900.85