you need to learn all about excel data
analysis you landed on the right video
video number four in microsoft 365 excel
the complete story and this is going to
be a crazy video you're not going to
believe it all of these tools taught in
one video that's right sort filter flash
fill power query pivot table charts
power pivot and even power bi that means
this is one video
to get an introduction to every one of
these tools now mex video number four
is three hours and 43 minutes long that
means there's everything in it one stop
shopping but guess what below the video
you should probably click more and use
the time hyperlink table of contents now
this is from video number three so this
is only an hour and at the bottom
there's also this visual time hyperlink
table of contents
[Music]
now of course each video in this class
has pdf notes you've got to be kidding
me 32 pages if we click on example one
we're going to start it off by learning
some cool tricks about sort filter and
flash fill number four we'll see one of
the greatest uses for a pivot table
summarizing survey results
five we'll see how power query can go
from a website that downloads a csv file
and we have to do some crazy
transformation to get just the file we
want and then later we simply click
refresh and everything updates example 6
we're going to have 12 different tables
but power query will have no problem
converting it to a single table and
calculating sales for us and we won't
load this to the excel worksheet we'll
load it directly to a pivot table and
make a report and chart example seven
will have to look at x lookup function
because sometimes worksheet formulas
helps you get to your final pivot table
and chart
in example eight we'll get an
introduction to powerpivot and dax
formulas because we have a bunch of
different tables all working together to
create these reports then once we create
our data model and powerpivot we'll send
it automatically over to power bi
desktop to get this amazing interactive
dashboard and the data model and power
bi desktop will look almost exactly the
same as powerpivot then in our final
example we'll import seven million rows
of data from an sql database and make
two dashboard reports and this is our
final data model with four different dax
measures here's the download link at the
highline college website this link is
also below this youtube video right
click save link as here's the folder on
the desktop that we've been using for
our other files that zip folder click
save there's the folder path
right click
extract all click extract then on the
left i'm going to open it up select it
on the left and there's all the files
we're going to use in this video yes we
have 10 amazing examples and since this
is about data analysis we have a lot of
files now below the youtube video
there's this list of the 10 examples
with a time hyperlink table of contents
so you can jump to whichever example you
want now on data analysis let's just
remind ourselves what we learned in
video number two
data analysis is when we convert raw
data into useful information to gain
insight and make decisions there's lots
of synonyms data analytics analytics
business intelligence data science data
analysis in every single example we'll
start with data data into information to
make decisions or data into information
to gain insight and don't forget almost
every single one of the tools that we
use require a proper data set now let's
go over to sort filter and flash fill
now the amazing thing about sorting and
filtering and this is an excel table is
these drop downs in this user interface
for sorting and for filtering it's the
same in all the tools this is an excel
table pivot tables power query power
pivot and power bi desktop they all use
the same conventions as we have over
here in the excel worksheet now to sort
means to organize records in a table
from smallest to largest or a to z
or ascending
or the other option biggest to smallest
z to a
or descending you can also sort by fill
color and font color now the most
important thing about sort is if we
decide to sort on this date column all
of the records will remain intact that
means this field here will be sorted
we'll do a to z but the sort in this
column determines the sort for all the
records so if i come up and say a to z
oldest to newest that record remains
intact as do all the other records
control z now we sorted by one column
but you can definitely sort by more than
one column let's sort sales largest to
smallest every single record is sorted
from biggest to smallest but watch what
happens when i come over and sort
region a to z this is a second sword
this is beautiful because this field was
already sorted when i sort all the east
together they are listed biggest to
smallest and when i get to the next
category biggest to smallest control z z
now if you want to sort a
column or a table that's not an excel
table
up in the data ribbon tab sort and
filter group for sorting there's buttons
and a dialog box and then some filtering
options now sorting without an excel
table you can do that you got to have a
field name at the top yes i know you can
highlight and force sort by highlighting
something in general it's always best to
have a field name at the top then we can
simply click the button and knows what
to do
also if this is not bold and the field
name is text and these are all text
watch what happens that field name gets
sorted with the records control z that's
why you hear me always say in every
video i've ever made about tables if you
don't have an excel table add some
formatting to the field name at the top
because excel is programmed to know hey
that's a field name now when i do this
it works fine control z
now most of the time when we're doing
data analysis and in particular when we
use power query we're going to have a
method to ensure that the data type in
each column is consistent but if you
have mixed data in the worksheet and you
try to sort a to z this is the order
that the types of data will be sorted in
as an example here's a really terrible
field or column of data including empty
cells now instead of using the buttons
for sorting we can also right click
there are filter and sorting options now
8 is easy to aim some other options here
but what we want is a to z
numbers at the top text true falses
errors and look at that empty cells
always sort to the bottom and they'll
sort to the bottom whether you have a to
z or z to a
control z
now let's look at filter to filter means
to show only certain records based on
one or more conditions for one or more
columns and you can use any logical test
an and logical test or logical test
between and many more and whereas
sorting organizes the records but keeps
all records showing when you filter it's
going to show you only the records that
match the criteria and hide the rest so
filtering will allow us to set a
condition and get just the records that
we're interested in but before i show
you filtering there's a particularly
helpful use for filters and guess what
this filter feature will be in every
tool now imagine we just got this data
set and you're like i don't know how
many product ids there are well when you
click the filter drop down in every tool
it will show you a unique list so
instantly i know there are seven ids in
this column region
there are six even in a column like this
with tons of values there is a unique
list now in power query for example
we'll use filter to get just the records
we want before we import but when you're
in the excel worksheet one of the best
uses for the filter feature is simply to
extract the records you're interested in
so imagine your boss just texts you and
says hey i need the west records you
just come up filter uncheck select all
click west click ok and there are your
records now you can tell that they're
hidden because the row header numbers
are blue and there's hidden rows and
watch what happens when we highlight and
copy control c
the dancing ants are having a huge party
now those dancing ants are only dancing
around visible cells those hidden
records are not copied now you'd open up
a new workbook paste and then email it
to your boss for right now we're going
to come over to west control v and there
we have extracted the records for the
region west
back over here we can bust up this
dancing ant party by clicking the escape
key now you can un-filter by clicking
the filter
clear also up in sort and filter there's
a clear
clear now the most powerful aspect of
filtering is data type specific
filtering when we click a drop down on a
date it knows it's a date and if we
point to date filters you've got to be
kidding me there's all sorts of date
specific filters escape
for text
text filters all sorts of important text
filters when we get over to power query
and we're filtering excel files we might
say we only want files that end with dot
xlsx
and for numbers there's all sorts of
number specific filters including
greater than or equal to some hurdle or
this one top 10 or you can change it
want to see the top five sales use this
filter
now one other important thing about
filter is we can use any type of logical
test and we have a video a couple videos
ahead that talks all about logical test
but if i select west from the region
column that's filtering on one column as
soon as i jump over to a second column
and say for example chantel and notice
that unique list is based on that
filtered column but when i select two
conditions on two different columns this
is an and logical test that means the
record only is included if
region equals west and sales rep equals
chantel
i'm going to clear this
now that's two columns two conditions
if you instead use one column and i say
northwest and in english we say and west
but this is not an and logical test this
is an or logical test when you're
selecting multiple items from one column
it's an or logical test when i click ok
as we'll learn a few videos ahead what's
happening is i'm saying is the region
equal to west or is it equal to
northwest and we'll use both of those
logical tests throughout the rest of the
class i'm going to unfilter
now on to flash fill now flash fill data
ribbon tab data tools there's the flash
fill button this is a one time simple
data cleaning tool if you have a
consistent pattern you can clean the
data by typing an example to the right
of the column of data then clicking
flash fill or control e now let's scroll
over and look at an example now we have
a column of data here and our task is
simply to get the name in the column to
the right now before you use flash fill
you have to make sure of three things
the first is this is a one-time task
we're never going to have to repeat this
the second thing is we will not get more
data later and need to refresh
if that's the case you use formulas or
power query and the most important thing
is you have to recognize a simple
pattern and this column of data has an
easy simple pattern i want everything
after the first space now here's how i
do it i usually copy and paste so i
don't make a typing error control c i'm
going to paste this and click in the
cell below and you either click flash
fill or control e i can't tell you how
often i use this tool but not as part of
a an elaborate data analysis project
just want to have a one-time task with a
simple pattern now this was everything
after the first space
over here i want everything after the
last space i type west enter control e
and that is amazing now you have to be
careful sometimes flash fill will really
mess things up and it's because it
cannot see the pattern if i come over
here and type zero five slash zero five
slash
2016
when i hit enter and control e
that is just junk
it cannot see the pattern now i have
lots of videos at youtube about flash
fill and how to do special tricky things
to extract a date like this with flash
fill but it's just not worth it most of
the time if you cannot immediately see a
simple pattern in this case i would use
power query and get the text between
these two delimiters all right so on
this sheet we saw sort filter and flash
fill now we're going to go over to the
sheet pt
and look at one of the single greatest
examples of why the pivot table tool is
so amazing now back in video number two
we learned about the basics of pivot
tables and our goal here is to simply
take this one column of survey data get
a summary with count and percentage
responses now anytime you have a data
analysis project the question is which
one of the many awesome tools to use now
in this case we know we're going to use
the standard pivot table and the reasons
are we already have the data in excel so
we don't need to import it with power
query we don't need to clean it with
power query we have a small data set so
we don't need to use power pivot or
power bi desktop in fact if i control
down arrow we only have about 160
records control home and the
calculations we need to make are simple
all we need to do is count and do a
percent of total the standard pivot
table calculation abilities are perfect
for that now we have a proper data set
field name at the top empty cells all
the way around so we click in a single
cell in the proper data set go up to
insert tables group and click the pivot
table drop down now we have four options
that allow us to go get tables from
table or range that's if we have a table
in the worksheet from external data
source that's if we have a table stored
as connection only in power query from
data model that's if we have a table or
a data model stored in power pivot and
power bi that's when we have a table or
a data model stored in power bi online
we'll get to use a number of these
different data sources throughout the
class now because we're going to do so
many pivot tables in this class we're
going to use the alt keyboard alt that
brings up n for insert i tap n i see v
for pivot table v
and we have a table from the worksheet
so we hit t
there's our pivot table from range
dialog box because this is a proper data
set it always guesses right we want to
on the existing sheet i'm going to put
it in d4
click ok
now i'm going to grab this pivot table
fields task pane and drag it closer and
then resize it in the pivot tables
fields task pane we always get a
complete field list at the top we only
have one field so we drag the field down
to rows and instantly we get a unique
list now because this is a text field
when i drag it to values which is where
we drag fields when we want to make
calculations it will default to count
now if you want to change the aggregate
calculation you click in the values area
right click summarize values by you can
choose your different calculations now
for us we definitely want count because
we need to count how many responses
there are now summarize values by is one
way to make a calculation the other way
is this amazing feature show values as
now i'm going to click escape we'll use
that show values as in just a second and
i want to rename this
just count will do
and enter now we want to make a second
calculation that tells us the percentage
responses so we drag survey data down
again to the values now we come over and
right click and what we want is make
sure that summarize values by is set to
count because as soon as you switch over
to show values as whatever you choose
here is based on that aggregate
calculation and what we want is to take
each individual count and divide by the
column total so that's what we're going
to use so i'm going to click percent of
column total
select the top
percent count
enter
that means in less than 30 seconds we
can take this survey data and we know
what the count and percent responses are
now we'll get to learn a lot more about
pivot tables in this video and
throughout the class all right we want
to go over to the sheet pq
now we want to talk about power query
and guess what power query is the
greatest excel tool invented
since the pivot table and the reason why
is ever since the 90s the pivot table
has been an amazing tool to build
reports and charts but we've always been
missing a tool that can import the data
into excel and also fix and clean bad
data that's what power query does power
query can import data from outside of
excel
things like text files other excel files
databases and lots of other locations it
can clean the data like split first and
last name into different columns or
extract a date from a date time it can
transform data like remove unwanted
columns which is one of the things we'll
do in our next task calculate new
columns we'll do that two examples ahead
or combine multiple tables into one
table we'll do that two examples ahead
and then it loads the data either to the
excel worksheet directly to the pivot
table cache or to the power pivot data
model not only that but power query is
not just in excel it's also over in
power bi desktop so whatever you learn
about power query here in excel it works
the same
over in power bi desktop now in excel in
the data ribbon tab get and transform
data and queries and connections that's
power query there's a bunch of different
connectors that connects us to different
data sources and then this will open up
the queries after we create them just
for kicks here's a power bi desktop file
and data and queries those are the two
groups that make up power query here's
all our connection to different data
sources this is how we get to queries
we've already created now we want to go
over to the sheet pq1 info now in this
example we're going to have some data at
a website we're going to need to
download it as a csv file and we'll talk
about what that is then import it
transform it and load it to the excel
worksheet and in the worksheet we'll add
formulas and formatting
and then when the website updates we'll
simply click refresh power query will
re-import re-clean and transform and
update our table now of course when
we're trying to figure out which data
analysis tool to do a project like this
because we're importing cleaning and
transforming of course we use power
query now the data we have at this
website it's student assignment score
data from a particular class now
actually i made this website this is a
fake website if you click this export
csv button it doesn't work and i did
this because the website that i usually
use to get my grade data i can't show
you that live in public but this is
exactly what it looks like over in the
canvas system now this is gray data but
it could easily be a website with sales
data for a company or government
economic data or data from a 911 call
center and at most websites where you
get data there will be an export to csv
button now for our particular grading
data from our canvas website they have a
bunch of columns with a bunch of field
names that i don't need i want to change
them a bunch of columns that i don't
need both with data and some extra
calculations that i don't need now if
this was canvas i just click this button
and it would download the csv file and
then that's what we import into excel so
what i've done is i've created three csv
files that will simulate this is the
first website this is when it updates
later and this is the final update so
even though we won't download these from
the website the process is still the
same when we get a new file we need this
new data to update our project now what
is dot csv mean it means comma separated
values and if we right click open with
and i'm going to open with notepad or
wordpad or word
i'm going to use word
and then click ok
this is what you see each one of the
field names name id quarter is separated
with a comma and then all of the records
there's a first name comma id comma the
quarter comma the name of the class and
then a bunch of scores now why in the
world would a table of data be stored
like this
well it goes back to the beginning of
database history since there were so
many different types of systems we
needed one method to get tables of data
from one system to another so
one of the conventions they agreed on is
a delimited table now delimiter means
what separates the bits of data for us
the delimiter is a comma the other type
of common delimiter is a tab we'll see
that delimiter in our next example so
all a
text file either csv comma separated
value or a text file which is a tab
delimiter all they do is get data from
one system to another and when power
query sees this it'll interpret it as a
table now another important note about
dot csv files this computer is
programmed to open up dot csv files with
excel so if i double click this this
looks like excel but it's not that dot
csv file extension and the file
structure prevents you from doing almost
everything in excel so just be aware
when you see csv this is not an excel
file now before we jump over to excel
we're going to do a little file
management now here's the folder we
downloaded and extracted and you need to
make sure that all of these files are
closed before we do our file management
so for example if you have this open you
need to go close it and then come back
to windows explorer so we can do our
file management now before we use power
query to import the csv data into excel
i want to look at the folder path we're
here in windows explorer and if i click
up in the address bar that's the full
folder path that power query will
memorize
when we import this file now this is
called an on-premise folder path and the
reason is this folder path is specific
to the computer i'm working on users m
girvin desktop that's going to be unique
to my computer you will have a different
beginning folder path but the rest of it
folder name from video 1 folder name
from the files we just downloaded those
will be the same now if you look up in
the formula bar
when we extract it it has a duplicate
folder name to make this folder path a
little bit shorter and easier to read in
power query i want to remove that first
duplicate folder path now over on the
left this is the folder we created in
video number one
but one two there's one duplicate folder
if i look inside this one well we're
looking at this when i look inside this
one this is the one that actually has
the files so i want to cut this and
paste it into this folder right here you
can either click and drag to move it or
you can use control x to cut select the
folder we created in video one and
control v
and now we have a clean folder path
here's everything inside that first
folder here's everything inside the
folder we downloaded for video number
four now if i click up here that'll be
the folder path now we'll point power
query to this and it will include that
file name at the end now let's open up
our excel file and start the import so
i'll double click we're on the sheet pq1
we go up to the data tab
getting transform now we can directly
click from text csv but all of these are
connectors and if you click the get data
drop down from file it has different
types of file types like excel csv
xml
there are databases later we'll connect
to an sql database
from azure
power platforms and many other
data sources i'm going to click escape
and click from text csv
in the import data dialog box we
navigate to our folder there's the
folder path i select grade 1 dot csv and
then click import now it wants to help
us properly import this it wants to know
what the file origin is that's correct
this is the one you always have to check
make sure it's the correct delimiter for
us it got it right and it usually does
data type detection the default is based
on 200 rows which usually works you can
base it on the entire data set
now you almost never want to click load
you want to click transform data what
transform data does is it opens up the
power query editor and gives us a chance
to clean transform do whatever we need
to do before we import and then load it
so let's click transform data
and here it is the power query editor
this is a new window on top of the excel
window and we can already see that power
query did its magic we have a preview of
the proper data set over here on the
left is the default name for the query
also on the right this is where we can
rename the query
and we definitely want to give this
query a good name i'm going to highlight
this and this is for the summer business
101
scores and enter we renamed the query we
can see it listed on the left now right
now in the power query editor we only
have one query but as we import and
transform new data in this excel file
we'll see other queries listed here also
now one of the most amazing things about
power query are the applied steps now
there are three automatic steps we can
add other steps later by using the user
interface and we'll add new steps these
applied steps are memorized by power
query so later when we refresh all of
these steps are rerun to refresh the
query now watch what happens when i
click on source
it gives you a preview of what it did in
that step that means we can look at each
one of the steps it memorizes what it
did that's really different than in the
excel worksheet when we do something in
the excel worksheet and have a
progression as we're building something
we can't go backwards like this so in
the first step it got the data in
records but notice the field names are
in the first row and the column headers
column one column two are default field
names when i select the next step i get
a preview sure enough it promoted the
headers now the last step is change type
and before i give you a preview of that
in the name column it says abc that's
the icon to say this field has a text
data type if we click abc a drop-down
appears and these are the different data
types that we can use in power query
decimal number that's up to 15 digits
like in the excel worksheet currency
that will apply currency but only allow
four digits there's a whole number that
will show it as a percent date time date
time
text true false and binary as we will
see later that allows us to actually
have files in a column where we're
allowed to do things like combine tables
now let's scroll over
and so here's test one abc we can see
the alignment to the left that tells us
it's text but when power query applied
change type to all the fields it usually
guesses right in this case because there
were no decimals it shows the whole
number data type which shows as a one
two three icon you can also tell that
these are numbers of course because
they're aligned to the right
so these applied steps the automatic
ones and the ones that we're going to
add later this is the foundation of a
power query query
but behind this foundation is an amazing
power query
functional language called m code and
microsoft says the m and m code means
data mashup that means to bring lots of
data together and mash it up into the
proper form now power query with the
automatic steps and when we use the user
interface to add new steps power query
writes all that code for us now you can
write your own m code but most of the
time you don't need to i will show you
how to write some m code though when the
m code we can write is better than what
power query can write now i gotta go
show you where to look
to see that m code because it is a thing
of beauty that microsoft created now
first there's applied steps but second
when you click on any particular applied
step
look up in the formula bar and you can
expand the formula bar look at the names
of the functions csv dot document
file dot contents
that is amazing microsoft named all of
these m code power query functions with
really easy to understand names and
there's the file path if we go to
promote headers sure enough they named
this function smartly table dot promote
headers if we click on change type table
dot transform column types so we have
applied steps and then in the formula
bar we can see each particular step and
the m code that's created but now i want
to show you the third place to go and
look and notice that's called source
there's the formula that's called
promote headers there's the formula
if we go up to in the home ribbon tab
query advanced editor let's click here's
the advanced editor in a new window and
this is all the official m code it
always starts with let
there's the name of the step then an
equal sign
and then the formula now notice this
first step we were allowed to go look at
it it's called source when we click in
the applied steps over here it shows us
the result of source
at the end is a comma and then of course
the next line there's the name of the
next step now why is there a pound sign
well because in power query and in excel
also we know that any text is in double
quotes so if this
name
just use double quotes power query would
think it's text so when they created m
code they said hey when we put a pound
sign on the outside and then double
quotes that means it's an official name
for an applied step now source does not
need the pound and double quotes because
there's no space so second step equal
sign table dot promote headers a comma
now the other amazing thing
step one is name source but what does
the next step do promote headers it
works with that so for automatic steps
and when we use the user interface the
first argument of the next function is
always the name of the previous step
comma at the end
change type table dot transform column
types and sure enough there's the name
of the previous step now when it gets to
the last step what there's no comma
that's because in
and repeating the last step tells power
query this is what it's going to deliver
as the final value in our case it's a
table with promoted headers and changed
data types we can click cancel now the
majority of the time all we need to do
is just look at applied steps but now
you know
applied steps see the formula in the
formula bar advanced editor to see
everything all right now that we've had
a complete preview of the magic of power
query
let's finish cleaning and transforming
this table now if i'm going to do a step
i definitely don't want to have
source selected i want to act on the
last step so i select change type now
our first goal is to remove some columns
we do not need quarter and course number
and there's some columns at the end we
don't need
the easiest way to do that is up in the
left hand corner
there's a drop down we click
and select choose columns
this is especially nice when the table
has a lot of fields we don't need
quarter or course number we don't need
current any of these calculated columns
at the end name id all the tests all the
quizzes we're good to go click ok
there's our new step table dot select
columns well that's a smart name notice
the first argument contains the name of
the previous step
now just if you're curious of course if
you go up and look the new last step
better not have a comma
and there it is
and it doesn't and the previous step has
a comma
cancel now we need to rename some of
these fields i don't want the summer 22
so i'm going to double click
test 1 i'm going to not have a space and
enter it adds a new applied step table
dot rename columns we could look at the
function notice it's acting on the
previous step and there it is there's
the old field name and the new field
name now you can keep this open
while you rename the field so i'm going
to double click
test 2 with no space and enter and sure
enough there it is and in power query
curly brackets mean a list and notice
there's a list within a larger list each
one has the old field name and the new
field name
we will continue on test three and enter
test 4 enter quiz
1 enter
quiz 4 that's the last one and enter
now we can close the formula bar and our
last task is we're going to have to add
data types now i don't want whole number
1 2 3 i actually want decimal in case we
get future scores that are decimals also
quiz 3 and 4 have a text data type so
when we get numbers later those numbers
would not work so we want to change the
data type and we absolutely could select
the last type and do it one column at a
time but i want to be efficient and not
only that we have this previous step
that already changed the data types so
what i'm going to do is i'm going to
delete it and just redo it down here
that way we're being efficient the fewer
steps the more efficient it is now you
can hover and use the red x but you got
to be careful when you're deleting a
step if this step would adversely affect
the subsequent steps you don't want to
do it but changing the data type has no
effect on removing columns or renaming
columns so i'm going to delete this and
this warns you deleting this step may
affect subsequent steps but it's not
going to have any effect on subsequent
steps so we delete i select the last
step i'm going to start at the beginning
click on name hold shift click on id and
instead of doing it one at a time
right click change data type and we'll
go down to text
now we select test one
all the remaining fields are numbers so
i scroll over hold shift click on the
last one right click any one of the
field names
change type and we want decimal
and there's our new step
table dot transform column types we see
nulls in these columns that's what power
query uses instead of showing an empty
cell
now we've done all of our
transformations we have our steps it's
named and now we're going to load our
table to the excel worksheet
in the home tab close group we click the
close and load drop down if you click
the button or select this first option
it does the default it loads it as a
table on a new worksheet if you want
control over where you load it you
choose close and low to by the way the
close means it'll close the power query
editor and load gets us to the next
dialog box so i click here's the import
data dialog box
if we select table it will load it as an
excel table we can also check pivot
table report it'll put it in the pivot
table cache or pivot chart that also
goes into the pivot table cache
if we select only create a connection it
doesn't load it to the worksheet as a
table or in the pivot table cache
if you leave it just like that it just
stores the query in the power query
editor and doesn't load the data
anywhere
later when we use powerpivot to load it
to the data model we'll select this
because we don't want it in the
worksheet and then check this so it goes
to the data model for us we want table
existing i'm going to select c4 and when
i click ok
there's an excel table loaded to the
worksheet now when you load a query over
on the right the queries and connection
pane always comes out too small so we
have to widen the task pane queries in
connection that's this button right here
if you don't see it now notice there's
the name of the query if we hover we get
a preview we can see how many columns
there's the load status it tells us it's
in the worksheet but later when we load
it to the data model or a pivot table
cache that's a helpful hint about where
it was loaded and there's the file path
now the name of this query is summer
business 101 scores but that's the query
object we also have an excel table
object here and it has the same name if
we go up to the name box in the formula
bar sure enough that's the same name but
that's the name of the excel table
object and it makes sense that a query
object and table object have the same
name because they're related in fact
anytime we want to edit or change this
maybe we made a mistake
we can just open the query change it and
reload it
in fact what i really wanted to do is i
wanted to split first and last name and
then sort the table by first name so
let's go do that and then reload the
query
to open the power query editor double
click
our goal is to split this column but i
want the change data type step last so
i'm actually going to x this out
i have the last step selected so now i
select name i can go to home transform
split or right click
split column
we want to split by the delimiter space
now it guesses right and we only have
one space here so any of these options
will work
when we click ok
now we have split one column into two
columns
there's our new step and look at that it
added an automatic change type now i
don't want that because i want to
manually do this at the end so i'm
definitely xing this out there's
actually a setting in file options to
turn off automatic data types
now the next task is to rename these and
by all means we could select the last
step double click and rename but this is
one of those situations where if we look
at the m code in the formula bar we can
probably figure out what's going on
table dot split column
there's the name of the previous step
there's the original name it has this
big function that actually did the
splitting but look at that
there are the new names and it's in a
list so we can simply edit the m code
selecting name one we'll type first
name
selecting
name two
last name
and now when i hit enter
i did that again
i'm going to x this out but sure enough
we did some m code editing and now we
have first and last name now we need to
move last name to the first column so we
can click the field name and drag it
over
there's a new step now we come to the
filter drop down and look at this sort
and filtering looks just like over in
the worksheet i'm going to select sort
ascending and now we have accomplished
our task two different columns sorted
correctly i see all of the steps we want
to add our last step i select last name
hold shift id
right click
change type
and text
select test
scroll over hold shift select quiz 4
right click
and we'll change this data type to
decimal number now we have all of our
steps
everything's looking good we come up to
close and load this is grayed out
because we've already loaded it so all
we have to do to reload is click the
close and load button when i close and
load that is a thing of beauty we have
updated the excel table by editing the
query now next we want to scroll over
and we want to add two new fields one
for total score and one for percentage
grade now anytime you're making
calculations like this in a column you
have a choice you can use worksheet
formulas or you can do them back in the
original query either way is fine
sometimes people are more familiar with
the worksheet formula so they like to do
it that way but in our next example i'll
show you how to create a new calculated
field in the query before we load it to
the worksheet now this is going to be
called total
score so when i type a field name it
adds a new field to the excel table
object
percent grade and enter i'll change the
column width now before we do our
formulas up above i'm actually going to
list the maximum scores so 100 100 and
then for the quizzes 30 and 30. that way
we can add up the maximum points for
assignments and use that in the
denominator for our percentage grade
so max scores
i'll enter some data and some formatting
now i went ahead and added some
formatting the numbers for the completed
assignment and then we want to add them
so i hit enter so so far the maximum
total points possible on this class are
260. now i want to add the total score
for each student
alt equals and i'm going to redirect it
test 1 to quiz 4
notice this is a table so it lists the
name of the table there's the implicit
intersection operator which makes this a
relative range of cells as the formula
is automatically copied down the range
will move and then it shows instead of a
cell reference colon cell reference
it lists the field name field name colon
field name so when i hit enter
there's the total scores now we can
build our percent formula equals
left arrow and there's the syntax for
relative cell reference divided by and
this is a cell outside the range so i
have n2 and we need to lock it so we hit
the f4 key when i hit enter the formula
is automatically copied down and in each
cell we compare accumulated points for
the particular student compared to the
total possible
133 divided by 160.51
257 divided by 260.988
now we can display this as a percentage
highlight control 1
percentage two decimals click ok i'm
also going to add some style formatting
right click mini toolbar
and then some borders
so what we've done is we created a query
and loaded it to the worksheet and then
added formulas and formatting now let's
go look at the updated website here it
is test three has been posted now this
is our fake website but what we would do
is click csv and download the new file
now there's two ways that you can
download the file and the way that i
usually do it when i download grades
from canvas is when i download it i
replace the old file using the same name
that way over in power query all i have
to do is refresh
but if you want to keep a historical
record and at any time be able to show
any one of the sets of scores then you
download the files and name them
differently
that's what we're going to do we're
going to go over to power query and say
hey power query don't look at grade 01
look at grade 02. now in excel there are
two good ways to update the data the
first way is let's edit the query double
click and in the source step you click
the gear icon
this dialog box allows you to browse so
you click browse
you find your file selected click open
i'm going to click escape that is one
way to do it the other way to do it
is in the data ribbon tab get data
data source settings now i like to do it
this way because over in power bi
desktop this is very similar to how we
do it we open up data source settings
we may have many connections to many
different data sources we select the one
we want and click change source
here's the same dialog box i click
browse
select file to
import ok
close
and we do have to refresh you can click
the refresh button there or you can
right click the table refresh
and just like that i have the new scores
i come up here type 100 and enter and
everything's updated now what if we get
yet more scores well let's go up to data
source settings select the source
click change
and instead of browsing guess what
there's only a one character difference
so i'm simply gonna
add a three here
click ok click close right click refresh
add the 30 at the top and everything's
working all right so in this example
here we use grade data but by all means
you might be getting sales data or
government data from a website using a
csv file we saw how easy it is to get
power query to import it we can clean
and transform it load it to the
worksheet and update later now we want
to go over and look at our next example
i'm going to click on the sheet pq2 pt
info example six this is going to be one
of the best examples in the entire video
and the reason why is business people
for decades have had to take multiple
files with monthly sales data combine
them into a single table make their
monthly sales report and plot the
results but the problem is for decades
this has been a horrible manual process
and when you get new data it was always
very difficult to update the solution
but then came power query power query
has no problem importing all these text
files combining them into a single table
calculating sales then we load it
directly to the pivot table cache and
the pivot table is suited perfectly for
these types of calculations and then we
plot it with an excel chart not only
that but if we start with nine months of
data and create our solution all we have
to do is drop whatever new monthly files
we have into the folder and click
refresh and everything will update now
looking inside of our example six folder
that we downloaded we have nine
different text files now i want to open
one of these up
right click open with
word now i opened it in word because in
the home tab paragraph i want to click
the non-printing characters button and
now you can see the delimiter that arrow
in word means a tab
this is the second common delimiter
besides a comma and you can see we have
one two three fields date units and
price when we get over to power query
we'll combine all 12 of these and then
more easily than in the excel worksheet
we'll get power query to multiply these
two columns and create a sales column
now starting on the sheet pq2 pivot
table we're going to go up to data get
transform and the get data drop down
from file and this is without a doubt
one of the most amazing things that that
power query brought to excel we can tell
power query to get everything from a
folder so we'll click
and we navigate right down to our folder
we're not going to see anything from
inside of it because power query is just
going to look in this folder and take
everything that's in it and bring it
into the power query editor so we click
open there's a preview and this is
different than our last example this is
a table but binary means there's a text
file which to us is a table in each row
all of these fields here are attributes
for that file this is just like you
would see in windows explorer date
access date modified the folder path and
also very importantly we'll have to use
this column here the extension now we
click transform data we can see on the
left we have two queries now we
immediately come over and rename this
i'm going to call it many files into one
table and enter
there's our source step up in the
formula bar there's the function folder
dot file and there's our folder path
now this content column contains the
text files now if you click off to the
side
don't click directly on the word binary
because that will extract just that one
file but off to the side
we can see this icon preview of january
february and so on so that means in this
content column we have a column of
tables with the same structure field
names at the top records in rows all
with a tab delimiter that means with
power query we can easily combine these
into a single table and the way you
combine the files i'm hovering over this
double downward pointing arrow button it
says combine files now you need to be
careful binary just means there's a file
here if these were excel files or
database files and the table object in
each row did not have the same structure
and delimiter for every row then this
button would not work so that's when you
use this button only when the tables
have the same structure and delimiter
now before we use this button we want to
think about the fact that power query is
pointing to a folder and getting all
these files now right now it looks like
all of the files are text files but what
if we accidentally put an excel file or
a database or a word file in that folder
then if we tried to combine it wouldn't
work now the best way to deal with this
is just to make sure that there are only
text files in this folder but if there
might be some other files there's an
easy way to prevent those files from
appearing in this column and it's by
using the extension field if we use the
filter option to only include files with
a dot txt that would solve the problem
now we have to learn something really
important about power query m code power
query m code is case sensitive that's
different than the excel worksheet
almost everything in the worksheet is
not case sensitive but in power query
everything is case sensitive so it looks
like some of the files come out capital
txt and some lowercase so if we're going
to filter here we're going to have to
say hey include that possibility this
possibility this possibility and so on
but we don't have to do that because
because microsoft knows that and so they
offered an option let's right click the
extension column come down to transform
and sure enough there's an option to
transform everything into lower case so
when i do that now we only have to
filter to include txt lowercase now it
added this new step now we come to add a
second step down to text filters i want
to say only equals
and because we thought ahead and made
everything lowercase that's all we have
to do click ok
now we're ready to combine we want to
remove all the attribute columns first
so i come to content right click remove
other columns
and there's our step
now we're going to click this button and
when we click this button
it is going to do a lot of things for us
it's first when we click it it's going
to ask us some important questions now
because there's a lot of files here
power query is just going to use one of
the files to build all the code for us
so it's saying i'm picking the first one
here's the file origin make sure the
delimiter is the correct one and it is
tab based on the first 200 rows that is
fine now when we click ok here
power query is going to build all sorts
of new queries for us all of these
queries were automatically built and
then used to get this one final table
that is a thing of beauty now we don't
really need to know about all of this
because we were sure that all the tables
had the same structure but what it did
is it took a sample file built a
parameter which is a variable then from
one file transformed just one of the
files
then it built a custom function and down
in our final table it added a bunch of
steps including invoking that custom
function
simply amazing now the last step we need
before we load this is i want to
multiply units and price in a new column
so watch this we can select the column
hold shift click on price i've selected
two columns and i want to add a new
column so i go up to add column there's
all sorts of amazing ways to add columns
but from number we click the standard
and when we click multiply notice we're
clicking something in the user interface
it'll build the formula for us there's
nothing like this in the worksheet so
we'll click multiply and sure enough it
took 3 times 21.95 and got
65.85 and then did that for every row
now here's the new step
let's look at the formula in the formula
bar table dot add column it's definitely
acting on the previous step
multiplication that's the new name and
then for each row units times price and
in the last argument it was polite it
added data type decimal it's called
number but that means decimal now we
definitely want to rename this so
instead of double clicking and adding
another step we're going to be smart
we're going to write better m code than
adding a new step we could tell that in
the second argument that's where it
named this field so i'm going to double
click and call it sales and enter all
right so now you can say you've actually
written your own m code let's close this
we have all of our steps we are ready to
load this now let's go up to home close
and load drop down close and load to
and the import data dialog box we do not
want to load it as a table and the
reason why is we're never going to use
that table for things like filtering
sorting or making other formulas so why
duplicate the data source by putting it
into the table and then making a pivot
table which stores it in the pivot table
cache in this case when you don't need
to table and you want a pivot table
that's your selection we want to put it
on existing c4
click ok
and even though i don't have a table in
the worksheet it is in the pivot table
cache now let's build our pivot table
we'll drag date down to rows and of
course instantly it will build a bunch
of extra columns in the pivot table
cache i don't need date i don't need
days i see months that's what i want
let's change the column width and i'm
going to click up on the formula bar
just because it was my cursor was close
change that to months
i drag
sales down to values i get sum of sales
in the header i'll put in parentheses a
dollar sign to indicate the unit and
then this is a standard pivot table so
in the values area right click
number formatting when i use the format
cells with a single number tab i'm
formatting the actual field so i'll say
number
comma and i'm going to show zero
decimals i don't need to see the pennies
click ok now we need to show the month
over month change and then the
percentage change and then percent of
total that's where
show values as comes in we drag sales
down a second time
right click and summarize values by has
to be sum because we want to see the
change in total
sales so the aggregate calculation is
right so we come down to show values as
and there's this amazing difference from
so i select that it wants to know
difference from what well of course i
want the month to month difference and
when you're doing month-to-month
difference sometimes
accounting reports require everything
based on january but that's definitely
not what we want we want the previous so
previous means when i get to this line
right here i'm going to take the
difference between this and the previous
when i'm down here the difference
between this one and the previous month
i click ok and if you do the subtraction
off to the side 520 minus 671 you're
gonna get minus 150 000
right click number formatting will show
it all in zero decimals this will be
called change
with a dollar sign now we drag sales
down again
right click
summarizes sum so we go to show values
as and percent difference from
months
previous click ok and that calculation
is polite it adds the number formatting
up here i'll type percent change and
enter
and then we want one other calculation i
want to compare each one of the
individual monthly amounts to the total
at the bottom of the column so i drag
sales down to values right click
summarizes sum so i do show values as
percent of column total
percent of total sales
double click i'm going to change the
number formatting on both the percent
columns
to show zero decimals now what we want
to do is go
add some files to the folder and refresh
this report remember that query right
there is looking into that folder so
when we refresh
it'll bring in all new files now here's
october to december so i click on the
first one i hold shift i click on the
last one and i want to put in some files
that are not txt we want to test our
theory i'm holding ctrl key to select
items not next to each other we'll do a
pdf and a dot csv copy those
select the folder control v
alright so these should make it into the
report showing us october november
december and these extra files should
have no effect on our report since we
loaded this directly to the pivot table
cache all we have to do is right click
refresh the pivot table and just like
that that is absolutely amazing
in addition let's go look at the query
double click and we have our one table
let's look at source and if you don't
see everything here you might have to
click refresh preview but sure enough it
got october to december dot csv and the
pdf and if we look at our filtered rose
step and sure enough our filter which
was protecting our report worked like a
charm i'm going to close this query
editor now we want to make an excel
chart control and roll to zoom in
however when you create a pivot table
with multiple calculations if you point
a chart to this pivot table it will
include all the calculations and you
can't remove them now that's different
than if we built this with formulas if
you build a formula report with many
different columns you can pick and
choose any of the columns for a chart
you're making but no problem we're going
to highlight this and i'm going to copy
the entire pivot table and then off to
the side i'm going to select i4 and
control v
i want to point to the smart tag and say
keep column widths scroll over click
inside the pivot table i see queries and
connections but let's go back to our
pivot table task pane and i'm simply
going to drag the calculations off
except for the one we want for our chart
sum of sales by the way if we didn't
copy and paste that pivot table and you
wanted to access the data in the pivot
table cache
you could go up to insert pivot table
drop down and that's what this option is
for from external data source i click
you have to choose a connection and then
there's the query and the name of our
table so you'd select that click open
but we have our pivot table here with a
single cell in our pivot table selected
we go up to insert chart group and we're
plotting values in a time series across
consecutive months so the perfect chart
is charts line chart let's do the one at
the right here with the marker so i
click now we don't need these field
buttons so right click hide all field
buttons
this is chart junk it's not adding
anything a legend really doesn't help
unless you have more than one series of
numbers so i'm using the delete key
up above i'm going to create a chart
title something like company change and
monthly sales i select the chart title
when i see the solid line i type in
equal sign that shoots me up to the
formula bar i select the cell i see the
formula i hit enter
with the move cursor on the outside edge
i'm going to click and drag now i'm
going to hold alt to snap to the grid
now let's move
holding alt so we snap to the grid now i
want to add some percentages and
conveniently we're going to steal them
from this other pivot table chart
elements green arrow
data labels and when we click more
options it opens up the correct task
pane
we'll uncheck both of those and we want
values from cells
we'll get percent change
click ok
check values from cells down here label
position will say above
now for the vertical axis i'm going to
select it
control 1
and in the task pane we're going to say
display units and i'm going to say in
thousands actually this is a pretty new
feature in excel it's been over in power
bi desktop for a while but that's
looking good so we have our
visualization and our report all right
in this example we saw how to go from 12
files use power query to combine them
create a sales column load it to the
pivot table cache create our report and
then our visualization all right we want
to go over and look at our seventh
example on the sheet vlookup and pt
now in example number seven
and eight and nine also we're going to
have three different tables and from the
three different tables we're going to
need to create reports and
visualizations now in all three examples
we have what's called a data modeling
problem our table does not have all the
correct columns we need to create our
final data analysis solution if you look
at sales rep id these are numbers but i
need to look up each one of these
numbers and bring over the sales rep
name
same with product i need to look up each
number in each row bring over the
correct product name as a new column
once we have both sales rep name and
product name then we can build reports
that actually have the name
or down here the product name
now we could make the reports with sales
rep id and product id but then the
reports and visualizations would not be
as user friendly now anytime you're
trying to build reports and visuals from
multiple tables and you have a lookup
situation there's a number of different
ways to solve the problem now the first
way is to use worksheet formulas we're
going to add two extra columns and use
the x lookup function to look up the
sales rep and product names now when is
it appropriate to use worksheet formulas
well if you have the data in the excel
worksheet already and control down arrow
you don't have a lot of data 43 000 rows
is not a lot of data control home and
our data analysis solution can be
sufficiently created with pivot table
reports and excel charts then we can go
ahead and use worksheet formulas have
one table and then make our reports and
visuals now example eight and nine we're
going to have the same tables let's go
look at this sheet rdm dax
example eight we're going to have the
same three tables but they're going to
be coming externally from an outside
excel file so we're going to use power
query to import and instead of loading
it to the excel worksheet and creating
almost a hundred thousand formulas we're
going to load it directly to the power
pivot data model
that allows us to build relationships
between the tables and then actually
build data model pivot tables based on
multiple tables now we choose this
approach of course we're using power
query because we're importing but we
choose power pivot because in the data
model in powerpivot it allows us to
create relationships between tables and
avoid all those x lookup formulas in the
worksheet and later we'll see another
reason for using powerpivot or power bi
desktop is if you have a lot of data
power pivot and power bi desktop can
deal with big data much better than the
worksheet also we'll get to see that
power pivot and power bi desktop allow
you to create reusable pivot table
formulas called dax measures and then in
example 9 we'll have the same data
source we'll use power query inside of
power bi desktop load it to the data
model there and make data model power bi
visuals and dashboards now when do you
use power bi desktop instead of power
pivot or the excel worksheet well the
most amazing thing about power bi
is that you have interactive and
shareable visuals all right let's go
over and start the x lookup pt example
now we have our three proper data sets
and i've already converted the first two
to excel tables this table is named f
transactions this one is named
d sales rep let's click in the product
table control t
and then enter
immediately come up to table design
properties and we're going to call this
d
product
now why the d
the d is for dimension and this is a
dimension or lookup table these contain
the attributes that we want to come over
in our new columns that will serve as
conditions and criteria in our reports
and charts
now there's a very important
characteristic to look up or dimension
tables and that is the first column of
any lookup or dimension table must have
a unique list and both of these do and
the reason why is simple over here in
the sales rep id column we have many
repeats three through three five five
and so on but for a particular row when
the lookup formula is going to look up
three and try and find a match if there
were duplicates it wouldn't know which
one to bring back so dimension or lookup
tables always have a unique list in the
first column and then attributes or
characteristics that can be used for
conditions or criteria for making some
summary calculation or other
characteristics such as a product price
that you might look up and use in a
formula by the way this one f
transactions the f means fact because
these are the facts that we're trying to
summarize
now we're going to use excel tables we
don't necessarily have new data we're
going to add later but that's the main
benefit of excel tables if we add data
later or new products everything updates
also as we have seen before when we add
new columns the columns are
automatically incorporated into the
table object and also the formulas we
create are going to be easier because
we're going to use table formulas now
this new field would be called sales rep
and of course when i hit enter a new
column is added to the table now we need
to look up three here five here four
here and in each case we need to get a
match in the first column and then go
over to our second column and bring back
that value now in the old days we used
the vlookup function but that is a
function from the past it's much more
difficult to use than the new amazing x
lookup function now our goal is to put a
lookup value that is what value we're
going to take and try and match in the
first column so i select that cell
that's a table relative reference comma
lookup array that's which one of the
columns or fields has the items that
we're going to try and match based on
the lookup value well it's that one
right there comma
return array those are the values that
we want to bring back to the cell now
this is an excel table so i should have
clicked at the top with my downward
pointing black arrow and that puts the
column reference in
now there are a bunch of other arguments
in x lookup we have a video all about
lookup functions a few videos ahead so
we'll learn all about these arguments in
that video but for doing an exact match
where you're trying to exactly match
something in the first column you just
need lookup value lookup array and
return array
now this formula will automatically copy
down because it's an excel table
that's a relative reference but when we
highlight entire columns those are
locked as the formula is copied down
which of course is what we need close
parentheses and when i hit enter wow
i've added a sales rep
attribute column that i can now use as a
condition or criteria to help do some
summary calculation now let's do the
same thing over here this would be
called product
enter
equals x lookup i'm going to arrow over
there's the relative cell reference
comma lookup array i click at the top of
the product id column in the lookup or
dimension table comma return array those
are the items that i want to go and get
so i select that field and there's our
formula close parentheses and enter
now if this is forty three thousand rows
tall forty three thousand times two is
almost ninety thousand formulas now
excel is going to have no problem this
is a small data set but if you really
did have a hundred thousand or five
hundred thousand rows we would be
switching over to power pivot or power
bi desktop
that much data is handled much better
with those tools now the rule i use is
about
50 000 rows is the max i put into an
excel worksheet but the worksheet does
have a million rows so you can add a lot
of data here but things tend to slow
down when you do that now we have our
one table and when we add columns like
this from dimension or lookup tables
this is called a flat table and all that
means is we have one table when you have
one table you can use what's called the
standard pivot table from table range
now the standard pivot table is the
pivot table tool that works with one
table only and uses summarized value by
or show values as calculations a data
model pivot table which we'll see when
we use powerpivot is a pivot table tool
that works with multiple tables and uses
dax formulas to make its calculations
now i'm going to use the keyboard with a
single cell in our one flat table alt n
v t now i'm going to put this pivot
table on this sheet to illustrate the
reports and charts and tables all
together
normally i'd put it on a different sheet
clicking in cell k19 click ok
the first one is sales rep so we're
going to drag sales rep
down to rows instantly we get a unique
list we're going to get the sum of sales
so we drag sales down to values
right click number formatting number
comma
will show zero decimals click ok
now go back over click in a single cell
alt v t
existing
i'm going to put this one in n19 click
ok
region that was an attribute that was
already in the table we drag it down to
rows to get a unique list drag sales
over to values right click number
formatting now i want you to notice
something
we are using the sales field in multiple
reports and every time we have to add
number formatting that's just the way it
is with the standard pivot table when we
use powerpivot or power bi desktop and
create data model reports and
visualization we won't have to
repeatedly add number formatting because
you can add number formatting to a
reusable dax formula but with a standard
pivot table you have to reapply the
number formatting each time you use the
field click ok
come back over to the table alt nvt
existing
i don't want to put it directly next to
the table and i definitely want to put
it at least a few rows down i'm going to
select k29 click ok
product i get my unique list sales down
to values right click number
we'll add number comma
0 decimals click ok
now this one we're not necessarily going
to look at we're going to make a chart
from this and anytime we have categories
like this and we want to compare the
amounts across these categories the
perfect chart
insert
charts is not a pie chart research shows
that it's harder to tell the differences
between the amounts with a pie chart the
preferred chart type for comparing
amounts across categories is either
column
or bar don't use the 3d that's mostly
chart junk bar chart is a good choice
when you have very long labels otherwise
column now we'll talk about some of
these other two-dimensional columns and
bars in our upcoming dashboard and
visualization video for the time being
we're going to select clustered column
right click the field buttons hide all
field buttons
we'll remove this chart junk delete i'm
going to move this temporarily over here
right here
product sales total and i'm going to
indicate the unit and
we want to show these in thousands so at
the top i'm going to indicate that in
the chart title
here's the chart title equal sign that
shoots me up to the formula bar click on
the cell and enter now i would like to
show the amounts at the top so we come
over to our green plus
data labels and actually for this one we
can just click data labels now let's do
a little trickery because
here we have chart junk right we
probably don't want both of these but i
want to show these in thousands which
will then show these in thousands and
then i want to remove it because i don't
want the double numbers so with the
vertical axis selected control 1 to open
up the task pane
display units
thousands
and sure enough that it achieved our
goal but now i'm going to delete this
and there's our finished chart and there
with the help of a little data modeling
with the x lookup function we've created
our two reports and our visualization
now we're going to use these same two
data sets and use slightly different
tools and to summarize here we didn't
have so much data it was relatively
quick and easy to add these x lookup
functions and the calculations and the
visualization we wanted are easily done
with a standard pivot table and an excel
chart let's go over to the sheet
relationship data model and dax formulas
now we'll go look at our excel file that
contains the three tables then we'll use
power query in excel and powerpivot in
excel to create these data model reports
now if you've never used powerpivot
before you have to enable that feature
file options
add-ins on the left
manage drop down and we'll select com
add-in
click go and then you check microsoft
powerpivot for excel or here's a great
tip
in the data tab in the excel ribbon data
tools if you click manage data model
we're supposed to jump over to the data
model but when you click it
and it hasn't been enabled it says do
you want me to enable it so i'm going to
click enable and there we see the empty
data model and more importantly there's
a power pivot tab in the excel ribbon
now the funny thing is we don't really
need to use this tab but it does have to
be showing so that this button works and
the reason we're not going to use the
powerpivot tab when we click this to
jump over to the data model we can do
everything we want like creating
relationships and dax formulas more
easily over in the data model now on the
sheet pp
what does powerpivot do it creates data
model pivot tables and does these things
it allows you to create relationships
between related tables to avoid many
lookup formulas like x lookup it allows
you to use more than one table in a data
model pivot table report it allows you
to import large amounts of data which we
do not have in this example but in our
last example we'll have 7 million rows
of data so we can import
using power query we import into a
behind the scenes
data model location called the columnar
database this compresses the data and
can hold millions of rows of data
and powerpivot will allow us to create
reusable formational dax formulas these
are formulas used in data model pivot
tables that means instead of using the
default summarize values by and show
values as we'll build our own formulas
now let's go over to
rdm dax
now we can go look at our source file in
our video for folder here's the example
8 source tables i'm going to double
click to look inside now on the table
sheet there's the fact table and one two
three dimension tables we use both of
these tables in the last example but
we're also going to have a date table
and it functions the same way as looking
up a unique list and returning a product
name
from this date over here where there are
many duplicates here we have a unique
list of dates and so we can find a
particular day
and return a particular attribute like
month name year name you can actually
add lots of other columns to a date
table weekday fiscal year and many more
we'll actually do some of those other
date attributes in later videos but the
only data attributes we're going to need
in our reports are the month name and
the year now you might be asking why are
we creating a date to mention table when
a standard pivot table will
automatically group dates into months
and years and the reason is we're going
to load all of these tables not to the
pivot table cache but to the data model
and when you're in the data model
because we can potentially have
millions of rows of data we don't use
that automatic date feature in a
standard pivot table we build a date
dimension table just like we would for
product and sales rep in addition when
we load these dimensional lookup tables
to the data model we're going to avoid
having to create those helper columns
where we use the x lookup function and
the way we're going to do that is we'll
create a relationship between each
dimension table and the fact table now
if we scroll up i have a visual here
that helps to understand relationships
now in each one of these lookup or
dimension tables the first column is
always going to have a unique list that
means one of each date one of each
product id and one of each sales rep id
in a relationship this is called the one
side however when we go to the fact
table the sales rep id column is allowed
to have many duplicates and that makes
sense because this sales rep is going to
make lots of sales that's why this is
called the many side so what we'll do in
the data model is create a one to many
relationship then we won't have to add a
helper column with a lookup formula we
can simply drag and drop
fields from any one of the tables and
our report will work perfectly so unique
list one side over to the many unique
lists for product id over to the many
one to many relationships so our next
task is to import these four tables into
our other excel file now here's the
thing this is an excel file and we're
going to import this using power query
now remember the text files when we
import a text file there is only one
table in that file but in an excel file
you can have many tables and you can
actually have many types of objects like
define names or even the entire
worksheet so what we'll do is we'll say
hey power query i want to import the
objects from an excel file and power
query will immediately say
which one of the objects do you want and
we'll just check these four tables so
we'll close this
scroll down we're going to create our
reports down here but first we have to
go up to data get and transform and
import tables from our excel file get
data drop down from file
from excel workbook we're going to click
video 4 folder
there's our example 8 source tables you
can double click or select it and click
import and power query is polite it's
looking in that file and the objects
that it found
are four excel tables and a worksheet
called tables we want to select multiple
items and check each one of our tables
by the way that blue
field name
line at the top that's the little icon
that means this is an excel table as
opposed to a worksheet now when we click
transform it'll open up the power query
editor and create an individual query
for each table so i click transform data
here's the power query editor on the
left we want to close the folder that
has all the queries from combining the
tables there's our grade table that's
the first query did that's the many
files into one but here it is d date we
want to click on each query and make
sure the data types are correct
date whole number text whole number
those are all correct that's a great
name for our query and those steps are
fine
d product we're checking data types
whole number text the name is fine the
steps are fine
d sales the data types are fine that's a
great name the steps are good f
transaction
date
whole number text and
correctly it it assigned decimal to the
sales column and then whole number for
product id
that is a good name the steps are good
actually these three steps are the same
in each one of these four table import
queries so if you go back to source in
any one of those queries
open the formula bar you could see the
on-premise file path there and notice
the functions are named so smartly
xl.workbook because we're accessing a
workbook file.contents because we're
trying to get some of the contents from
that workbook and this formula delivers
a table down here if we scroll over
that has a column called data and in
each row is one of the different objects
in fact d date if you click off to the
side from the word table you can see a
preview that's the d date table here d
product so this column here has all the
objects from the excel file that's step
one it navigates through this table and
selects just one of the objects up here
you can see it got the f transactions
and there's the table and those sets of
applied steps are the same for each one
of the import queries here navigation is
getting d date
now so we don't have to add any steps
like we did in earlier queries now all
four of these have not been loaded so
when we go up to home close
close and load drop down we definitely
want close and load two in the import
data dialog box we definitely want to
come down and say add this data to the
data model but when you use this in the
future and you know you're going to the
data model your reflex has to be the
very first thing you do is come down and
check only create a connection the
number one mistake people make when
they're learning power pivot and data
model is they keep this selected and
then they come down and click this and
the problem is when you click ok it
loads the tables to the worksheet and
the data model so we want to say only
create a connection by selecting this
we're forcing this top part to have no
effect we're definitely not loading it
to the worksheet and we're definitely
not loading it to the pivot table cache
so only create a connection
add this to the data model when i click
ok
over here in queries and connections you
can see power query is working to import
this but also the data model that
columnar database it compressed all of
this data now you can hover
and we see loaded to the data model
that's to the data model data model
and this one too now that we've loaded
these to the data model we're definitely
going to go look and build relationships
and our own formulas now i'm going to
use control s to save
then i'm going to click the manage data
model button
now you can see this opens the power
pivot for excel window it's a new window
on top of excel this is not power query
it's not excel this is our data model
window it has its own tabs
down here
these are previews of what's in the
columnar database they show it just like
a real table there's d date d product d
sales rep and f transaction now what i
mean by a preview here is if you have
millions of rows in the columnar
database it's definitely not going to
load it all here but it'll give you a
preview
now later we'll come back
and in the fact table we'll build our
formulas down in the what's called the
measure grid now when we loaded this in
the home tab
view
we're in data view and you can see the
little tabs that means we're allowed to
get a preview of the tables
but we want to go over to diagram view
notice the lines between the tables this
is where we can go and create
relationships so i'm going to click
now what we see in diagram view is the
name of the table and a list of the
fields now i'm going to arrange the fact
table right in the middle
the date dimension table on the left
product
and sales rep on the right
and in diagram view there's two
important things we can do we create the
relationships between fields simply by
clicking and dragging and then later
when we have fields in a table like
sales rep id that we don't want to show
up over in the reporting area that means
the pivot table field list we can hide
them
now let's first create the relationships
and here's how easy it is i'm going to
take sales rep id
and drag it over and on top of sales rep
id when i let go it creates that one to
many relationship there's exactly one
sales rep id in each row and over here
in this column there can be many
duplicates we'll drag product id from
the dimension or lookup table over to
the fact table there is another
one-to-many relationship and from the
d-date table we'll drag the unique list
of dates over to the many repeats in the
transaction table and there by dragging
and dropping we have created
relationships and these relationships
will help in two ways the first way is
it will replace all those x lookup
functions which means the excel file can
calculate more efficiently or more
quickly because it doesn't have all
those formulas and the second thing is
we're going to be able to drag and drop
fields from any one of these tables into
our data model pivot table report so in
diagram view we create relationships but
we also want to hide fields like date id
that's a field we're never going to use
in our reports and also a field line
date we want to hide that because we're
going to use the date field over here
also the number field we're not going to
drag and drop number fields into pivot
tables instead we're going to build our
own formulas so in f transactions we
need to hide date i click on that one
hold control click on sales rep sales
and product id
right click
hide from client tool what this does is
it will hide these fields in the pivot
table field list now we kept region as
unhidden in the fact table because
that's an attribute or condition or
criteria we can still drop into our
pivot table and we don't have a region
dimension table product id right click
hide from client tool
sales rep id hide from client tool and
month number we don't need month number
although we will use it later the month
category we're going to drop into our
pivot table is this month so we're going
to right click hide from client tool and
by the way any fields that are hidden
we're still allowed to use them for
example we're going to use the sales
field to create a formula in just a
moment when fields are hidden they're
just hidden from showing in the pivot
table field list all right so we created
relationships and hid our fields now
let's go back over to data view
now in data view
below the bottom of the fact table we
can create formulas in what is called
the measure grid if you don't see this
measure grid that's because area
calculation area is not selected i'm
going to select this now i'm going to
click in a cell below the table and this
is one place that you can create what's
called a dax measure a dax measure is a
formula that you can drag into a data
model pivot table the other type of dax
formula that you can create in
powerpivot is a dax calculated column
we're not going to create one in this
example now the dax in dax stands for
data analysis expressions and
expressions is a synonym for formula
dax is a function-based formula language
which has some functions that are
similar to the excel worksheet and some
that are unique to dax now when you're
building dax measures there's actually
two locations where you can build
measures now over in the excel ribbon
there's a power pivot tab and in the
calculations group you can create a
measure i don't like how this feature
here places the formulas in the measure
grid so i tend not to use this some
people do though so i'm going to use the
measure grid and the formula is going to
be
similar to excel totally different than
power query and if you know access
formulas it's exactly like access
formulas so we need to name this dax
measure and that name will show up in
the f transaction table in the pivot
table field list so watch this i'm going
to start typing
in the measure grid and notice what
happens it jumps us up to the formula
bar now that name will show up in the
pivot table field list but now we need
to put an assignment operator and then
the formula after it now in the excel
worksheet and in power bi desktop the
assignment operator is the equal sign
everything after the equal sign is the
formula but in excel powerpivot and
access databases we have to type a colon
and then an equal sign now our goal here
is to sum so i can type sum and we can
see the sum function in the drop down
now the sum function and other functions
like average and min and max those are
the same over in excel there are other
functions like summarize some x which
are only in dax now i hit tab and it
says that it wants a column name now i
can scroll down and then select there it
is right there or in powerpivot and this
is different than power bi desktop you
can actually click on the column header
this is the same as excel tables so when
i click at the top of the field name it
properly puts in the column reference
and column references always have the
table name and then in square brackets
the field name now i close parentheses
name of the tax measure assignment
operator formula now when i hit enter
i get a result that gives me the grand
overall total for every value in the
sales column now you'll see the clever
thing about dax formulas is when we drop
this formula into a data model pivot
table whatever the conditions and
criteria are in the row column filter
area this dax formula will see those
conditions and criteria and give us the
right answer
now another amazing thing about dax
formulas is we can add
number formatting to the formula and
then wherever we use this that number
formatting will follow us around so home
ribbon formatting i'm going to click
comma
and then decrease the decimals and
there's our dax measure to show you two
other simple tax measures we're going to
calculate
average sales up in the formula bar
colon equal av and the average function
is the same as over in the worksheet i'm
going to select sales
close parentheses and enter
format this
so that average of
318 dollars that's the average for every
single transaction in the sales column
another formula we might want to see
count or number of transactions
hopefully i spell it right number
transactions colon equal sign
and this is a unique function to dax
this function does not exist
over in the excel worksheet or power
query count rows and it's asking for a
table so i'm going to put in f
transactions notice i type ft i see it
in the drop down so i hit tab and the
amazing thing is is count rows will take
that f transaction and as it sees
conditions and criteria in the pivot
table it'll count how many transactions
there are that meet those conditions now
we'll explain a little bit more about
how that happens when we build our pivot
table but for now close parentheses
enter and this is going to be comma and
of course 0 decimals because we're
counting now let's go back over to
diagram view and we can see the f
transaction table has some new entries
at the bottom now here we are in diagram
view in the power pivot for excel window
and this is a picture of our finished
data model
now there's five parts to this data
model that we created the first one we
can't see that's the behind the scenes
columnar database that compressed and
stored the data the second thing is we
can get a preview of the tables either
here in diagram view or over in data
view the third thing is we create
relationships in this data model then we
create dax formulas in this case we
created one two three
dax measures now the dax measures over
here in powerpivot shows an f of x icon
this icon and the names will show up
over in the pivot table field list and
then the last thing we created over here
is we hid some fields these are the
fields we don't want to show up in the
pivot table field list right now we want
to jump over to the excel worksheet and
make our first data model pivot table
i'm going to close the power pivot for
excel window
with the x now on the sheet rdm dax
we're going to select cell c37
go up to insert tables group pivot table
drop down and from data model and now
this dialog box says pivot table from
data model we just select the location
and click ok now i'm going to go grab
the pivot table fields task pane
move it resize it expose all the tables
now this is showing you all the tables
in the data model plus all the other
excel tables that are in this workbook
if you hover for example it tells you
that this has a range of cell source but
these we know they're in the data model
but they came from a query now currently
we're in the all tab which means we
could see every table from the data
model and all the worksheets now if we
go look at active there's nothing here
yet meaning no tables or fields or
measures because we haven't added any to
the active pivot table but if you come
back to all and our first report is
going to be a regional report and since
we want region in the rows and that's
the default i can simply check and
instantly i get a unique list of regions
in the row and i see the field down here
now we want to see total sales for each
region and i'm going to check this
because the measured by default goes
down to values and look at that we do
not have to add number formatting
we'll check average and it has our
number formatting but i would like to
change the name of this and the
formatting so guess what i can go back
to the data model data tab data tools
manage data model in data view i select
the cell with the measure come up to the
formula bar i'm going to call this
average transactional sales hopefully i
spelled it right and enter
and i want to increase the decimal so 1
2.
now we don't have to close this we can
actually leave this open i can just take
my cursor and click back in the pivot
table and look at that that measure
updated if we had used this in many
different pivot table reports they all
would update and now let's do check mark
for number of transactions and there it
is for region we have three different
dax formula calculations if we go
looking active we can see all the fields
we've used now let's come back over to
all and i want to create the next pivot
table so about four cells below not alt
n v t for table on the worksheet we'll
use alt
n v and d for data model d
and because the ok button is selected we
can just hit enter
now this time i want a sales rep but i'm
going to right click the table and force
it right up front to go to the active
tab
transactions also
now i can come over to active and that
is a thing of data model pivot table
beauty we're allowed to have multiple
tables in the pivot table field list to
avoid all those x lookup functions we
definitely hid fields that we don't need
that makes this less cluttered and we
created reusable formulas called dax
measures that even have number
formatting so for our sales rep report i
simply check that to get my unique list
and then check check check i have all
three dax measure calculations
i didn't have to manually number format
any one of those columns now let's
create our third report for product and
this time we're going to do the keyboard
alt nvd and enter
we're going to send product over to
active f transactions over to active
active tab that is a thing of data model
pivot table beauty i'm going to check
check check check
and there's our finished product report
now i'm going to move this to the side
select cell i 37 alt n v d enter
d date right click show inactive f
transaction showing active come over
here
we're going to drag year to rows
month down below years
and what is happening here the months
are sorted alphabetically in a standard
pivot table there's a behind the scenes
custom list that sorts the month when
you drag it into a standard pivot table
but in a data model pivot table that
feature does not exist to get our months
to sort chronologically we have to go
back to data data tools manage data
model
in data view we go to the date table
we select the month column and remember
this has text but month number
1 to 12
if we could tell this field right here
to always sort according to this field
then of course january would come first
february 2nd and so on in all data
models when you have a date table like
this you have to use the sort by column
feature in powerpivot it's home sort and
filter sort by column button you select
the column to sort month and by month
number when i click ok
when we go back to the worksheet the
months are sorting chronologically now
to finish our report we check
three measures
we need to change the column width and
i'm going to double click between any
two to best fit the width of each column
and just like that our monthly report is
done
now let's control s to save now we got
to talk about how these one two
three reusable dax formulas make their
calculations in each row of each report
if we look at this second report right
here
total sales there's the formula when we
drop this measure down into the values
area behind the scenes it calculated the
correct amount for ahmed shantell gigi
and it got the correct grand total at
the bottom and the way it did it is with
something called filter context and what
filter context does is when this measure
hits this row it sees the condition
ahmed that whole f transaction table is
filtered down to just the records for
ahmed and because the records are just
for armed when some sums that filtered
sales column it gets the correct amount
when it gets to the chantel row the
entire fact table is filtered down to
just the transactions for chantal and
then the measure adds just the sales for
chantel but when the same measure gets
to the grand total row there are no
filters on the table and so it adds to
get the correct grand total now the
whole filter context process is
happening behind the scenes in the
powerpivot engine and columnar database
so there's no way for us to go look and
see how this is working but guess what
the process is exactly like the
filtering that we learned at the
beginning of this video now i made a
visual to help understand how filter
context works on the sheet filter
context visual but remember when we
created this report we dragged sales rep
from the sales rep table let's go look
at filter context visual
now here's a picture of the report we
were just talking about and here's the d
sales rep table and f transactions the
fact table and remember from sales rep
id
over to sales rep id there's a one to
many relationship and here's how filter
context works
this measure right here we drop it into
the pivot table but behind the scenes as
soon as the measure sees this cell the
context is the condition or criteria
ahmed because this is coming from the
sales rep column in the sales rep table
it's just like we learned at the
beginning of the video the process
filters to show just a med this single
condition filter then flows across the
relationship from the one side over to
the many side and sure enough all we see
is five every sale is for just ahmad
once the filter has been transferred
over to the fact table
that's when the measure kicks in and
adds all the sales now the reason that
dax formulas calculate this way is
because if you have a lot of data if we
send the filter over here the table is
much smaller
guess what this measure only has to work
across
ninety 7694 rows
instead of the full forty three thousand
two hundred and twenty two so filter
context make sure that the formulas are
only working over the necessary number
of records to make its calculation when
the measure gets to the second cell here
chantal
in the d sales rep table
now it's filtered to chantal the filter
flows across the one to many
relationship all we see is fours all
these sales belong to chantal
and that's our result and of course when
we remove all filters from the d sales
rep table there's no filter on the fact
table and there's the total and for
our count rows function right here now
you understand why the function is
called count rows all this formula does
is look at how many rows are in the f
transactions when there's no filter it
sees forty three thousand two hundred
and twenty two when we select gigi
far fewer looks like three thousand
three hundred and sixty three rows so
count rows counts how many rows sum adds
all the sales and the average formula of
course averages these so the moral of
the story filter context is amazing
because it makes dax measures work more
efficiently now i want to go over to the
sheet mistake now we want to look at two
potential pitfalls when you build a data
model now we were careful and built our
data model to avoid these two pitfalls
but i want to show them to you so let's
go to data tab
over to data tools manage data model
and in diagram view
we very carefully hid the date field
that way nobody's going to drag it into
a report if you drag it into a report
something really bad happens to the fact
table
also
we hid the sales column because we
didn't want anyone to touch that we want
them to use measures but if you don't
hide it and someone drags it into the
pivot table another potential bad thing
happens so i'm going to select sales
hold control select date right click
unhide from client tools
now let's go back to this sheet and
we're going to use our keyboard alt nvd
and enter over the pivot table field
list i'm going to right click f
transaction show inactive over here
we're going to mistakenly drag date in
and watch this when i drag it down to
rows watch how long it takes
all right i'm going to drag off
everything but year
and now i'm going to drag the sales
field this is not a measure drag it over
values and already were annoyed
because it certainly didn't add any
number formatting but now let's go look
at the data model
we want to go to data view
and this is the fact table and the
product id is supposed to be the last
column what happened here
when you drag a date field into a pivot
table it thinks you want to group the
dates the problem is this is 43 000 rows
for each one of these columns that's too
many formulas
when you use a date table and a
relationship we only have two years so
this is only
720 rows
back over here the other thing is when
we drag that sales a number field into
the pivot table
powerpivot actually created a dax
measure but it's hidden if we go to
advanced and i don't know why they don't
have this on by default we're going to
click show implicit measure and sure
enough this is called an implicit
measure this is the one automatically
created by the pivot table this is
called an explicit measure we created it
ourself now there are many problems with
implicit measures the first is well it's
hidden
second is this is a read-only formula
you can't edit the formula you can't add
number formatting notice this is grayed
out you can't change the name you can't
reuse it from the pivot table field list
and when we export our data model to
other tools like power bi desktop
implicit measures do not show up so our
rule is we don't use implicit measures
we always want to use explicit measures
which just means we create them
ourselves now the best thing about
implicit measures is we unhide them and
then right click delete
yes i'm going to delete from model i'm
also going to select date
hold shift click on date month and right
click delete columns
yes
go to home
over to view diagram view select date
and sales and right click hide from
client tool
now there's another way to prevent the
date field from creating all of those
extra columns
attached to the fact table if you go
over to d date and this works in power
pivot and in power bi desktop we want to
go to the design tab and we're going to
tell powerpivot that this is a date
table as soon as we do that it will no
longer add those extra columns to the
fact table so design calendars mark as
date table so i click and click mark as
date table
it wants to know where the unique
identifier is that's our unique list of
dates so that's correct click ok and now
no matter what we do we're not going to
get all those extra columns with 43 000
formulas in each
all right let's go back to rdm dax all
right we saw some great advantages with
a data model pivot table using power
pivot by having multiple tables we were
allowed to create a relationship and
avoid all those x lookups with multiple
tables we can drag and drop from any
field into a pivot table we have
reusable formattable dax measures we
also hid a lot of unnecessary fields we
saw that filter context helps the
formulas calculate more efficiently and
guess what if we have a data model
inside an excel file built by powerpivot
we can automatically send it over to
power bi desktop where we have lots of
interactive visualizations that are
actually a lot nicer than what we have
in excel so that's our next task now you
want to make sure and save this excel
file with a data model and before we go
over to power bi desktop which is a
different program in this excel file
let's go to the sheet bi desktop now
power bi desktop does these things first
it's a free microsoft download you just
click this link and download power bi
desktop to your computer and when you
get it open and start working power bi
desktop has the same tools as power
pivot and power query in fact all the
tools in power pivot and power query
started in excel first and then they
invented this other tool called power bi
desktop which is designed to have better
visuals and shareability now power bi
has more varied visuals and reporting
tools than in excel and the visuals are
interactive there are also more dax
functions including table functions and
once you create a data model in excel
powerpivot you can import your
powerpivot data model into power bi
desktop now power bi desktop is the free
download that's where we build the data
model and all the visualizations once
you have that file complete you can
share that file the traditional way by
emailing it to someone and a lot of
people in the world do it that way but
there's a second tool called power bi
online
and power bi online requires buying a
license now this is august 18 2022
and by this time in history many
entities buy power bi online for
employees when they buy microsoft 365
office and here's what the online
version does different than power bi
desktop you can upload your power bi
desktop file to power bi online and then
it's easy to share the reports visuals
dashboards and the data models also you
can upload excel files and the
powerpivot data models
inside of excel files now the sharing of
reports and visuals that's on the
information side that's when you want to
share your results with people but
there's a whole other side to power bi
online when we upload data models from
power bi desktop or from excel
powerpivot they become universally
available to assigned groups of workers
or here at highline college an assigned
group of students and teachers that
means when you upload data models groups
of workers can access the data from
within excel
or even power bi desktop
this means there's one source of truth
one location for the data no need to
share files by email now let me just
show you we already saw an example of
this if you go up to insert tables drop
down for pivot table there it is
once you upload your data model all your
fellow workers have access to it no more
email in your file with the tables or
the data model you just upload it here
and then everyone in their different
office or here at highline different
classrooms they just go to excel log in
and they can access the single source of
truth data set
now in power bi online the way you share
things is you create online workspaces
for groups of workers to share reports
visuals dashboards and data models and
all it involves is just adding the
emails for your fellow workers or fellow
classmates and then you can share your
data models visuals reports and
dashboards now we'll come back to this
excel file
a little bit later
and upload the data model and then we'll
see how amazing it is to have this
universal place to save data but what
we're going to do next is we're going to
take our power pivot data model from
this excel file and import it into a new
power bi desktop file and then we'll
look around and get introduced to power
bi desktop however before we do that
please control s to save this excel file
and then close this excel file before we
go over to power bi desktop now i've
already downloaded power bi desktop i
have an icon to open it on my taskbar
and i can see the folder path with this
file that's the file that contains the
data model
so i open a brand new power bi desktop
file now here's the power bi desktop
file it doesn't have a name and it
hasn't been saved so we use the same
keyboard as excel to open save as
f12
in the save as dialog box we're going to
navigate to our class folder and we're
going to call it
this is what i'm going to name it you
can name it that also
and then click save there's our name in
the title bar now before we take a tour
of power bi desktop i want to import
that power pivot data model to do that
we go to file
import and this option power query power
pivot and power view we're going to
click in the open dialog box we navigate
to our class folder and there's our file
that contains the powerpivot data model
let's click open this message says hey i
cannot import everything from an excel
file i can only import queries and
what's in the data model now when we
click start right here we're going to
get all the queries from that workbook
there are a few we don't need but we'll
easily delete them let's click start
and the migration is complete here's all
the queries
here's the data model items let's click
close now we could take a proper tour of
power bi desktop this is the power bi
desktop ribbon it has tabs
and over here one two three those are
the three areas we can work in in power
bi desktop if you hover this model is
like diagram view in power pivot let's
click oh and there's our imported data
model
it got everything
i think if we look around here
adjust things the table the fields the
measures the relationships
and there's a few differences over here
look at that icon for a measure it's
like a little calculator instead of that
f of x in power pivot
and instead of a hidden field being
grayed out there's an eyeball with a
line through it so this for us in power
bi desktop is called model over in power
pivot diagram view now if we hover here
this is data this is like data view in
power pivot so when i click i can see
the tables over here there's a list of
tables i can click to expand and i see
the fields and measures
i can click on f transaction and there's
the table i also see the measures so
data is like data view over in
powerpivot and here this is report when
i click
this is like the worksheet the worksheet
would hold data model pivot tables and
excel charts and and for that matter you
can put anything else from excel in the
worksheet here in report view over on
the right here are visualizations we can
choose from we can open tables and drag
and drop fields and measures into our
visualizations there's a filter area
also some of the possible visualizations
and reports well there's a bar
there's the other type of bar cluster
stack column clustered column there's a
line chart x y scatter there's a cool
map there's a table a slicer and this is
similar to a pivot table a matrix
there's also a bunch of other different
visualizations and get more visuals is
awesome you can go and download all
sorts of amazing visuals
so we have report
data and model but where is power query
well home tab everything in data and
queries that's power query in excel we
have the queries and connections pane to
open queries here in power bi desktop
queries transform data drop down
transform data notice it says this opens
the power query editor also notice this
is data source settings that same
setting in excel is data get data and
all the way down to data source settings
let's go look at power query editor it
looks almost the same as over in excel
if we select the query f transactions
there's the preview name and applied
steps now we don't need a bunch of these
queries we're going to collapse this
folder before we can delete it though we
have to delete the query many files from
one table because it is using elements
from in that folder click hold shift
click on summer business 101 scores
right click delete yes i want to delete
the queries right click the folder
delete group
delete now over here in power bi desktop
it does not say close and load to over
in excel we get to choose where to load
it here in power bi desktop we can only
load it to the columnar database in the
data model so close means to close the
window apply means to apply the steps
and load it to the columnar database
there's the drop down but for us we just
click the button close and apply
and the steps are applied next we want
to build our visualizations and reports
we want to create this sales report page
that shows region product sales in a
crosstab report it also visualizes the
sales for sales rep using a column chart
and then using a line chart we want to
see the trends over the last two years
but the thing that distinguishes power
bi
is if i want to analyze the trends for
chantel and see the cross tab with just
chantel sales i simply click and that is
amazing there's the trends for chantal
and the whole table region and product
those are all chantal's number that's
about 3.9 million sure enough that's the
column height and if i want to remove
the filter i simply click again
i come over and i'm interested in
spitfire when i select spitfire this is
highlighting just spitfire sales for
each sales rep and here's the trend
click it again and the filter goes away
now the first visual we'll create is a
line chart i'm going to click somewhere
in the white area go over to
visualizations and there's the line
chart click
we can resize it
make it a little bit taller and moving
over here has a different cursor and
sometimes it's actually kind of hard but
it's that white cursor
look at that red line it means i'm right
in the middle i'm going to let go in
visualizations when we're in build
visual we can drag and drop fields and
measures to these areas of the chart so
we'll have year in the x
month below year
and then total sales is going to go on
the y-axis now before we format and do a
few other things let's talk about this
awesome area here these three dots give
us more options if we click we can
actually export the data from this
particular visual as a csv file
show as a table if we click that there's
a table down here we have a back to
report button we can remove and
spotlight spotlight doesn't work here
because we don't have any other visuals
but it would highlight this and gray
everything else out and then we can
uncheck spotlight
we have focus mode which expands the
particular selected visual we can click
back to report we have a filter button
that will tell you how it's filtered if
you have filters we don't have any and
then we have
one two go to the next level buttons and
one two drill buttons
now we're already drilled up to the
highest level if i select drill down
this just turns it on so when we click a
particular data point it shows at the
next level all of those data points that
make up 2021 so when i click there's
january to december for 2021. drill up
i'm going to turn this off now go down
the next level if i click this for a
year month this may or may not be an
appropriate chart because that's all the
januaries all the februaries and so on
drill up this is probably the
appropriate one it'll go down a level
and show year and month
now we have the year repeated many times
i think i'd like to see just one these
labels have been concatenated
over in format
that's our little paint brush there's
different areas and if you're coming
from excel it does take a while to get
used to where everything is we want to
do something to the x-axis if this was
collapsed you could open up values and
sure enough down here concatenate labels
we're going to turn that off we can
collapse this it would be nice to have
data labels so down here data labels
we'll say on now let's go back over to
build visual and here's something that
does exist over in excel in a pivot
table if you hover over a point it shows
you a tool tip but over here in power bi
desktop we can do better than just
showing the amount that's in the
vertical axis
build visual down here is tool tips we
can drag other measures here's number of
transactions
and when we hover both are listed in the
tool tip we drag average
down below number of transactions and
that is beautiful every one of these
tool tips shows three measures and
they're calculating the measures based
on the conditions and criteria at this
point so this would be 2021 march now
when we created the tool tip
that title is too long and we don't
really need it because we have total
sales over here
so let's go over to format
and there's a visual and general
here's the title we'll select off all
right so we have our line chart next we
want a matrix and i got to show you a
gotcha i do this all the time i want a
matrix right here but i accidentally
leave the line chart selected so when i
come over to build visual i'm thinking
i'm going to get a new matrix but when i
click it it's polite it thought i wanted
to convert that line chart to a matrix
luckily control z gets us back to our
line chart the trick is when you create
a new visual make sure that the white
area is selected and now let's do a
matrix we can resize it
something like that and when we move it
around we can see those lovely red lines
and in build visual it looks sort of
like a pivot table task pane rows
columns values so we'll take region from
f transactions that's going to be in the
row there's our unique list
product we'll put that in the column
there's the unique list and we'll drag
total sales down to values
and there's our cross tab report showing
region and product
now let's click in the white and create
a clustered column
we want sales rep we can check this and
it will go to the horizontal axis our x
and then total sales to the y
let's add tool tips here also some
number
of transactions and average
we don't need this long title
so over in format general
we'll turn the title off back over to in
format visual
let's have data labels
and now when we hover we can see our
three measures and here's one of the
things that makes power bi desktop so
beautiful when i click the line chart is
filtered and so is the cross tab report
now we click chantel to unfilter now
we're allowed to control how these
visuals interact we go up to format then
click edit interactions
so we have the column chart selected so
i'm allowed to on the other two visuals
decide whether i want it to filter or no
i do want it to filter same here i want
it to filter
selecting the line chart
we get to decide filter or not but over
here we get a third option highlight and
that's the one we want
that means when i select an item over
here like spitfire
it highlights just the amount leaving
the total there for easy comparison and
then this one of course is filtered
clicking spitfire to turn it off we can
uncheck edit interactions over on the
right we have the filter pane and this
filter does three things it can filter
on the selected visual it can filter the
entire page meaning all the visuals or
reports and you can have filters on all
the pages so if you have new pages or
tabs with other visuals and report a
filter here filters them all
if we select the line chart here's year
for the selected visual we can open it
up we could select the logical test
equal to which is is and then type
i want to see just 20 21 and then you
have to click the apply filter button
and bam
just this visual is filtered for 2021.
to clear the filter you can use the
eraser now we would like to do a few
more things to this report we want to
add a title a slicer and a card showing
total sales i'm going to move this a
little bit to the left let's move this
down
we'll move this down
all right over here let's click in the
white and we're going to start with a
slicer and we want year
then from the drop down we can say
list
resize it now click in the white and
we're going to have a card and we're
going to drop the total sales measure we
can resize it move it up a little bit
end up in format
call out value and we'll say
value decimal
one
click in the white
we'll try it text box and we can write
colorado boomerangs
that's the name of the company sales
report
control a let's say 28
bold
we can resize it there we go
and center
click off to the side
now we can select
2020 it filters everything on this sheet
2021
and erase
so the sales report is looking good but
we want to create another page and show
some different metrics for the region so
we're going to add a new page double
click and call it region report
all right we click in the white come
over to our visualizations we want a
matrix and we're going to put three
variables in the row we'll start with
the region field from f transactions
and then year we're dragging it down
if we check these they tend to go to
columns so i'm dragging
and month below year
now if you don't see these expand
buttons you can come over to format row
headers
and turn it on or off
back over to build we want total sales
down in values actually we can check
these and number of transactions
we'll expand this let's increase the
font size for the whole matrix over to
format
grid
again sometimes these things are hard to
find you wouldn't think it's grid
options but sure enough global font size
and i'm going to increase it to 12
resize it bring it all the way down to
the bottom
actually we're going to have a title at
the top so maybe right about there
right click
expand to next level
so that's how we'll leave it and then
as we're analyzing over here we can open
the years
now let's click in the white and we want
two regional bar charts one for product
one for sales rep now we're going to
look at the difference between stacked
and clustered we're going to look at bar
charts but the difference is the same
for stack column and clustered column
let's start with stack bar
bars are always horizontal
now we want region in the y-axis
and then product in the legend
and then we'll check total sales
there's a stacked bar and it's called
stack because the items in the legend
are stacked one on top of the other now
you use a stacked bar chart like this
when you want to directly compare the
variable in the axis using the lengths
of the bars it's very easy to see which
one of these regions is the biggest and
the smallest so the emphasis is on the
variable in the axis rather than the
variable in the legend but if we switch
to clustered bar
now it's very difficult to compare the
totals for the variable in the axis
but it's very easy to compare the
differences across the product or the
item in the legend using the column
heights and it holds exactly true if we
switch over to stacked column it's this
variable down here that's being
emphasized by easily comparing column
heights if we switch over to cluster
column the emphasis is on the items in
the legend so we can compare within each
region the items from the legend now
let's go back to stacked bar
now if you want to change the colors of
the columns you come over to format
down to bars and there you can change
all the colors i'm going to leave these
colors just as they are
pull this up
by the way um i like the title i like
everything this came out virtually
perfect let's add some data labels a
data label so over to format
down to data labels
and on now watch this here's a great
trick i need the same exact chart but
all i'm going to do is change the item
in the legend
make it a little bit less
tall control c click on the white
control v
there we go and with this selected
we'll remove the legend and watch what
happens there's actually no difference
between stacked and clustered when
there's only one variable but we want to
go up to our sales rep table and check
sales rep and now we have two beautiful
charts region sales rep region product
and here's how amazing this whole setup
is if i want to look at just northwest
there's all the sales reps and the
products and it shows me the detail over
here i can expand to see a particular
year
click it again
we can click on the outside edge also to
unfilter now if we're going to use this
as a filter and we only have one bar
instead of highlighting let's edit the
interactions and filter this i think
it'll look better
format edit interactions we have this
visual selected so i'm going to come
down here and say filter and select this
one
and change this to filter
now we can turn it off
and now when we click northwest
that's looking better
click off to the side
remove the filter and if we select sue
for west that is looking good and the
cool thing is if you add up all those
amounts it's exactly equal to 0.62
million or the total down here now i'd
like to see an excel chart do this
this is why if we have a data model over
an excel power pivot might as well send
it over to power bi desktop and take
advantage of some of the interactive
visuals
next we want to add a title to our
report
text box
[Music]
i'm going to click on the outside edge
down to effects
and background color i'm going to add a
very light gray
because the next thing we want to do is
i want to insert the colorado
boomerang's logo
insert
over to elements image
and in our chapter 4 folder there it is
colorado boomerangs double click we can
resize it and that is looking good
now there's one last thing i'd like to
do actually i want to show you something
about formatting now i am going to
change the colors here i'm going to go
over to format down to bars and i'm
going to change this to a medley
of blues
so i'm using all these related blues
and that's looking pretty good now i do
all my formatting manually
but in power bi desktop excel word
powerpoint all the microsoft tools you
can go up to view and i have no idea why
this is not in format
and there are themes now themes will add
formatting to everything you can look
through these
also you can browse for themes online
but if i select this title it formats
everything for me now again i don't use
themes but you might like to now i'm
going to control z
and there's our finished regional sales
report now our next task is to publish
this power bi desktop file and we have
two pages with two reports and the file
itself will show up at power bi online
listed as a report we want to also
publish our excel workbook file and when
we publish this it'll be listed over at
power bi online as a workbook but before
we publish either one of these files you
have to have a power bi pro license or
hire and you have to be at an
organization that assigns emails to
everyone in the organization like a
business or here at highline college and
the way you log into power bi online is
you have to use your institutional email
so here at highline the students in
business 218 class and teachers and
administrators they'd have to use their
highline issued email to not only login
to power bi online but also into power
bi desktop and of course your excel file
now the dialog box for logging into
either one of the files or power bi
online is exactly the same so we're
going to jump over and look at power bi
online first before we upload these
files now there's lots of ways to get to
power bi online one of them is to use
this address you could also get to it
from your power bi desktop file your
excel file or even outlook now i'm going
to type powerbi.com and enter it wants
your email and it reminds you here about
organizational email so i have to enter
my highline college email and enter and
this is the page that all apps and the
website will make you go through you
have to go through your institutions
login system i'm lucky i have an easy
password it's just a bunch of dots but
when i hit enter it opens power bi
online now here's the home page and it
may have some recently used reports or
workbooks or workspaces here there's
also some items here but what we really
want to do is to go over to workspaces
and each one of these workspaces is a
location that has designated emails so
certain people can access each one of
these workspaces now i'm going to first
show you how to create a workspace which
is just a few clicks
i'm going to call it fall 2022
business 218.
now i added a description and uploaded a
picture now i click save now we have no
content in our workspace yet to add
emails we come over to access and you
enter email addresses now i added one
person from our entity linda quick also
we get to decide what type of
participant if you want to learn more
you can click this and there's a
detailed list we're going to add
contributors which allows us to do all
the things we want to do create edit and
upload reports and workbooks
so it changes to contributor click add
we have two people in the workspace we
could add lots more and actually for
this class i will add all the students
in fall business 218. now let's add some
content that means we'll go back over to
those two files now you do have to sign
in to both files but once that's done we
save our file we have our two pages and
then in home
we have the publish button click
and of course it wants to know where is
the destination
there's our destination
click select it's publishing and if you
don't have power bi online open they
provide a link you can click this to
jump over now it opens in power bi
online and shows you the report you just
published but over here on the left we
can click on our new workspace there's
the report that's the power bi file and
the data model that was in that power bi
file is called a data set content
that'll show you your reports and
workbooks data sets and data flows
that'll show you your data models
now we can also click to open up our
workspace we don't have any dashboards
later but that's a place where we can
pin things from reports there's our
report we don't have a workbook and
there's our data model now let's go look
at our report here in power bi online
now it works the same as over in power
bi desktop we can click to filter and
click to unfilter over on the left we
see our pages
i select region i want to see quad in
the east filters perfectly and then
unfilter now anyone with access to the
workspace can view the report but
there's some other options up here you
can save a copy that actually saves it
online download this file this will
download a power bi desktop file you can
print the page you can even if you have
a website embed code click this and you
get the code there's also a generate qr
code
over here for export
we're not going to use this one because
we'll open it up from inside of excel
but you could export it to powerpoint or
pdf you can also share this report you
can copy the link and email it to
someone
directly email from here
even teams in powerpoint now we want to
go back over to our excel file now here
we are in our excel file and when we
publish this to power bi online the
worksheet data will show up as a
workbook
none of the queries will show up and the
data model will show up as a data set
that we can use in various ways now in
our second power query example i
actually added some formatting and a
title at the top just to tidy this up
because in power bi
online we're actually going to pin this
report to an online dashboard now save
this
and here's how we publish we go up to
file
publish
and there's two options upload your
workbook this allows you to interact
with your excel workbook but not in
excel it is in excel online which is a
very limited version of excel
and of course you can pin selections
from the workbook and share
this is the one i usually do this will
just upload what's in the data model but
i am going to show you this one so
before we click either one of these you
have to choose a workspace
and there it is at the bottom fall 2022
business 218. now we're going to click
upload
down at the bottom you can see it's
working
up at the top it gives us a yellow
banner and we can jump over to power bi
online so i click i got this message but
i'm going to click ok
then we're going to go look at our
workspace open the drop down and i don't
know what that message was saying
there's our workbook now when we click
on the workbook again it's only going to
show us just the worksheets not the
queries and it opens in excel online
this feature is just for us to view
stuff you can see all the tabs below
that was built with a query but the
query is not here but the result is
let's go back over to pq2pt
if we look at data sets now we have two
and we uploaded powerpivot data model
to our power bi desktop file so these
two are the same
and later in excel we'll see that now we
have access to these so we can use this
as our one source of truth and build
pivot tables over in excel now before we
do that we want to look at one last
thing over here in power bi online
dashboards that's just a location where
you can pin
anything you want from any of the
reports or workbooks you have now the
word dashboard is very common it just
means one location where we can present
useful information and metrics for
making decisions but dashboards are not
limited to power bi online you can
absolutely make a dashboard in excel and
in fact this page that we made in this
power bi desktop report
this can be called a dashboard also but
when we're in power bi online dashboards
are a specific location where we can pin
important information and then share
those dashboards easily so in summary a
dashboard is one location where you can
present useful information and metrics
for making decisions information is
presented in a neat and organized manner
dashboards can contain tables charts
visualizations data validation pictures
and other visualizations of data and
when new data arrives the dashboard can
be refreshed just like a dashboard in a
car a dashboard should present
information that is required to make
good decisions now to see how power bi
online dashboards work let's go to this
report i want to pin this to a dashboard
and we don't have any dashboards but it
will ask us to create one
so we go up to the top more options
we want to pin this to a dashboard
we don't have any dashboard so it's
polite it wants us to create a new one
i'll call it colorado reports and pin
now you can also create a mobile layout
and we're not going to do that but you
click on that and drag and that
determines how it will look on mobile
devices there will also be this web view
also
and there's a dashboard so we can click
and sure enough this has been pinned to
this dashboard it works just like if we
were looking at the actual report
i'm going to go back to our workspace
and go back to the report
regional report we'll go ahead and pin
this one also pin to dashboard
there's our dashboard pin live now i'm
also going to pin something from an
excel workbook although the way it works
is clunky so i don't usually do this but
you never know when you have something
in an excel workbook that you want to
share
i'm going to highlight
and this one's way over here we see pin
and it will pin this selected range
we're going to put it right in the
colorado reports dashboard and pin
now we can go look at our dashboard
so this is working here
we have this one it's working also
and down here we have a picture of our
report and chart we created over in
excel now if you click this it jumps
over to the excel workbook so that part
of it seems clunky to me
we're going to go back over to our
dashboard scroll down and the little
options here i'm going to say delete
tile
now the advantage of the dashboard is
that of course you can take various
things from various reports and
workbooks and then you have some options
up here you can save a copy that saves a
copy of the dashboard print it you can
share it i'm going to uncheck allow
recipients to build content with us send
an email notification
and up here
i'm going to put an address and then
grant access now i'm on my cell phone
and i'm looking at my email there's the
invite i'm going to click it open this
dashboard now when you click open this
dashboard if this is the first time
you're viewing on your phone it'll ask
you to download the app and then log in
using your organizational email and sure
enough i click the dashboard and there
it is and i can't really give you a good
view but it is interactive i opened this
one report and on the phone it is
interactive just as if we were in power
bi desktop or the web version of power
bi online so i'm filtering and then
unfiltering
that is why power bi online is so
wonderful for sharing
now one last thing i use outlook online
all the time and up in the left hand
corner there's an app tile you can go to
online excel but this is how i usually
get to power bi online all right so that
was a lot of fun with power bi online
and power bi desktop now that we have
two data models stored at power bi
online
next we want to see how to connect to
our online data models in excel and
power bi desktop i've opened a new excel
file saved it with this name to our
folder i named the sheet and i'm logged
in
and you are not going to believe how
cool this is i get to go
to insert tables pivot table drop down
and there it is or i can use the
keyboard remember alt n v that opens the
drop down and there it is b is for power
bi online so tap b
and look at this it opens up a task pane
called power bi data sets
now i'm the administrator and have all
sorts of different workspaces over there
so i have access to all sorts of data
sets but if you didn't see the data set
right at the top you simply type 04 dash
m365 and enter
and there it is right at the top this
one is from our power bi desktop file
and this one's from the excel file i'm
going to use the excel data model and
click insert pivot table and here's our
pivot table field list and this is the
data model that we're connected to
online these tables were not imported
this is only a connection to that one
source of truth sitting online at power
bi online if you go look in the data
model there's nothing there
and this is nice we have the measures at
the top the three dimension tables and
that one loan field from the fact table
is listed down here now i'm going to
select
year region
sales rep
year went to column so i'm going to drag
it above rows
and then check check check and there i
have created a data model pivot table
with that lovely number formatting
automatically coming from the dax
formulas one other note
these were explicit measures that means
we authored these measures as we
mentioned earlier if you create implicit
measures either in the excel file or the
power bi desktop file those measures
will not show up when you connect to
this live data model data set from power
bi online now i'm going to control s and
we're going to jump over to a new power
bi desktop file i opened a new file
saved it with this name and the data
source here we go to get data and there
it is power bi data sets and notice it
says create a live connection to data
sets in power bi service so i click
i see the one i want right at the top so
i'm going to double click
and over here in report view we see all
of our
tables including f transactions with one
field and three measures now i'm going
to hit pause and create a new page you
can create any page you want and then i
created this dashboard click and it's
all filtered click again
unfiltered
and if we go to model it shows us a
picture of the model but that data model
is not really here well we have model
and report
well there is no data tab because the
data isn't here it is still stored in
the power bi online website so that's a
pretty cool use for power bi online
store a data model online and access it
from anywhere now we're back in our
original excel file we're on the sheet
refresh eight and nine and this is a map
of our data connections for example
eight and nine
here's the file this file has the source
data the excel tables that we then
imported into this file then we built a
data model and from power bi desktop
imported it into this file then when we
publish both of these files to power bi
online those data models were stored
online and then we connected both of
these files to the data models online
that means if we go add some data to
this file and refresh all the way
through
everything will update so we're gonna go
open this file here's our file we have
one two three four source tables and we
have two things to do we're to add new
transactions for a new year to the fact
table and then we're going to update the
date table
click in a cell control down arrow
scroll down i put new transactions here
click in a single cell do not include
the field names control asterisks
control c
and below the table we control v
instantly those records are incorporated
into this table object control home now
the date table has to have a unique list
in this first column of every single
possible date from the fact table well
right now if i control down arrow this
table only goes to the end of 2021 now
i'm going to show you a killer trick
here when you have your date table in
the excel worksheet now these are actual
dates but look up in the formula bar
month
text
and year these are all formulas this is
an excel table so if i can just quickly
add the whole new year of dates
everything will automatically be copied
down now here's a great trick you select
the last cell with the last date
there's the fill handle
hover your cursor and when you see your
crosshair cursor you're not going to
click you're going to right click so
here we go right click
drag one cell down
back
and when you let go you get a secret
drop down let go and there it is down at
the bottom series
now there's other ways to get series but
this is the fastest i'm going to click
and whatever you do don't leave it rows
because that'll shoot the dates to the
right we want to shoot them down the
columns
step value is one day at a time and all
we have to do is put the end date 12
slash 31 slash
2022
and when i click ok
that is a thing of beauty that's using a
series trick formulas and an excel table
and that's it now we have to save this
control s
two of our tables have been updated i'm
gonna close this file now in our excel
file we go over to the worksheet rdm dax
the data was imported by power query all
of this is coming from the data model so
all we have to do is right click refresh
and there we go
there's the complete new year and all
the amounts have updated in our power bi
desktop file we go up to home queries
and click refresh
it's refreshing
and there's the new totals
now i had to adjust the font size but
that is looking good let's go over to
sales report
wow look at the trend and for some
reason the summer time june and july
wasn't nothing but now that is a huge
jump control s
and now we click publish to send the new
version to power bi online select our
workspace select it's polite it's asking
if we want to replace and we do
we need to republish our excel file file
publish be sure and select the workspace
and upload
i definitely want to replace
and then the beautiful thing is over in
power bi online when i go look at our
dashboard
look at that it is updated
so is this
now when we open up our power bi desktop
file that was connected live to power bi
online
it's already updated
in our excel file that's connecting to
power bi online we have to right click
refresh
and there's our new data
control s
control s
all right so example eight and nine we
learned all sorts of things how to use
power query to import data how to build
a data model import a data model from
excel into power bi desktop upload to
power bi online and even connect to
power bi online data sets now our next
example we're going to use power bi
desktop but we're going to connect to an
online database with 7 million rows
let's go over to the sheet
10.
in this example we're going to use power
bi desktop and power query to import 7
million rows of sales data that's a lot
of data now for some of you in this
class you're like we never get that much
data well you do get that much data for
big companies seven million rows is not
even big and then we're going to build
an interactive gross profit dashboard
now we're going to go over to this next
sheet
and talk about what we're going to find
when we import the data from a
structured query language that's sql
database
we're going to have a fact table with
date product the website where the
product was sold the country code where
the product was sold how many units were
sold for the transaction what the
revenue discount was so for this
transaction right here for every one
dollar the customer got 35.3
pennies off for every one dollar and
then there's net standard cost in this
case for every one dollar of cost from
the lookup table the charge is 1.03 so a
dollar three that means the price was
increased by three percent here the
price was the same down here 0.99 that
means there is a one percent discount
now it's important to understand how
these columns are working because we're
going to have to calculate revenue a
synonym for sales and cost for each
transaction the next table uh dimension
table there's the what's called the
primary key country code that will be
hooked up with this foreign key this is
the one to many side and then this is
the product table with retail price
standard cost and the category for the
product primary key foreign key this is
the many side this is the one side we'll
have to look up and use both the price
that's for selling it to the customer
and the cost that's for the business on
the expense side the cost side how they
calculate expenses now this table is 22
rows this is 126 but this is 7.7 million
rows
now let's go talk about the columnar
database because when you have 7 million
rows that columnar database makes the
impossible because you could never have
7 million rows in an excel worksheet it
makes the impossible possible now just
as a small example this table right here
has 606 rows it has four columns that
means the total number of cells or bits
of data is 2
424
but what a columnar database does is it
takes each column and stores them as
individual columns but only stores a
unique list so product id is going to
have 7.7 million rows but when it gets
stored in the columnar database they're
only going to store the unique items now
there's also a behind-the-scenes map
that determines how many rows there are
that it can use to reconstruct when it
makes calculations but on the storage
side our table over here that originally
had over 2000 rows when we count all
these unique lists and add them up
there's going to be 621 bits of data or
cells in the columnar database now this
is only with a table that has 600 rows
so you can imagine with 7 million rows
that columnar database is going to
compress and store it efficiently now
let's go over to the next sheet
this is a diagram that's going to allow
us to calculate the sales for a
transaction we're going to have to use
the product dimension or lookup table
and we're going to have to use the fact
table so for this yellow transaction
here there were four units sold
the revenue discount they only got 1.9
pennies discount for every one dollar so
that's why in the formula you take the
one dollar minus the discount then in
our formula we're going to have to look
up based on this foreign key on the many
side product name we're going to have to
look up in the dimension or lookup table
the aspen product and then bring the
price back to the formula and that's how
we'll calculate sales for every line in
our table you can look through this if
you want and this will be our formula
since we're going to be doing it in the
data model we're going to use dax
formulas now round we know how to do
related is like x lookup x lookup is
more complicated when you do related to
look up a product price because there's
a relationship you just have to say what
you want that's it and then we multiply
by
well one and that's the name of the
column that has the revenue discount so
that's really this part and then times
there's the name of the column for
quantity and of course over here d
product retail sales
that's this field from the dimension or
lookup table now the last thing is
credentials we're going to be logging on
to an sql database and pulling the data
from online and the great thing about
storing the data online is it's not on
premise we don't have to worry about
those text files or excel files and what
the file path is power query is just
always going to be connected to that
online database which isn't going to
move but in order to get access we're
going to have to use these credentials
server database username and password
all right now we're going to open up a
new power bi desktop file our goal will
be to create these seven measures
this gross profit page
and using a map visual will plot gross
profit across the world all right we're
going to start our sql power bi desktop
adventure by opening a new file
saving it with a good name
and
step one is to get the data so we go up
to home data we could go to get data sql
server but there's a big button right
here so i'm clicking this sql server
database we have to enter our
credentials the server is pawn
dot highline dot edu
tab and the database is lowercase boom
data we're going to do a direct import
bring the data into here
let's click ok
be sure to say
database on the left the username is
excel is fun all lower case
and this is where most people get in
trouble you gotta spell this right with
correct capitalization
capital e
x c e l that's excel capital i s is
capital f u n and an explanation point
we definitely want to connect just to
server and database now we're going to
click connect this is not encrypted so
we click ok and just as in excel we get
the navigator window and we get to pick
amongst various objects do not import
the d calendar table it doesn't have the
right dates we want d country d product
and f transactions we do not click load
we want to click transform data now
there's three queries on the left f
transaction is selected
there's our preview
the name is perfect and we have these
two steps now we already reviewed these
columns and the data type should be
perfect because this is coming from an
sql database but we still have to check
and sure enough
revenue discount is not correct so
we're going to add a step click abc and
we definitely want decimal number as our
data type it added that step
we'll scroll over and when you import
data from a
relational database which just means
there's relationships these extra
columns come in with data from the table
on the other side of the relationship we
don't need any of these so i'm going to
click the first one hold shift click the
last one and right click remove
there's a new step the data types are
right this table is looking good now we
click d product data types are all good
and this is a column with every single
transaction from the f transaction table
for that product the relationship is
what brings that table in but we don't
need this column so right click remove
the name and the steps are fine d
country
looking good
right click remove
those are good
now we're going to click close to close
this and apply to send this to the data
model but when we click it's busy
working behind the scenes building that
columnar database so all these millions
of records will be compressed into a
much smaller space
and there it is done now let's go look
at data
and on the right there's our tables d
country d product and f transactions if
we go over to model
it already put the relationships in
there's our f transaction
d country from the one to many side
our product table
we have a unique list of products from
the one to many side now what we're
going to need next is a date table and a
date table must contain a unique list of
dates for every possible year from the
date field in the fact table if we go
look at our tables d country does not
have a date column d product doesn't
either
and in the f transaction table we have
one single day column and when you have
only one date column in the fact table
and there's no other date columns in any
of the other tables that will allow us
to create our date table in a much
easier way now we'll create the date
table in just a moment but this is our
fact table and we use power query to
import it into the data model but i want
you to notice that when you're in data
view and you select a column on the
preview for this table we're allowed to
change the format now this is the same
over in powerpivot also i want to show
this date
as a short date i don't want to see the
day name also notice that over here in
the preview in data view you can
actually change the data type on a given
column now when we import using power
query we don't want to set our data
types here in the data model we want to
do it with power query but in just a
moment when we create our date table
we'll have to use this feature to make
sure the data type is correct now let's
create our date table using dax formulas
and the way we do it is we can go over
to table tools and this is different
than power pivot power pivot we can
create measures and calculated columns
but we can't create tables here in power
bi desktop we're allowed to create dax
tables and they'll be loaded to the data
model
so let's create a new
dax formula to create a new table
up in the formula bar we type just like
over in powerpivot name assignment
operator formula the name for us is
going to be d date and luckily the
assignment operator is just equal sign
instead of powerpivot where we had to do
colon equal so equals and there's two
calendar functions calendar
is what you use if you have multiple
date columns and then you just specify
the start and end and it creates a
unique list but when we only have one
date field in the fact table we can use
calendar auto
if you had a fiscal year you'd put it in
but we don't so you just don't put
anything in and when i hit enter it
hunts through the data model finds the
min and max date and gives you a unique
list for every day for every year it
found in the date column in the fact
table now we select the date field the
automatic field name is date we have to
set the data type over here in the data
model because we didn't bring this in
using power query we definitely want
this as a date and i'm going to format
it
as short date now if we look over on the
right we now have a new table and it's
in the data model let's open we have one
field now when you have a date table you
have this unique list but then you can
add as many attribute fields as you
would like if we want year and month in
our reports and visuals we have to add
those to the date table you can also add
lots of other date attributes like
fiscal year fiscal quarter week and many
more now we want to go back to table
tools because this button right here is
really important for date tables we saw
this over in power pivot when we mark a
table as a date table
then the automatic date table creation
feature is turned off now we saw in
powerpivot that if we dragged a date
field into a pivot table it added all
these columns to the fact table and we
definitely don't want that but over here
in power bi desktop it's even worse if
you drag a date field into a visual and
you don't have your table marked as date
every time you do it it creates a new
hidden date table and you can't go and
look at them so i'm going to make sure
and mark this as a date table and then
tell it where the primary key or the
unique list is date okay now we can add
our extra fields or columns we're going
to say new column
up in the formula bar we definitely need
month number
equal sign and the function is the same
as over in excel
month and we want to access
using row context the date in each row
so i say dd
down arrow there's the date field tab
close parentheses and enter now this is
the first time we've created what is
called a dax calculated column
this over here is not a dax calculated
column this was a one column table but
this is a dax calculated column we can
see the formula in the formula bar and
dax calculated columns calculate
differently than excel worksheet
formulas and different than excel table
formulas notice up here that's the
complete column reference if we had a
column in the worksheet and used
worksheet formulas how would we access
each row we'd have to use a relative
cell reference
if we were in a table column we wouldn't
have the full column reference we'd have
the field name in square brackets with
an at symbol but over here in dax
calculated columns and also later we'll
see dax iterator functions they both use
a complete column reference and then
behind the scenes it's calculated using
row context and all row context means
for a full column like this is it can
see each item in each row so from now on
when we create dax calculated columns or
later iterator functions we'll just put
in the whole column reference and row
context will get the item in each row
now for each new column we create we
have to consider data types over in
column tools i'm going to check the data
type now that's fine but it is a number
if we were to drag this into one of the
reports it would try to add them but
this is one of the fields we're going to
hide so back in table tools
new column
this is going to be month
and whereas over in excel you use the
text function to create a month name or
other possibilities like day name but
over here in dax they decided not to
call it text they call it format
and so what format would do we put a
value in dd down arrow tab to get the
full date column and i want to format
each one so i type a comma there's the
format argument and then in double
quotes m means month d means day and y
means year now watch what happens and it
has to be in double quotes but i'm going
to put 1 2 3 4 m's for month n double
quote close parentheses and enter
that will give us the full name of the
month but that's not what i want i'm in
a backspace and only put three m's
that will put an abbreviation three
letters
now over in column tools we need to
check the data type text is fine
also just as in powerpivot this would
sort alphabetically and watch this i'm
going to go over to report click in the
white and all i'm going to do is check
month and when you do that it throws it
into a table sure enough
that's alphabetically
so we come back over to data and we have
to tell
month to be sorted by month number so we
come over to sort by column and we
definitely want to say sort that by
month number you don't see anything here
but over in report view that's sorted
chronologically exactly what we want
back to data now we can add our next
date attribute field year we'll click
new column up in the formula bar the
name of this dax calculated column will
be year and the function is the same as
over in excel year
d d
down arrow to get the full column tab
and when i close parentheses and hit
enter
row context sees the date in each row
and gives us the year
now data type whole number if we
accidentally drag this to the wrong
place an implicit measure would be
created to add year which isn't what we
want we could add text data type then it
would be aligned to the left but better
than that we can come up to properties
and say summarization i do not want you
to summarize so that means the whole
number is fine now we have our date
table now over on the right there's our
completed date table notice the icon for
dax calculated columns f x
with a table
now we go over to model view
we grab our d date table
we'll bring it over here
and then we're going to take date drag
it over to date in the fact table
we have the one that's the unique list
over to the many because we hope we have
many sales for every one day the next
task is to hide columns that we don't
want to appear over in report view
month number is only a helper column for
month so right click
hide in report view notice that's
different than over in excel powerpivot
over there it's hide from client tool
in the fact table
we don't want any of the foreign keys
country code date and product
we also don't want any of the number
fields although we're going to use all
three in formulas so i'm still holding
control and we're going to hide all of
those remember if we didn't hide those
and left them in report view someone
might drag and drop and create an
implicit measure right click
hide in report view country code right
click hide now we're definitely going to
use retail price and standard cost and
formulas but we do not want them in
report view
select right click hide in report view
we're going to use both of these over in
report view so now we've imported the
data using power query created the
relationships created a date table with
a dax table function and then dax
calculated columns and then hid the
fields we don't want in report view
now we're going to create our dax
one dax calculated column on the fact
table and then a bunch of dax measures
let's go over to data
select the f transactions table now
here's our fact table and when we
imported this table it did not come with
a field that shows us the amount of
revenue or sales for each line or row in
this table we don't have a field for
cost of goods sold expense either no
problem we can create both of those and
in fact we already saw the formula for
calculating revenue before we started
the project now i'm going to show you
two ways to calculate the revenue column
one is the two-step method the other is
absolutely amazing and makes dax
unbelievable is the one-step method now
i'm going to show you the two-step
method first because it will help us
understand how the formula is working in
each row and then i'm going to show you
the one step method now to calculate
revenue notice we have quantity that's
how many of the product they bought and
we have their discount but we don't have
the price
well the price is over in the d product
table
retail price is used to calculate
revenue and standard cost is used to
calculate cost of goods sold which is
how much each product cost to make no
problem we're going to go over to f
transactions and because there's a
relationship between those two tables
and the product is listed in every row
in the fact table instead of using x
lookup we just use the dax lookup
function related now we definitely want
a new column
and we're going to call this line
because we're going to have two revenue
formulas one totaling it up and this one
is to calculate the line or row revenue
and i'm going to indicate the dollar
sign
equal sign and we're going to build this
formula one part at a time right now all
i want to do is look up the price for
each row in this table let's use related
and notice all it says is give me the
column name if i put d product
and retail price here because there's a
relationship this lookup function knows
whenever it sees by row context the
product to just get the right price
close parentheses and when i hit enter
row context gives us the price in each
row now this table is so big if you try
to scroll down and find a different
product you're not going to do it but
remember the magic of filters we learned
at the beginning of this video
if you click it'll give you a unique
list and sure enough this field or
column has that many different prices
i'm going to click cancel now the next
thing we need if we have the price is
the quantity or the number of units
purchased so up in the formula bar
times and we actually just can put the
full column reference so it's from f
transaction
and there's quantity again this is the
full column but row context will pull
the quantity out from each row when i
hit enter that's the amount of the
revenue without the discount now they
give our discount in terms of number of
pennies for every one dollar so we want
the amount they pay so times in
parentheses there's the one dollar and
please subtract
whatever the discount is f transaction
and then revenue discount close
parentheses and this is the formula
enter that gives us the line revenue
after the discount now down below there
are some definite decimals that we need
to round so before related we use round
we learned round in an earlier video at
the end comma 2 because we're going to
the penny close parentheses and enter
and that is our dax calculated column
formula now if we look over at the f
transaction there's the calculated
column now when we created the
calculated column line revenue we can
see it up here here's the column that's
step one
now we need to create a measure to add
this entire column so we can use that
measure over in report view now there's
a few places that you can create
measures if you already are somewhere in
the table where you want the measure to
appear you can come over to table tools
new measure but since you always have to
make sure that you have the right table
selected i got in the habit of because i
usually have to come over and select the
table and then create the measure i just
click on the table and right click
new measure is right at the top up in
the formula bar we're going to call this
measure total revenue
and i'm actually going to add the
function name sum to this because in
this measure we're going to use the sum
function in our one step method later
we'll use just total revenue and that's
the form that we'll use over in report
view but inside of sum ft that's the f
transaction table and guess what i get
to use my calculated column
so two steps create calculated column
and then create measure to aggregate
when i hit enter this measure will add
this whole field we also could have used
other aggregate functions like average
min or max now i do want to add some
formatting to this measure so when we
drop it in the visual it has the right
formatting now in power bi desktop in
data or table view we cannot see the
result of the measure we see the measure
down here
so what i usually do is i come over to
report view we don't need this anymore
so i'm going to delete i'm going to
select a table
and then resize it
as i create each measure i'm going to
show it here in the report area to make
sure it's calculating the right result
and i'm going to add one field so that
way i can see how the measure is working
across the fields and it will calculate
the total at the bottom i'm going to
check
total revenue and bam there it is the
two-step method we created the dax
calculated column the measure and
there's all the revenue for the product
with a grand total at the bottom if we
were over in power pivot right we would
see the grand total in the measure grid
but over here in power bi desktop when
you are in data or table view we don't
have a measure grid so that was the
two-step method
now we get to see the amazing one-step
method now remember what we did with the
two-step method here's the f transaction
table and we attached to the side of the
f transaction table a calculated column
then we created a second formula a
measure to add well in the one step
method we're going to use an amazing
iterator function that simulates row by
row calculation called sum x and in the
first argument of sum x you just say hey
which table
would you have put the calculated column
so the first argument will have f
transactions and then
you put the exact same formula and i'm
going to copy this
in the second argument of sum x and then
sum x does both steps
inside the formula it'll actually
calculate
all 7 million values as if we had a
calculated column that's the x part the
iterator part and then the sum part adds
all seven million rows to get total
revenue now i'm going to hit enter
come over to f transaction right click
new measure
total revenue
equal sign
the sum part will add the x part will
iterate and look at the arguments all it
wants the table to iterate down and
whatever formula you would have put into
a calculated column
f transaction
that's the table comma expression is a
synonym for formula in this case a
calculated column control v
when i close parentheses
enter
add some formatting
come over to report view the table is
selected
i check total revenue
and you gotta be kidding me the exact
same thing but with the one step method
we did not have to add that calculated
column now the difference between these
two methods let's go back to data or
table view with the two step method when
you create a calculated column these
values are stored in the columnar
database that means it will take more
storage space in the columnar database
but these values are only calculated
when you create the column or when you
refresh the table each individual value
is not calculated over in report view
when we drag and drop the sum measure
into reports or visuals with the one
step method you don't have to store
anything in the columnar database so it
doesn't require extra storage space but
every single value in this measure right
here that's this entire 7 million row
column all those values are calculated
inside the formula every time you drop
it into a visual or report or when you
change the conditions or criteria so
which method should you use
well it's really up to preference
as the great dax masters alberto ferrari
and marco russo say
a hundred million rows or less and
there's not much performance difference
now the rule i use is this in report
view
now i'm going to uncheck both measures
and performance just means how fast does
the measure calculate and get the answer
so we're going to add both measures and
watch to see
is one faster than the other
so i'm going to check the two-step
method first so check
well that was fast now let's do the one
step method
well that was fast they look exactly the
same and my rule is this if i start
dragging and dropping a one step method
and it's calculating too slowly
that means there's some calculation
intensive steps in the formula and i
might want to move it over to a column
what that would do is allow it to
calculate one time when we refresh or
create the column and then it wouldn't
have to recalculate each time we use it
in report u
but again if you only have seven million
rows like we have it's probably not
going to matter which method you use now
there are ways to measure speed there's
a method inside of power bi desktop and
there's also a program called dax studio
with those tools you can measure
performance but we're not gonna need to
do that in this class
but again most of the time it doesn't
matter now i'm gonna drag this over here
and i'm going to name this page
test calculations and now we have four
more measures to create
now you can create measures in table
view or report view the advantage of
doing it in report view with an extra
sheet like this is as soon as you
calculate it you can see the result so
we'll pretend that this is our measure
grid like over in power pivot now i'm
going to go over to f transactions
right click new measure
up in the formula bar we're going to
create the total cost of goods sold
one step measure
this measure here is going to be similar
to our total revenue we need to
calculate
every line item cost of goods sold in
the fact table and then add that means
we use sum x the table of course is our
fact table f transactions comma and
expression is the formula we would use
in a calculated column now we're going
to have to round each amount we're going
to have to look up the standard cost
from the d product table and then from
the f transaction table whatever the
quantity total units is and the net cost
equivalent so i'm going to type round to
get the price we're going to use related
but wait a second i thought related had
to see each row in the fact table well
when you're using sum x remember this is
exactly like a calculated column so when
we put d product
standard cost close parentheses that
related we'll see every single row in f
transaction
of course it will see the product in
every single row and through the
relationship it'll go and get the
standard cost so we have our cost
times f transaction
quantity
times
f transaction
net standard cost now this is not the
discount when you have a discount you
have to do one minus when you have net
cost they already made that one minus or
plus calculation for you so in the
formula we just use that column now
that's the formula for round now we can
look at the screen tip to help us
because we're a couple functions deep
comma number of digits to round to 2.
now watch when i close parentheses round
is completed that's the expression or
the calculated column now i have to
close off some x when i hit enter add
some formatting
and come over and check total cost of
goods sold bam there's our total cost of
goods sold for each product and the
grand total at the bottom now our fourth
measure we want to calculate is a metric
called gross profit and gross profit is
revenue minus cost of goods sold
manufacturers
and retailers like this metric because
it takes the total revenue minus the
total cost for the product cost of goods
sold does not include all the other
expenses in the business it's revenue
minus the single expense cost of goods
sold so for example this is a boomerang
manufacturing company there's a gel fast
there's wood paint sandpaper machine
maintenance packaging instructions and
maybe some other things also all of
those together make up the single cost
of goods sold expense so when we
subtract these two things it gives us
gross profit sometimes called
contribution margin that means the
amount left over to cover other expenses
and profit
so we
right click f transaction new measure
we'll call it gross profit and wait a
second we get to take one measure
already created minus another now watch
this we're not going to include the
table name square brackets and the
measure that convention is reserved only
for columns when we type a square
bracket the dax engine knows the
convention
is to use square brackets only for
measures so we can down arrow to get
total revenue tab
minus
square bracket i see total cost of goods
sold so tab that's our formula enter
i better add
enter
some number formatting
now i'm going to come over and check
gross profit and so for the alpine
product the gross profit was 32 million
now an even better metric is to take
gross profit as a numerator total
revenue as the denominator and then
that's called gross profit percentage
that tells you how many pennies for
every one dollar are left over for gross
profit which is all other expenses and
profit so right click
new measure and instead of using the
division operator in dax
we can use the divide function it needs
a numerator so square brackets and
that's going to be gross profit comma it
needs a denominator square bracket the
measure total revenue now we could comma
and put alternative result that's what
you put in the cell if there's a divide
by zero error if you leave this omitted
meaning just close parentheses then if
there is a divide by zero error in the
report it'll look like an empty cell
close parentheses enter
now this is going to be a percentage so
we'll add percent number formatting i
want two decimals i could come over and
check
and so crested butte has 37 percent
gross profit so for every one dollar 37
pennies are left over for gross profit
whereas carlotta doublers much higher
gross profit percentage now the last
measure we need is total units and we
have a quantity column in our fact table
so right click new measure
total units no dollar sign because this
is counting
some
f transaction and down to quantity
that's the number of units sold for each
transaction close parentheses enter and
i'm going to do comma we definitely want
zero decimals because this is counting
we come over and check total units and
there's our last measure and now let's
go over to model to admire our handiwork
f transactions there's a bunch of
measures we have our hidden columns our
one column where we might pull into a
report the dimension tables calculated
table calculated columns and hidden
fields now often time the hard work is
creating the data model but getting this
correct makes all that visualizing and
reporting and sharing easy and effective
now we can go over to report view we're
going to add a new sheet gross profit
report i'm going to click in the white
we want a line chart we'll put this down
at the bottom
we'll put year in the x
month below year and we want three lines
in our chart we definitely want to see
total revenue total cost of goods sold
and percent gross profit now anytime you
have a measure like this this is a teeny
number between 1 and 0. these are huge
million or billion dollar numbers so
when that happens we come over to the
measure in the y axis click the drop
down
move to secondary y-axis we could see
the label here
and the two labels over here we
definitely want to bring this down two
levels and show year and month
over in format x-axis we don't want to
concatenate that's looking much better
in general i don't want to see a title
that label this label everything's
looking good really a chart like this is
a lot easier in power bi desktop than it
is over in excel now i hit pause and
created this second visual a stacked bar
with category in the y-axis and product
in the legend and gross profit now we
finish this gross profit page and if we
look at this line chart we can notice
some patterns it looks like we have a
seasonal pattern christmas time
christmas time christmas time is always
up and over time the christmas sales are
getting bigger and bigger we can also
see the pattern and this is probably not
very good and this is the gross profit
percentage for all products together but
over the years the trend is down
so from about 44
down to 40. that means over the four
years if gross profit percentage goes
down they have less left over to cover
other expenses and products now if we
come up here if we want to look at how
this chart changes by beginner we can
competition
it looks like different specifics but
the overall pattern is still the same
the seasonal is christmas and it looks
to be going up over time and gross
profit percentage down over time now we
can unfilter we want to create one more
page we're going to call this page
gpmap enter
we click in the white and we're going to
see this amazing visual a world map
and it'll show the whole world now
oftentimes data sets will have longitude
and latitude we don't have that here but
it does understand descriptions or names
like city state or country we have sales
all over the world and we have country
so if we drag that down to location
immediately those dots show up they're
all the same size but we want to look at
gross profit so we'll drag this to
bubble size and instantly we could see
the bubbles all have different sizes now
the funny thing is you would think that
australia would have a huge bubble but
it doesn't it looks like the united
states has a big one over here this is
europe if you hover
germany looks like it has a big one
japan too
and france has a big one and in fact
it's august 20th when i'm filming this
video and right now over in france there
is a 10-day world championship of
boomerang throwing usa has a couple of
teams certainly france germany japan and
brazil
so there's our map for comparing a
particular metric all right so we did
one page with a map we did a gross
profit report we had one page where we
tested all of our measures and in model
there's our data model now we're not
going to publish this and share this and
in fact the main thing we learned in
this example is how to import big data
and build a data model over here in
power bi desktop now that was the most
epic video i have ever made
three and a half hours your one-stop
shopping for excel and power bi data
analysis
we covered all of these topics so you
can become awesome and have fun with
efficiency using excel and power bi
all right if you like that video be sure
to click that thumbs up leave a comment
and subscribe because there's always
lots more videos that come from excel is
fun all right we'll see you next video
[Music]
you