query

How to Use Power Query / Get And Transform in Microsoft Excel 2016



Sharing buttons:

the power query tool is incredibly

powerful and incredibly detailed it can

be used to pull data from external data

sources as diverse as websites access

databases other excel files or even

JavaScript objects as a result like the

pivot tables I could probably spend an

hour or more discussing the different

ways to use the power query tool for

this course we're going to use the power

query tool strictly within Excel to work

around some of the limitations imposed

by the lack of true power pivot support

for the basic cell installation the

result will be a fairly high-level

introduction to the tool and a solid

example to follow for learning how to

further extend its usefulness for your

own needs first the power query tool

builds off of an initial data set so

while it's possible to create a blank

query doing so is very challenging we

need to design a query that merges our

products and line items tables together

so we'll start by using our products

table now we're going to add it as a

query quite quickly so at our products

table we're going to select the entire

table we're going to go to data and

under the get and transform tools which

is what the power query has been

relabeled as in 2016 we're going to

create a query from the table this

action opens a whole new window called

the query editor this editor has its own

ribbon which will allow us to modify our

new query with a whole host of database

like functions for now we're going to

simply close and load to and we're going

to select only create connection and I'm

going to press load this creates a query

from our product table and opens up our

workbook queries window will be able to

reference this query from other queries

which is what we'll need to incorporate

the products directly into the line item

table since we want to merge this with

our line items table we're going to go

to our line items table and do the same

thing that we did here we're going to

select the whole table we're going to

get our data we're going to choose from

table to create a new query now let's

learn about this query editor in some

detail each of the actions along this

ribbon does not permanently create a

change rather it adds an entry in a list

of to-do items in order to generate a

data

set this list of steps can be seen to

the right under applied steps this means

that each time you perform an action it

will get added to the list and you can

visualize the data at any given point in

time to understand what is happening

with each step for example I can select

the ID field and use this remove columns

button to eliminate the ID field since I

don't need it for anything this is added

a removed column step to my applied

steps I can see the data before by

clicking on the change type and the ID

reappears in my field but now what did

we come in here to do since we don't

have excels self-service business

intelligence we cannot use the data

access expressions Dax

to create calculated fields directly in

our pivot tables as a result we're going

to create a power query that merges our

products and line items tables together

and then calculates the information we

need within so the keyword I use there

was merge this is portrayed as a merged

queries option under our combined

options you could also append queries

which simply stack data on top of one

another so I'm going to click on the

merge queries and this pops up a brand

new merge window with our current Lite

items table at the top and a drop down

below that the drop down will allow us

to select any of the other power queries

within our workbook I'm going to select

the products from that drop-down and it

will show me all the fields available to

me now at this point I need to define a

true database style relationship between

the two tables selecting the correct

join will depend upon your data needs

and your visualization needs for our

data I'm going to define an inner join

which will only bring matching rows from

each table together a left outer will

take all of the entries from the first

whether they match or not and provide

matching rows from the second so you

might lose some rows from the second

table but you'll never lose rows from

the first the right outer does a mirror

image version of the left you'll get all

of the rows from the second product

whether or not they match something from

the first and you'll only bring in the

rows from the first and match the full

outer will bring all of the rows from

both and match them up as best as

possible

the inner will bring only those that

have matching values in each set and

then the left ante and right ante

provide versions where they only pull

the rows from the first

from the second for our purpose we want

to only show rows where both of them

have a match once we've selected the

join kind we need to identify what

fields should perform the joint on our

join will match the product ID from the

line items to the product ID on the

products this is the same approach as

the relationships except now we're

defining a truer database style

relationship once we've done this we can

press ok to update the query this is

appended a single new column onto the

end of my query which has table for each

the values it's also got a funny little

arrow symbol so if I click on that I can

see all of the columns that are crammed

into this single column and I can choose

to expand them if I didn't want to

include any I could deselect them since

I don't need Product ID twice I'll

deselect that and press ok I've now got

a table that contains almost all of the

information that I need for the invoice

table but I still need those

calculations each calculation will be a

column so we're going to go to the add

column ribbon and add it from there

I want to add custom calculations so

that's going to be done using the add

custom column command these columns can

