In this video, I'm going to show you how to solve this problem
of selecting the top row in each category.
Let me show you what I'm talking about based on some sample data.
So in this case, if you notice we have this
is project management data and we have different projects identified by the
project number key over here and as you can see we have at least one
project listing here ab134 cd14 and it's just a sample data set but
you can imagine a long list of these projects.
Now this is an incoming data but we would what we would like to do
is for each project select the row with the
maximum finish date. Now this is project management data
but i've seen the same scenario or same requirement
in lots of other data sets. So maybe you have come across something like this.
If you haven't this would still be a fun exercise to kind of jog your brain and
learn a little bit more about Power Query which is what
we're going to use to solve this right so you so you
understand the problem here that we have to select for each project
which is the category you know the top row
based on some criteria so we're going to start here at level
1 which does not have any ties and then we're going to look at how
we're going to handle it in case there are ties how we're going to use other
columns as tie breaker. Now this question came from
Ian Bowman a member of our LearnPowerBI.com.
You can find him at Workadaptive.com and if you haven't subscribed to our
channel yet make sure you do because this is what we're gonna do for
this problem we're not only going to solve it we're
going to give you the solution but more than that i want to give you the
thought process behind it and i feel that that that idea
is really important in fact that might be the
missing step in your mastery to Power BI understanding the thought process that
goes behind solving problems and we're going to cover this one in
detail and the the four other steps in our next
video the five steps to master power bi so
that's coming next so make sure you subscribe so you're notified
whenever that as soon as that video is live
uh let me quickly introduce myself in case you don't know my name is Avi Singh
I'm a Microsoft MVP and a best-selling Power BI author. So let's continue on
to the problem again we're going to solve a simple scenario where there
we're going to select the top row and then we're going to look at example 2
which is slightly harder which is where we have a tie and then we're going to
have to figure out how to do the tie breaker so for that i have my data
loaded in power bi i want a magical tool and we're
going to step into the kitchen of power bi
which is the query editor let's go to table one
this is our simple table with sample data and again our goal is to simply
select the top row from this now often you would find me talking about
human learning versus machine learning and in a way
learning power bi is about dumbing yourself down
as it turns out these machines are very prescriptive whereas humans are
intuitive now whether you think that makes humans
smarter i don't know right but but will that
leave that debate aside but we're intuitive so we can think in leaps and
bounds so if if you show this problem to a human they
can intuitively say yep this is the top row this is the top right those are the
ones i want to keep and what you need to do is to break down
that thought process in simple simple dumb steps because i think
machines are kind of dumb right now right i mean they
they can only do stuff in a very prescriptive way
all right so that's what we need to do we need to break down
now another key concept to understand is that how power query works
and power query is very unique in this way then in general
the way power query works is row by row and what that means is that if you're
using some logic or some information to solve
a problem as a human which does not exist on the
row then it's not going to work right so if
you think about it i mean as a human i was able to determine that
yep for ab13455 this is the right row and and these ones
are not right right because they don't they're not the maximum date
but how did i do that well if you just focus on
this one row let's start over
in this row on this row oh actually let's try this guy on this
row i need one other piece of information
which is not available in this row and that is
this information what is the maximum date right so if only i had
this information available right here to me
in this row then this would be a very simple problem
all right so that was the thought process behind it that's how i'm
thinking about it now let's see how i would go on about it
so what i'm going to do is i am going to insert a quick step here and i'm going
to rename it just so i remember that this is a big
step so let's say start um find max row
max date maybe max date let's try that okay so i'm starting something awesome
and then i'm going to go here go to transform and click
on group by over here click group by and i'm going to group by my
category and again you can generalize this data right so this is project
number data but this will be applicable to pretty much any data
set so you select your category or could be a few columns as well and uh
and then you're gonna say give me the maximum
of the date actual finish date in this case
and that would be my max date so now i have the answer now of course
you remember our goal was to have it be available in this row
side by side the max state you know we wanted to be available in there
hold on all right sorry about that so i i
didn't realize that i was working on this earlier and
and i had the column max state in there so ignore that so again
if you go back this is our incoming data set
if you go back there was this max date column here ignore that
we want really the max state to show up on the side
available in the same row and once you have all of the information you need
to you know whatever do the logical or mathematical competition in the same row
then you realize it becomes really really easy
so now we have this how do we combine it back to this and that's that's a little
tricky one but we can do it so what we're gonna do
is i'm gonna i'm gonna mark an additional step and
i'm going to say i'm i'm done with this uh
find max date so i'm going to go in here and i'll say end
uh find max date and when i do things like that and what i'm going to do is
right now end mac state is referring to grouped rows right so by default power
query you refer to the previous row and that's
what it's doing you can see it here but actually i don't want that i want to
go back to my original form of table so i'm going to copy that
and then go to my end date and just have it point to
that you know that previous row so now we're back to where we started but we
have this information in group rows right
so uh in fact let's uh let's let's rename that step as well to say
max date table sorry i should have done this earlier i hope it doesn't mess up
my next step uh it didn't all right so i have max
tape table and i have this and really if you think about it this is
a merge problem i just need to merge these two together
so that's what i'm going to do with a little bit of help from
power query so i'm going to go to home merge queries and i'm going to merge it
with itself table 1 current and i'm going to do it based on project
number key and and don't worry this is what i call
a dummy merge and i'm going to edit it because i don't
want to merge and find this step with this step i really
want to merge this step with this step but i can
quickly change that here and i can say yep no no no don't
don't go to this one go to max date table you see how
easy that is max date table and now that merge
is done and i can just expand and get the max
date value perfect right so you can see now the information that we need to make
our decision right so whether we retain this row
is it is it the maximum date for this project
or not is available to us right there in the row itself so you see how easy it
becomes all i have to do is compare these two dates and say
if these match then i'm keeping this row thumbs up
can you see my thumb all right if they don't match then i'm not keeping this
row in this case of course they do not match so this is going to be
deleted in this case it is going to match so let's do that
really quick i'm going to say add column custom column
and i'm going to say match you can say match
max date oops
and i'm just going to say does max date equals actual finish date hit okay
and you can see that for for the right ones it's true everything else is false
and then i can just say true and there's a big reason
filter it down to just true and this is our end result pretty much right this
was our goal to start with the original table
but then we only wanted to keep the top rows in each category
top rows how based on a certain criteria in this case the actual finish date the
highest one right so we have that but there's a reason why
we call uh the query editor the kitchen of power
bi because it has got the trash can and that's why i love it and we talk all
about it in our power bi tutorial if you haven't seen that you can access
it on our channel we're going to link it somewhere here but let's see there we go
so in a link to our power bi step-by-step tutorial where we talk more
about this concept kitchen of power bi and the trash can so
in here the trash can is that all the columns that we don't need
anymore we can get rid of them right so that's the trash can you peel the potato
or peel the banana and you don't have to
eat it you know you can throw it away all right so uh so we're done with that
so that was our approach one now let's uh up the stakes a little bit
and and see how we can do this with the tiebreaker
so first of all as i was saying earlier we were talking about human learning and
machine learning right so human learning machine learning well so i often ask
when people say oh this needs to be done like well how would a human do it right
so and again the answer would depend upon your scenario and as it happens in
this case that our tiebreaker is the step number so if the dates are
are common you know there's a tie then the the
highest step number wins now this is in this scenario but
imagine a more complicated scenario imagine where it
was not just um uh you know two columns when maybe three
columns were involved for a tie break or a four or five right i mean there was
this this the oh no you sorted by the project
number then actual finish date then then schedule start date then step
number then something else and then something else
and and in that sorting order keep the top row and imagine if this was
like based on two columns five columns 10 columns 20 columns right
so you know sometimes i love exaggerating the problem how would you
solve it what would be the most elegant way to solve it so the the you
know so the trick i'm going to show you that's going to work
as beautifully and elegantly regardless of how many columns you need
to to figure out the tiebreaker you could be right
so in this case there are two columns so what you need to do
is could be two columns five columns ten columns doesn't matter that you're going
to sort the data set
all right so for that i'm gonna sort by project
number key so project number key i'm gonna sort it ascending
and then i'm going to sort descending by step number
and you see that it gets added in the m code in the formula bar so it's doing
the sort order but again you can do this step and go ahead and
click and if you need other sorting to to use as a tiebreaker again could be
two columns five columns ten columns doesn't matter
you do that in this one step which is easy
and then you do this you're gonna go to add column and add an index column which
is right here so when i click that and basically we're going to go
through the same process that we did here
where we selected the max state but well max a yeah in this case we're going to
select the minimum index because we've already sorted it in
the right order so now we can say for each project find
me the minimum index and we're going to keep the row so
i'm going to go through that fast because
we have already covered that pattern so you understand the concepts
but you realize how we took this weird problem of that oh i need to
yeah it needs to be by you know by project number and by step number and
then i'm finished getting started or a lot of these and that stuff almost
doesn't matter i mean you take care of that
in your one step uh you can't see my finger
this sort one and you can add two columns you can add three columns five
columns ten columns here doesn't matter just click sort and it would keep adding
to the sort order and after that you add the index
keep the minimum index and boom you're done so let's go ahead and do that
so i'm going to do this a little bit quick we may not go through
all the steps the way i did it so i'm going to first take project number key
and i'm going to say minimum in this case of the index
and i'll say min index and i'll hit ok and i'm going to rename this to min
index table and i'm going to add another step
and i'm going to merge this with itself oops okay i'm going to reset this step
back to the last step so let's go in here
and again i'm not doing it as clean as i was the other one that is how i
typically do it but um but yes with this one
you've already seen me kind of do that so i just want to give you the end
result so we're going to match it with the current table project
number key project number key and this is again as i said it dummy
merge because i really want to merge it with the
min index table so i'm going to now bring that
back so now i'm merging that table with a min
index and that's done and i'm going to grab the minimum index
and now all i have to do is compare if these two index match
right then i'm keeping the row otherwise i'm not so add a custom column
match index let's just say match and i'll say index
equals min index sometimes you have to be careful about the data's type
uh it did work so that's great and i'm going to keep
only true and use the trash can of our query editor the kitchen of power
bi and really these columns have served
their job so that's it so now we have taken the same problem but
we have almost an unlimited capability of tiebreaker so whatever
a human would do to you use as a tie breaker we can do that
so again folks i'm going to pause here and uh
and hopefully this video helped you if it did then give it a thumbs up and
definitely subscribe but i feel that i've been teaching power
here for a long time and i feel i've not realizing it i've been
teaching the thought process but i really feel that that's the
missing step so i'm going to focus more on this in my future videos and
certainly inside my learn power bi program as well
and look out for my next video which talks about
all the five steps and how this step fits
into that uh overall steps that you need to master power bi i'll see in the next
video until then power on