Removing Rows Removing rows is a really important part of data cleansing. We often end up with completely blank rows.

So not just an empty cell here or there but an entire row of null values and again rather than just keep them in our our dataset it's important that we do remove them because when we bring the data through into the report, we will see those blanks in some of the visuals that we create.

It's also possible in Power BI to remove top rows or bottom rows so we can specify for example remove the last two rows in the data set or remove the top 5 rows in the data set.

Sometimes we have to do what's called promoting row headers. So we'll often find if we have got some null values at the top of the table and we remove those site the column headers that the actual field names aren't updated correctly. So we'll end up with a table with heading such as column three column 4 column five and this is going to be no help to us when we bring that data through into the report because the field list will just display these headings.

So we need to tell Power BI to promote the first row up to a header rule.

It can do this for us quite easily in query editor and it will correct the field names right away.

Let's do a workout and see how to work with blank rows and remove those in Power BI query editor.

So I'm just going to go in to Get Data and use an Excel workbook here. So module 3 lesson two workout.

And when the navigator comes up, I'm just going to select remove rows as the worksheet and it can see already there lots of blank rows and so on in the starter set. So I'll use transform data and that will take me through to query editor where I can fix up the data. The first thing I want to do is remove the blank rows. so I'm just going to go here to the Home tab remove rows and remove blank rows.

And we can see they disappear immediately now if I look down at the bottom of this data set, I'll see it's not an entirely blank row it does have some values in it, so it still remaining but it's really no use to me and my analysis so it's much better to get rid of that so I can use remove rows, remove bottom rows, and it's the bottom two rows so I'll specify how many I want. Click on okay, and they're gone.

It's important point I did this point in time that if I make a mistake, if I do remove maybe rows that I shouldn't have I can easily x out that step over here to the right. So if I X here it will bring those two rows back. I'm going to remove them again. So remove bottom rows and remove two bottom rows. There we go.

The last thing for me to do is fix up my column headings. I've ended up with these column headings which are really no use to me at all. When I bring this data into my report and the first row contains what really should be the column headings. I'm not going to remove the first row what I'm going to do is ask Power BI to use the first row as headers. And when I click on that we can see right away iIt's taken row 1 and it's used them as my heading row. I now I have a complete data set which I can use here. So I'll click on close and apply and that will bring that through into my Power BI report ready for some analysis.