Hello my friend. Today we're answering a Power BI question
asked by Monolito Landsheer from Netherlands, a member of
our learned Power BI family Monolito. Thanks so much for
your question. So Monolito shared this file. And Monolito
works in the travel and tourism industry has been working in
that industry for a long time. And he shared with us of course,
anonymized random data of a hotel price sheet.
Now the challenge here is essentially taking what I call,
I would call this a human friendly format and turn it into
a more machine friendly format, more of a tabular format.
Something like this which Power BI can easily consume. We can
chart, we can graph, we can use in our model. So again Monolito,
thanks so much for your question and this was discussed with
Monolito. We worked on this problem together in our QA call,
in our live QA call and if you have.
Power BI questions, you can ask them too. Just go to
learnowerbicom question O let's look at the problem here and the
goal is to go from this format to this format. Now if you were
to break that down and step by step and justice focus on let's
say a single row O we have this data. It's showing a week's
worth of data for Group One, group 2 and so forth. For now,
let's just focus on Group One O if you were to do Group One, we
do need to switch that or UNIVO.
This data into rows, right? So we'll have that, but then after
that we also need to fill in the dates because this is a week's
worth of data, SO5 July to 11th July. So we have those at the
ends, but then we also need to kind of fill in the dates
between that as well.
And then we also need the group and the resort. And that's what
if we kind of, you know, just reorder the columns there, we
would get the final format that we're looking for, date, resort,
group, amount. Now if you realize here there is also some
complexity here is that the headers are actually split in
multiple rows and after that not only do we have to do this with
one sheet, but we also have to do it with cycling through all
the input sheets in the data.
Now that full complete solution, how to sort out or handle these
headers which are actually split in multiple rows, how to combine
multiple sheets. That full solution is covered in our
advanced program and you can find out more about our advanced
program by going to learnpowerbi.com advanced.
But for the purpose of this YouTube video, what we have done
is we've taken that and simplified that a little bit. So
what we've done is combined those headers into a single row
so you don't have to do a handle with the headers being in
multile rows.
And then we've also simplified and we're just going to focus on
a single sheet. Now if you really want to learn Power BI
and not just watch this video and move on, then I would highly
recommend that you also download this file by going to
learnpowerbi.com download and then follow along with the video
or repeat the steps yourself after watching this video.
So let's click on Transform Data. That opens our Power Query
editor, which is the kitchen of Power BI and we're going to
point that to our Excel file. So new Source Excel Workbook, Hotel
Price sheet simplified and I'm going to select our input table
from here.
And hit OK, all right, now this one. I'm going to designate this
as the source because you're going to see later. I need
actually two things from this data. So I changed the name to
source and I actually right clicked and unchecked the Enable
load. I don't quite want to load this data, so we're going to
right click on this again and click Reference.
And this is the file that we're going to keep building. So the
first thing I'm going to do is I'm going to remove these header
rows that we don't need so 12345. So I'm going to click
remove rows, remove top rows.
Click five. Hit OK O. Now that those rows are removed we can go
ahead and promote this row as header and for that we are going
to click right here. Use first row as headers. I click that and
you can see our headers are in place. And now of course if you
look at our problem, we essentially have data in columns
and we have to.
Move that into rows. That's our first challenge. Now there is
the next challenge that we're going to tackle next, which is
that this is actually not a single day's worth of data. This
is a week span of data. How do we deal with that? We'll take it
step by step, but the first thing is again to unpivot that
data. So I'm going to hold these date start and date end columns.
I'm going to select both of them and then I'm going to right
click on this and say.
Unpivot other columns, right? So the other columns, I want them
to be transitioned to rows. And you can see this happening right
here. Again, that data which was spread out in rows is now
unpivoted into columns, right? So these were sorry, these were
column headers and now these values are captured in rows. So
that's what UNPIVOT does, which is one of my favorite operations
in Power Query.
All right. So now our real work really begins. So now we've got
to figure out that, OK, this was for a week's worth of data and
then we have to decipher which one is that. So for that, if you
now, of course, one concept that I talk about is human learning
comes before machine learning. So first you have to understand
like, how would a human do this, right?
So of course, if I'm looking at this and I know that the week
starts on, actually what I'm going to do is I'm going to
remove the date end column. That's actually confusing. We
don't really need it. So let's remove that column and let's
focus on the date start, right? So if you're looking at this
date start and this is a Monday, now the week in this case, if
you look at the data, it does start on a Monday. So if I'm
looking at a Monday.
It is this date, right? So if I'm looking at this cell, this
cell is 20th December. What about the next cell over here?
Well, the next cell is that date plus one, and the next cell
after that is that date +2, right? So you get the idea.
Essentially what we have to do is take the date start and if
it's Monday at 0, if it's Tuesday, add one, if it's
Wednesday, add 2 and so forth. So let's now again, so that's
the human learning part.
We understand that as humans, and now we just have to break it
down step by step and teach that to the machine again. It's
really simple guys, A Power Query or DAX or Power BI in
general. You can take a complicated problem, just break
it down into simple steps.
And go from there. All right. So again, just as we said that
humans can do it, we're going to teach Power Query to do it. So
first step, let's split out. So right now it has both the group
name and the Monday, Tuesday in the same column. We're going to
split that column so that it's separated. So I'm going to right
click on it and say split column by delimiter.
And in this case, there is the pipe delimiter, which is already
there and I'm going to choose the leftmost delimiter hit. OK,
so now we have the group name here and the weekday here. And I
don't really usually like this automatic change type steps, so
I'm going to actually delete this one and I notice there is
another one up there too. I'm going to delete that one as
well, so change type is gone.
And here what I'm going to do is I'm going to actually rename
these, but sometimes I just instead of adding another rename
step, I just like editing the query right here. So in here I
can go instead of calling it attribute, I can call it group
or group name maybe. For now let's say group is fine and the
other one is my weekday. All right, great and actually I'm
going to go back.
And here's the UNPIVOT column. I actually wanted this one to be
called Amount, not Value, so I'm going to change that as well
while we're fixing column names. And I'm doing this because the
output that Monolith had requested was looked like that.
So if you look at the output, the output we want is the date
resort group amount, right? OK, so come back here.
Go to the last step. Oh, I don't really want change type so
remove that. So we have the weekday amount. Looks good, so
let's keep going with the logic that we had. So again, we want
to add 0 for a Monday and one for Tuesday. So we're just going
to define a numeric column for that, so we'll call it
WEEKDAYNUM. And it's a simple if and clause. So I'm going to say
if.
Weekday equals Monday then zero, right? And else if weekday I can
just copy this one, if weekday is Tuesday then one and repeat
that. So let me complete that and we'll be right back.
All right, so we're almost done with this. So again, the same
way human learning to machine learning, this is what we would
have done as humans for Monday, add zero, Tuesday, add one. And
I've just thought that to Power Query, Power BI. But I'm going
to do one additional thing, which is just in case we get
some data where it's not a match, I want to raise an error.
And the way to do that is you can just type in the word error.
Like that. And then here you can specify the error message and
I'm just going to say unknown weekday, weekday value and I'll
do that. And again, I don't expect to find this in the data,
but this is what's called defensive programming, just in
case something doesn't work. Maybe in future something breaks
in my data, this is going to catch it and it's still going to
be consistent. So again, you can see now we have this.
Which is really clean. And now just again, just as what we had
done as humans, we're like, oh cool, now add add these dates to
that date. So let's do that. Let's add another column and
this is going to be kind of our final date and this will be date
dot adddays and we're going to start with the date start.
And we're going to add the weekday NUM. Now I'm not 100%
sure if this is going to complain about the data value
not being date. Let's just give it a try and then we'll fix it
if you need it. I think it was able to automatically kind of
recognize that it's a date. So that did work. So that means
we're in business. Let's go back to the output that we're looking
for. So the output we're looking for is date, resort, group,
amount. Now really we do have the date we do have.
We don't have the resort name. We do have the group and we do
have the amount. So for the resort we need to go back to the
source and that's why I had specified that as a source and
referenced it. So now you know. And here actually I made a
mistake, I didn't really. So it had some automated steps that I
didn't want them to be there. So I'm going to remove them. I'm
going to remove the change type, I'm going to remove the promoted
headers.
I do need the navigation. Now that does mean that I also need
to change this file. So I am going to go all the way back and
notice that now I actually need to remove 6 rows, not five. So I
am going to change the next step, remove top rows and change
that to remove 6 rows and everything is going to be as it
was, at least for this query.
So that one seems to be good. Now it's back to that. Now let's
go back to our source and we're going to right click on it and
reference this again. And this is where I'm going to extract
the resort name, resort name. Now resort name is simply the
first row and the column called column one. And the way to do
that is simply. You can go here.
And to reference the first row you can do curly brackets 0 so
the rows are index based and it's a zero based index. So 0
indicates the first row and this would give you the first
complete row. In fact you can hit enter on this and you can
see what it does and it's just giving you first row. And of
course the first row just has resort XYZ and that's it.
Nothing else and not very interesting.
But actually we don't want the whole row, we want the column
one. And for that we can specify kind of square brackets and the
name of the column. And in this case it's just called column
one. And we hit OK, now notice what happens here. Notice this
query looks slightly different. That's because what it's saying,
What it's trying to tell you is that this is a scalar value,
which just means a fancy word for. It's like a single value.
It's not a table. Most things in Power Query are going to return
a table. This is a table.
But this one is a scalar value, which is fine by us. Now hey,
it's possible that in some scenarios you're dealing with
the scalar value. Now you do have a button to convert it to a
table if you want it, but we don't want that in our case. Now
we'll go back to our query and this is actually I think we're
ready to kind of rename it. So this is actually the output
we're working on. So output of that 2021 table. And now let's
add that column, custom column and this one is super easy.
So I'm just going to check. I want to match what he called it
resort. He's just calling it resort, and we're just going to
reference that other value. In fact, you can see autocomplete
even shows me that value. So yeah, so it's just, I'm just
saying, yeah, go pick up the value, stick that in this column
and that's now we have the resort name right now.
We're almost done. Let's let's go back. So it's date, resort,
grou, amount, that's the format that they need. So I'm going to
select date, resort, group, amount.
And I'm going to right click and say remove other columns. It
also sorts or reorders the columns in the order in which I
selected. So now I have the final format looks good. And
again, you remember I removed the automatic change type steps.
I don't like those. They can be really problematic. I'm not
going to go into the detail of why right now, but I think we do
cover that in our advanced lessons as well.
But yeah, just trust me on that, right? You should be in control.
Don't let Power BI stick in those automatic change type
steps. So I want to be in control and usually I typically
focus on just numeric and date columns. You do want to set the
type, but I usually always do it at the end when I'm done with
all the transformations unless I need to add 2 numbers and
therefore I have to do that earlier.
All right. So we're ready for that. So let's go to date. And
I'm just going to change it to a date value and this one, I'm
going to change it to a decimal number and that's it. That's our
Power Query challenge. I want to thank Manolito again on working
with me on this question on the live call. And as I said
earlier, if you have any question, if you're stuck
anywhere, just go to learnpowerbi.com/question and
we'll get that question answered for you. I'll see you in the
next video. Until then, power on.
Show Vijay pal.