in this video i'm going to show you
power bi
grip by using three easy ways
the first one using the query editor the
second one
using dax and the third one using a
little bit of magic
inside power bi now of course we are
going to compare
these three at the end and see which one
comes out at the top
uh but we also have a bonus tip for you
on how you can use the group buy and the
query editor
to debug some query issues hey i'm avi
singh
a microsoft mvp and a best-selling power
bi author
and if you want to become a power bi pro
make sure to subscribe and click that
bell
so you are notified whenever i go live
to answer your power bi questions so
we'll jump right into the power bi
desktop for this
in the query editor so let's go ahead
and this
is using our famous adventurers model by
the way to download this
file and any other file in any of my
videos all you have to do
is go to learnpowerbi.com
download and we're also going to put
that link in the corner
uh and down in the description below and
that will let you download this file and
follow along or examine it later
if you wish so again let's go to the
query editor
and the and the table we're going to use
for this example is the budget table
so for the budget table you can see it's
a pretty simple table with the budget
amount by
uh product category subcategory product
name month and so forth so the first
thing we're going to do
is we're going to try to group by and to
give us
budget by category so for that what i'm
going to do is i'm going to right click
and
reference this because i don't want to
mess up my original budget table so i'm
going to
right click and say reference and i'm
going to rename this table
as budget by ear right so i just uh
oops i just renamed that in this
name part over here and once i've
renamed it i'm also going to right click
on this
and move it to a specific group
just to just to keep it organized in in
the queries in this section so budget by
ear but again i haven't done anything
yet
so again our intent here was to group it
by ear so for that i'm going to go
on the transform tab up here and then
choose group by so let me do that so i'm
going to click
group by and i think the column that's
selected
i had category selected is selected by
default
and here the next thing that i would
look at so ignore the basic in advance
for now
group by category sounds right but then
i want to examine the operations so if
we go
in here i don't want to count rows i
really want to
add up or sum the budget so i'm going to
choose sum and then
specify the column budget amount don't
forget to give it a good name
in this case we're going to call it
total budget and i'm going to hit ok
and watch what happens from all those
rows now summarize or grouped
by category and i'm getting the answer
for the total budget
by category budget uh oh boy i messed up
i changed the name
to budget by year okay budget by
category that's what we're doing
now let's go back and do budget by ear
so i'm going to repeat similar steps i'm
going to right click and reference
i'm going to give it a a good name
and this time i do mean budget by year
and i'm going to move this
to the group just so i keep them
organized inside my query editor now i
come back here now i don't have
an ear in this table right now but i do
have the month
so what i'm going to do is under
transform i'm going to look
under the date and time because that's
the kind of column this is
and in date i'm going to select year
so great so that's a year i'm just going
to rename this as well just so i don't
get confused later on
so now it's in here and now i'm ready to
say group by
again same action group by year similar
action oops
not that i wanted the sum of the budget
amount
and we would call it total budget
hit okay and there we have it budget by
ear but
let's do one more thing which is budget
by ear
and category so again similar steps
right click
reference budget
by year and
category oops
uh typo there cat to gree
i got that and i'm gonna i'm gonna keep
them organized
in that group by and come over here
instead of this i actually want the year
rename it
and and and and here if you go
click transform and group by you're
gonna have to switch to the
advanced one right so you have to switch
to the advanced one
uh oops let's try that again so again
click on group
by and make sure you switch to advanced
now what
advanced lets you do is it lets you
specify
not one not two not three but as many
levels of grouping as you need
so if i wanted i could say yep we now
group it by category group it by
subcategory and so
keep going depending on what your table
was so i'm going to remove the
subcategory from here
year and category and again i want the
sum of budget amount
so we'll get that total budget
hit okay and voila we have budget
by year and category as calculated using
the query editor now before
i go on so what i'm going to do is i'm
going to load this table back
into a model but i promised you the
bonus tip on how i use this sometimes
to debug query issues now sometimes if
i'm working with a complex
query then you know as you can see in
this one there's a lot of different
operations going on
now often based on especially when
you're merging tables and such
you want to be very careful about the
number of rows
typically if you're working operating on
a data table you don't want the number
of rows to
suddenly change so in this case i'm just
going to make something up let's say
uh let's say in this case what we're
trying to do is go to our data table
sales
and we're merging it to our product
table
and bringing in colors so i'm going to
say merge with the product table
and hit ok
and in here let me just expand and just
get the color
now again so this is just uh
you know kind of something that i
thought of but in a real scenario often
it's a it's
quite a bit complex query but the key
thing that i want to understand and
confirm
is that the number of rows didn't change
because if the number of rows changes
something
bad happened one of my assumptions
didn't hold maybe the product
key isn't unique in the product table or
if i ended up with less number of rows
maybe i didn't do the merge right or
something like that
so i want to validate it i want to
verify that and usually what i do
is i go before the step when i did a
merge
and i say group by insert
and i'm going to go to the advanced tab
again but this time i'm going to delete
the single grouping so there's no
grouping at all and just leave it to
count rows because that's what i'm
looking for
and hit ok now this should give me the
total number of rows
and i can do this step again so so once
i know the total number of
number of rows here i can uh so i'm
going to delete this step
you can repeat this step after
your processing and maybe you have like
10 20 30 steps and
you can repeat it maybe at the crucial
points and then
use the query editor to cross check to
make sure that the number hasn't changed
if it has changed then throw some kind
of error and basically stop the refresh
from happening so that's my bonus tip
for you so
let me show you what this end result is
going to look like with the tables we
loaded using the query editor all right
so my tables
have been loaded here and if you wanted
to
you could create a tables or list out of
it so this is my finished file you can
also download the finished example
uh using the same link that i talked
about
and here you can see i've just placed
the same thing
you know so this this table shows a
budget by category
and the next one here uses the fields
from budget by ear
and the ones over here uh both of them
use you sh
are i'm using to display the budget by
ear and category so
again no surprises there whatever we saw
in the query editor that's what's been
loaded in the model
now let's uh go to our next step
and do it by dax now what i did want to
say was that if
you are starting out in power bi and you
would want to understand
how the query editor works and how dax
measures work which i'm about to show
you right now
kind of step by step and and build you
kind of your first dashboard your first
power bi model
then i'm going to link to a power bi
tutorial in the corner here and down in
the description below
and go ahead and access that all right
so now we have
uh okay cool so this is we're gonna do
the same thing using dash so the budget
by category by ear so for that
what i'm gonna do is is go to the
modeling tab
and click new table now if you have been
through my tutorial you know that i love
measures i'm a huge fan of that but in
this case we're not using that one we're
seeing
a new table so i'm going to click that
button and
then i am going to type a dax expression
but this tax expression is going to be
different than measures
which return only a single value this is
going to return a table
so here we go and i'm going to say i'm
just going to prefix everything by dax
so dax
budget by category that's the first one
that i think we had done in the query
editor
and i'm going to use a function
available in dax called summarize
columns and i'll just say
i want it by category so let me just
select
a product category
and and say so now the next thing that i
need to specify
is a name and expression pair so the
name
what do i want to call this uh this uh
this column i want to call it total
budget
right so that is the name and you can
see this highlighted in that
in that help thing over here and then
what is expression how do i calculate
that
and for this we can use our budget
measure which budget
measure which is a simple sum of budget
amount and you can examine that
when you download this file and that's
it i'm going to hit enter
and let's see what that table looks like
so i'm going to go to the data view
and if you notice this tax budget by
category
this looks uh identical to our budget by
category
we built using the query editor again
there's some formatting differences but
otherwise it's calculating the exact
same numbers
so now let's go ahead and calculate
really quickly
uh let's go do new table again and
this time i'm gonna say budget by year
oops uh budget by here okay great
and i'm going to swap out product
category with
calendar year
and i'm going to define the next one as
well
and then we're going to go quickly
examine them so the next the last one we
defined using the query editor was
budget by year and category and we're
doing the dax version of that
so here uh i'm just going to add
calendar
here and i'm going to keep the product
category so by year
and product category and the calculation
is the same
so now let's go back to the data view so
this is the one we were looking at
earlier this was the dax
budget by category let's look at the
next one dax budget by year again
no surprises looks uh as we expected as
the query editor version
and this is the last one which is dax
budget by ear and category and again let
me switch to the finished example
where what i've done is i have displayed
fields from these table in just a list
over here right so now are you ready for
the third step which is doing it
using a bit of magic so let's go back
to our model here that we're working on
and uh let's go to the
relationship view here and unfortunately
it's created some automatic relationship
let me clean that up and
we'll pick up from there all right so i
i did clean up the relationships and
cleaned up this view but i also
created a simple view
or a layout as it's called in this new
relation model diagram view which is
just shows the budget table so budget
table is our data table in this case and
it has these two lookup tables
now here we're going to use the magic of
relationships so we don't have to do
anything in the query editor
we don't have to do anything in dax
we're
so ignore all of the tables that we have
created using dax
or the query editor we're just going to
go to our original budget table
and the budget is as i promised just a
simple sum of budget amount but we can
grab this field
put that in a list let's say and let me
let me make the size a little bit bigger
and then we're gonna just go reach over
and
pretty much we can pick anything in
our lookup tables including the columns
that we had decided so i can
i can go to my product table that's what
we had started with
and i can say oh i would like to see
this by
category so i can just drag and drop
category over there
and there it is those are results by
category now let me make a few
different copies of this so here i can
say
i want to see by the calendar
year
okay not do not sum the year so don't
summarize
perfect and then i can i can go over
and change this to a matrix and again
drag and drop so i already have here
and i would like to see
maybe put category in and rows in here
but but again uh well that does not look
pretty for
uh for the adventure works data set and
let's do one last thing and
add a plus icon here perfect but you can
see that
we didn't really need anything special
to do anything special oops i i did it
by
category in here let's do the opposite
perfect
i don't have to do anything in the query
editor really if you want to group
by something and you have the data
organized in this shape of data table
and lookup tables
you can group by or slice and dice it
any which way you want now if you want
to understand how that works step by
step and walk through a more extensive
example
again uh link to our tutorial in the
corner
now let's come back and we had promised
that we're going to compare
these three scenarios let's go ahead and
do that now let's say
that we changed their minds or her users
did
and they said well this is great but i
actually
want to see it by year and quarter and
you know and and we all we have to do to
fix that is just simply drag and drop
so i'll remove category there and i'll
go back
and grab the quarter
from there and just put that in the
columns
and there we go now we have it by year
and quarter now
so this is easier to do all you're doing
is kind of clicking and selecting what
the user has you can have them sitting
on the side
but of course in the in if you did it
using query editor and dax you would
have to modify
each query or the specific query now
this goes
deeper than that though so just for fun
i had uh
let's undo these changes and i had added
a slicer
for quarter right and i want to show you
that if you
add a slicer for any field and so forth
in the lookup tables so this is the one
that used the magic of relationships
so here if i click on q1 it's totally
responsive
if i click on q2 it's totally responsive
if i select hue and q2
it's responsive so this basically this
plays
with the rules of power bi if you
connect tables this way
and operate using the magic of
relationship everything is going to be
dynamic everything is going to be
responsive and it's going to update
based on the selections that you have in
a slicer or
the cross filter you use using other
graphs but the same
filter if somebody were to add
in here and i think i'd hit it so there
we go
the same slicer if you had used dax
you're going to notice that it doesn't
filter it doesn't impact it at all
because dax generates a static table
not a dynamic table and the same thing
with a query editor
none of it is going to respond and again
you can go
back as an author you can go back to the
query editor you can go back to find the
dax measure
but folks the biggest difference is that
solving something in in either in the
design
time or run time right so if you use the
query editor or the dax approach
you have to solve it at the design time
as an
author now as an author you may not
precisely know
exactly how the user is going to slice
and dice it and of course if you're
publishing this
online then it's possible that hundreds
of users
can connect to your model and creates
hundreds of separate reports
you cannot predict in advance are they
going to like to see it by year and
quarter year in quarter by category
or the thousand different possibilities
which may exist in a realistic data set
so in general scenarios it's more
powerful to just leverage the magic of
relationship
which automatically allows you to slice
and dice or group by data
any which way you want rather than query
editor and dax
now there are scenarios where it does
make sense to use group by and query
editors or tax
if you have run into one of those i
would love to hear from you in which
scenario you use that and
why it made sense so let me know and
until next time
power on my friend hey keep watching
more videos
and keep learning power bi but if you
did enjoy this video i would love to
hear from
you so leave a comment like subscribe
all the good stuff
power on my friends