perform a wide variety of calculations

using the available columns and some

special functions for this course I'm

going to avoid the functions as they

will work differently from your typical

Excel formula bar variety and have a

completely different syntax now this

column is going to be our total revenue

so I'm going to give it that name and

I'm going to add the formula as being

equal to sales price adding columns

simply by double clicking multiplied by

quantity

once I've specified my formula I can

determine that there are no syntax

errors below and I can press ok doing so

has added a new column to my table where

the total revenue is equal to the sales

price times the quantity I can do the

same thing for my total cost of goods

after creating these fields we need to

make sure that they're treated as if

they're numbers so we're going to go to

the transform tab and this allows you to

do a whole bunch of things to change the

actual data structure of the fields so

I'm going to select the total revenue

and the total cost of goods fields and

under the data type option

I'm going to select the decimal number

this means that it is a number with some

type of decimal places this makes sure

that the pivot table treats them as if

they're numbers because sometimes the

pivot table might think they're in text

or some other non numerical value which

makes sums counts averages and other

functions behave differently once we've

created these two fields we need one

more field and that's our profit field

and again we're going to do the add

custom column going to call it net

profit and our net profit field can

actually use our other two calculated

fields so I can use total revenue minus

total cost of goods again we need to

make sure that it's a number value so

I'm going to change it to a decimal

number at this point we'll go back to

the Home tab and before we finish we

want to make sure to rename our query

from line items since we already have a

table with that name and we're going to

call it invoice support once I've done

this I can choose close and load two but

I'm going to only create a connection

and I want to add this data to the data

model and I'm going to press load by

adding it to the data model I can now go

to my data tab go to my relationships

and I can create a new relationship or

my table here is my invoice sport table

and I can use its client code to map it

to the client list just as we did with

the other tables I can press ok to

generate that relationship closing my

relationships window and going back to

my pivot table I can now see that I have

a new table called invoice support as an

option I'm going to get rid of my

current values my current cost of goods

sold and my description and I'm going to

use all the fields that I need from the

invoice support I'm going to bring sales

price quantity cost of good total cost

of good total revenue and net profit

into my values field that I'm going to

use the description field as my row

column and now when I select the client

code I have the list of the widgets the

sales price the quantity the cost of the

goods the total cost the total revenue

and the resulting net profit all like

our original invoice template and all

easily reorder will simply by dragging

and dropping now I'm going to make one

more adjustment instead of using this

filter which is kind of ugly and kind of

challenging to use

going to get rid of it I'm going to

replace it with a slicer so for a pivot

table slice room go to analyze under the

pivot table tools choose insert slicer

and I'm going to use the client code

press ok and now we have an invoice that

uses the client code I'm going to make

it wide instead of tall and I'm going to

give it eight columns and now we can

pick and choose and define our invoices

using our slicer the slicer works just

like it did for the table and now I have

a basis for an invoice template that

does not use formulas at all while

having a variable number of rows and

entries while the formatting isn't quite

there you can easily apply your own

formatting information set the columns

and the rows and all the different font

features and then add some additional

lookups to generate the address details

and referral details using the same

general approaches in our original

invoice now let's use all that we've

covered to complete exercise number

seven for exercise 7 you can start with

either your solution to exercise 6 or my

solution exercise 6 which is the

exercise 7 file now each of the tables

has been normalized and the

relationships have been defined your

goal is to create two pivot tables the

first should summarize the average

number of hours worked for each employee

and since this is a straight

relationship the power query will be

unnecessary as you'll be able to

calculate the average using a pivot

table built upon the data model the

secondary pivot table should calculate

the total amount earned by each employee

this will require the power query tool

to add a calculated field using the same

approach that we just used in this

lesson to calculate total revenue my

solution will be made available to you

but there are different routes you can

take so you should focus on getting the

right outcome our next lesson will look

at how to extend pivot tables with pivot

charts for an added level of data

visualization hi I'm Nigel from simon

says it thanks for watching if you need

additional Microsoft Excel 2016 training

you can get our entire 60 course

software training library for $1.00 this

is a limited time offer that includes

three individual Excel 2016 courses to

help you master Microsoft Excel click

the learn more button on the right I'll

see you next week with additional videos