Excel 2013 Power BI Tools Part 2 - Getting Started with Power Query

By Andy Brown

http://www.wiseowl.co.uk - How to use Power Query within Excel 2013 to extract, transform and load data, including installing Power Query, creating and managing data transforms and choosing whether to load data into your worksheet, PowerPivot data model or both.

Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, PowerPivot, SQL Server, Reporting Services, Analysis Services, Integration Services, ASP.NET, C#, Visual Basic, VBA macros, SQL and Microsoft Access.

---

Closed Caption:

welcome to his wives our tutorial on
getting started with power query it's
one of a series of tutorials on using
the power business intelligence tools
within Excel 2013 here's what you
learned during the tutorial we'll start
with showing how to install power query
in Excel 2013 then look at how to create
basic queries within power query to
extract transform and load data we look
at different options for loading data
either into an Excel worksheet or the
underlying powerpivot data model will
look at how to create data transforms
within a query to manipulate data and
finally we look at how to edit queries
you've created to delete tasks or rename
them or anything else
so let's get started
unlike all the other Microsoft power
business intelligence applications power
query requires separate installation and
you'll be able to tell me you've done
that because between the view of the
developed tab and any others they'll be
a power query tab just here in fact you
can see it's missing the first thing to
do to find out is whether your support
running power query and you can do that
by choosing the file menu in Excel 2013
going to account and you can see that
i'm running microsoft office 365 ProPlus
and as long as we got the word pro+ or
professional plus or something similar
you should be ok so come out of that and
what I now need to do is go to Google or
some other search engine I believe they
exist and search for the download and
the shortest phrase i can think of to
type in is Microsoft space 3937 nine
because that is the ID number of the
download on the Microsoft website and
you can see it come straight up with it
as number one so i can click on that
link and download microsoft power query
it's a free download and I can choose
from a variety of languages i'm going to
go for english click on the download
button and decide whether I want 32-bit
or 64-bit this depends on the copy of
Excel you're running if i get back to
excel i will now be able to find that
out again I'll go to file from the menu
and again choose account but this time
if i click on the about excel button it
will come up with the top saying what
i'm using 32 or 64 bits and it's 32 bit
as recommended when i installed office
365 so i can choose ok to confirm that
and cancel out to that and I can choose
to download 32-bit now my experience of
this is I needed to click on the next
button and then nothing happened
so what I need to do on my machine and
then maybe a way around this is it says
if your standard doesn't start click
here if I click there suddenly there's a
link here which I can then click on the
download enough power query so click on
that it's going to save the file
it's 11 megabytes but I've got a pretty
fast connection here
and you can see it appeared that in my
windows explorer if I now double click
on that it will say you want to run this
file i do I construes next to go through
the next next stages and you can see
I've actually been telling a bit of a
lie here I've actually already installed
power query which is why i'm not going
to go through the rest of the
installation so for the moment are just
choose cancel to come out of that and
I'll take you back to excel and we'll
see how to enable power query now
there's a good chance that if you
install power query you won't have
anything more to do
power query will appear as an option up
here but if you want to retrieve it or
if anything goes wrong here's how to do
it if you choose file from the menu and
this time if you go to options if you
click on the Add Ins on the left-hand
side you'll see a list of all the atoms
you have enabled in Excel now my problem
is power queries enlisted so what I need
to do is go down to where it says manage
and she is to go to commons which is
what power query is click on the Go
button and tick the box next Microsoft
power query for then choose ok you
should be able to see the power query
comes in this time it's my last timeout
Lee and now i will be able to use power
query
so having set up a pair equerry the next
thing to do is to actually use it to do
that in our new workbook or an existing
workbook click on the pack weary tab and
then she is where you're going to get
your data from there's a huge range of
sources so the most interesting ones on
from other sources you can get
information on facebook i know that
works because I tried it and it was
weird to see my list of likes appearing
in an Excel spreadsheet you can get
information from SAP salesforce.com you
can get from you is your cloud you can
get from access or sequel server or any
other database you can get from Excel or
CSV files in fact i think the range of
data sources is more impressive and I've
seen for any other application
perhaps that's why there's a little
smiley face on the feedback button
we're going to do none of those so we're
going to get information from a website
and to make life a bit easier
what I've done is created a blog on our
wives our . code or UK website so if you
go to that URL go to that web address if
you go to resources and choose blogs
they're categorized on the left hand
side if you choose MS office and within
that if you choose power bi XL 2013
you'll see a list of links and probably
one of the top gives useful links and
downloads for video tutorials so if you
click on that you'll see underneath the
power query heading that there's a link
to wikipedia population data table if
you click on that it will come up with a
website which lists a number of
different tables you can see that
because if you scroll down in particular
there's a table of countries and
dependencies by population and what
we're going to do in Excel is not only
important that but also create a link to
it so that when this list updates the
excel spreadsheet will automatically
update and we'll even edit the data as
it comes in to remove a few
discrepancies so to do this
the first thing to do is to copy the URL
the website address to the clipboard
and then in excel if you go to power
query Gator from web and if you paste in
the URL into this box if you don't
choose ok what power query will do is
look down that website and see if you
can find any tables and it's actually
found three now two of them are terribly
interesting i've no idea what that means
and I don't really know what that table
is either but the first one countries
and dependencies by population looks of
interest so i can double-click on that
opening in power query and what I now do
is show how to extract transform and
load this data so that makes more sense
what I'm going to do is ignore the
problems I've got this data for the
moment and just loaded directly into
Excel you can do that in one of two ways
you can click on close and load need to
choose a default option at the top which
will load it into the default
destination
that's almost certainly your current XR
worksheet alternatively you can give
yourself more choices by choosing the
second option which is what i'm going to
do now this is a bit of a confusing
dialog box it RC two separate questions
the first one is do you want to bring it
into your worksheet if you choose table
that's exactly what will happen if you
choose any create connection what it
will do is create a link to the
underlying data but won't actually
import it so I'm going to leave this
table so I see the information in any
worksheet tacked on the bottom is a
completely separate question which is do
you want to add this data into your
underlying powerpivot data model so i'm
going to do that as well so i can show
you what happens if we now choose load
to load the data it will appear in two
different places the first places as as
an Excel table which you can see has
been given a net sensible name but also
if I now go into power pivot which I can
do by clicking the powerpivot tab and
click on manage you can see it's coming
in as a table in PowerPivot too and i
will be able to link their table to
other tables rename it to be columns all
of the usual powerpivot functionality
what I'm going to do those go straight
back to excel and show now how to change
this load settings which I found very
hard to learn when i was teaching myself
power query it turns out that what you
do is this
if you click on the query in your list
three dots appear it's tempting to think
you want to click on the edit button but
if you click on these and um I'm
promising three dots what you'll be able
to do is change the late two options a
much clearer dialog box appears and you
can now take what you want to do so for
example I've decided I don't want to
refresh the data model i can uncheck
that box
and she's load and what will happen now
is it will load the information into
excel it's displaying a warning saying
that is going to lose the data model
data and what is done is laid their
latest up-to-date data from the
underlying website into exile with Xcel
sheet but if again to powerpivot you can
see it's completely empty because it's
got rid of my old data from that
it's time now to tidy up the data which
we've loaded obvious problems we've got
an extra wrap here which really
shouldn't be there giving the field
headings the population is left-aligned
which suggests is being treated as text
and other number the world population is
also left a line time which will give
the same problem we've got a couple of
extra columns the date and the source
which we really aren't interested in and
don't need and the heading for the
country or dependent territory is a bit
wordy and we could shorten that so what
we'll do is solve all of these problems
and the way to do that is to go back
into power query to do that if you go to
the power query tab and make sure you
click on show pain to bring up your list
of queries on the right-hand side there
saved alongside the workbook but if the
champagne box is intact it can be quite
hard to believe that i'm going to
double-click on the query i created to
edit it and i'm going to go through and
create some transforms the first and
most obvious thing I need to do is to
sort out the row headings so to do that
there's a box that says use first row as
headers that's exactly what i want to do
if i take on that that will solve that
problem
the next thing i want to do is to rename
my columns so the country are dependent
territory i'm going to rename and just
called country
it may not be accurate geographically
but I can live with that and then i'm
going to get rid of the columns are not
interested in which the date column and
the source column i use the control key
to select them because they were next
door to each other to remove them i can
click on this tool if i choose remove
other columns it will do the opposite to
what I want it will delete all the other
columns so i'm going to choose remove
columns and their disappear now what you
notice is is gradually building up a
list of stick weary steps when i click
on any one of these you can see the
progress is made to that date the
cumulative progress
the next thing I need to do is sort of
the population what I'm hoping is this
will be easy i'm hoping when i change
the data type of this column to a whole
number the park where will recognize
that and it has done and I justified it
I've nearly home and dry I can do the
same thing to the percent of world
change a decimal number and i get a load
of errors and that's because it didn't
recognize their numbers the percentage
sign was stopping at doing that so what
I need to do is to undo that step i can
do that by going to my list of steps
clicking on the cross next to the 1i
want to remove and it will disappear
what I need to do first is replace all
the percentage signs in their column
with blank strings so i can do that by
clicking on replace values and changing
the % to nothing
I choose ok they'll disappear and what I
can now do is change the data type of
that to a decimal number and those
figures will be okay and I should have
finished what actually happened is my
population figures are reverted back to
the left and there isn't that happens is
when i deleted the task it combine two
different things into the same task I
think and so I've now got to redo my
population figures so you have to keep
an eye on what you're deleting
what I'm going to do now is to show how
you can rename the query and any steps
within it and also how you can delete
individual transforms or bulk transforms
let's start with the easy thing i'm
going to rename the query let's call it
import country data I just a retired the
previous name and press return I can
rename individual tasks in much the same
way so for this replaced value task i'm
going to do is change that to say
replace percent sin symbols i could type
and for this changed type 1 i'm going to
rename that and call it change data
types to illustrate how deleting
transforms works and going to add two
new ones the thing we'll do is sort of
countries into alphabetical order by
clicking on the eight said task of the
top of the screen and then i rename this
percent of World column by
right-clicking on it
choosing a name and i'll call it just
percentages i've added two new
transforms what I want to do now is
change the sorted ros1 getting ahead of
myself there to do that you can click on
it and click on this cross or
right-click and choose delete both of
those two things will do the same thing
they will delete that individual
transform but they won't have any effect
on subsequent ones it's usually safer to
right-click and choose delete until end
and what that will do is delete not just
that transform but all the subsequent
ones as well and the reason that's the
safe thing to do is because it may be
the case transforms further on down the
line won't work if their predecessors
have been deleted
on this occasion actually I didn't need
to be quite so cautious because the
transform to change the name of this
column can't possibly have been affected
by sorting a completely different one
but hey how to show where this can go
wrong
let's take the replace percent symbols
the transform and delete it in isolation
it comes up with a dollar box warning me
i'm about to do something catastrophic
and indeed that's what that's justified
by the results
you can see now that my percent of well
column has errors all the way down it
and the reason for that is that the
predecessor task has been deleted
and so the percentage symbols are still
in the column what I now need to do is
to insert a task to change the
percentage symbols two blanks and so
probably the safest thing to do is just
delete that last task as well and
recreate the scenario had before that is
to replace all the percentage symbols
with a blank and then to change the data
type of this to a decimal number
irritating me what I then got to do is
also change the population to a whole
number because that was all caught up in
the changed type task and so when I
deleted that I deleted the two
transforms one for the population column
and one for the percent of World column
I think I've now finished what i can do
is to load this data into excel and
where you should see as we wait
patiently for it to load is that I've
got numerical columns they've all got
sensible names and I'm good to go if you
like what you've seen and heard so far
why not head over to the wise our
website where you can find loads more
free resources including these videos
some written blogs and tutorials and
even some exercises that you can
download to practice your skills
thanks for watching and see you next
time