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.
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.
- How To Add A Responsive Navbar To Your Blogger Website - Live Blogger BlogSpot
- Slide Presentasi PowerPoint Sederhana namun Keren dan Menarik PowerPoint
- Design Your Week: Time Management with James Jeffley
Source and I actually right clicked and unchecked the Enable - ID Card Make
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.
