After clicking on OK, it will result as below in the table. As I have entered 5 in the textbox, only 5 rows will be kept and other will be removed.
If you put 0 as number of rows, it gives the empty table as result which is shown below.
If you enter non-numeric integer values like text, decimal or negative integer value in the Number of Rows text box, then the OK button will be disabled. It gives the warning message as shown below.
Keep Bottom Rows
This is the opposite functionality of Keep Top Rows. Keep Bottom Rows keeps on N rows of Bottom and removes other rows. The value of N will be the same as in the case of Keep Top Rows.
Once you click on OK the Keep Bottom Rows will give output, as below.
Keep Range of Rows
Specify the number of rows to keep starting at a specific row. It is used to keep only the N records starting from a specific row number. Once you click on Keep Range of Rows it will ask you to enter the starting rows and number of rows to be kept in the table. The value of both textboxes will be in an integer as shown above in case of Keep Top Rows and Keep Bottom Rows. But, the value of First Row can’t be 0.
In the above image, I have marked the table row before applying Keep Range of Rows.
In the above image, I have taken 4 as First row and 15 as the number of rows. Click on OK it will give the below result.
If you put 0 as Number of Rows then it will return an empty table with no rows as shown below.
Keep Duplicates
Keep rows containing duplicated values in the currently selected column.
Keep Errors
It keeps rows containing errors in the currently selected columns. If the selected column doesn't contain any errors then it returns as an empty table with no rows as shown below.
If the selected column contains error then the Keep Errors will give the following result. To generate the error in a column for learning Keep Errors you can change any selected column to date, the whole number or decimal number which has text in value. After that to see the Keep Errors result, select that column and click on Keep Errors.
Remove Rows
As described above, Keep Rows is used to keep the top N number of rows in a table while Remove Rows is used to remove or delete the N rows in a selected table.
The functions provided by the Remove Rows are as below,
- Remove Top Rows
- Remove Bottom Rows
- Remove Alternate Rows
- Remove Duplicates
- Remove Blank Rows
- Remove Errors
Now, let’s discuss each function of Remove Rows in details
Remove Top Rows
It removes Top N number of rows from this table. Once you click on Remove Top Rows it will ask you to enter the value of N. Then, the value of N should be non-negative integer including 0. I have highlighted top 5 rows in below image.
Now we want to remove those rows from this table. So, we need to click on Remove Top Rows option of Remove Rows. It will open the below window where we need to specify the value of N which is non-negative integer value.
Now click on OK it will give the below result after removing 5 rows. Now from the below image, it is very clear that the sixth row as shown in above image has become the first row of the table. Because it has removed the other 5 rows which were at the top of the table.
While working with Remove Top Rows we can put 0 as N. But, there is not any meaning of that. Since, when you put 0 it will not remove any rows. Alternatively, we can say it will remove 0 rows from the top.
Remove Bottom Rows
It removes the N rows from the Bottom of this table. It works in the same manner as the Remove Top Rows works. The only difference between both is that Remove Top Rows removes the row from the top while Remove Bottom Rows removes the row fro the bottom.
For using Remove Bottom Rows we need to click on Remove Rows and select the option Remove Bottom Rows. Again we need to enter the value for N i.e., number of rows to be removed from bottom. The value of N can be non-negative integer including 0. Removing 0 rows from bottom has no meaning at all.
Remove Alternate Rows
It deletes the rows based on the pattern. It takes three rows as inputs which are First row to Remove, Number of rows to remove and Number of rows to keep. All these inputs are of non negative integer value. First row to remove can’t be 0. To understand this concept I have added an Index column. To know more about Index Column please have a look at my previous article.
Once you click on Remove Alternate Rows the below window appear.
Based on the input as provided above it makes a pattern to table to delete which is described below
- First row to remove- As we have entered 5 so it will not affect for the rows between 1 and 5. It will start deleting from row number 5.
- Number of rows to remove- As we have entered 2 so it will remove 2 rows starting at row number which is at the position 5. In this way it will delete row number 5 and 6.
- Number of rows to keep: As we have entered 3 so it will not delete three row after first delete. In this way it will not delete row number 7, 8 and 9. As 2 rows has to be deleted and starting at 5 and three rows to be skipped.
This pattern will be repeated again and again for the all records of tables. So, again it will remove 2 rows and skip 3 rows to not delete. To understand it see below.
As in the above image we can see the pattern that after removing two rows it has skipped 3 to remove. Once you click on OK as in above image the result of Remove Alternate Rows based on the pattern will be as shown in below image.
In this way we have implemented some of Remove Rows functions. Now we shall implement some more Remove Rows functions which are Remove Duplicates, Remove Blank Rows and Remove Errors in details below.
Remove Duplicates
It removes rows containing duplicated values in the currently selected columns. Below is the result of Remove Duplicates based on the column Ship Mode.
All rows containing duplicated value of Ship Mode has been removed and only first occurrences of each Ship Mode are left.
Remove Blank Rows
It removes all blank rows from the tables.
Remove Errors
It removes all rows which contain errors in the selected column of the table. To describe this function I have imported a new excel sheet as data source and table which are as below. Here in the Effective From column is mostly as date format. This column also contains some text value like ABC, MNO, XYZ, etc.
Now we shall convert this column into date format and it will give error as below in place of text value.
Now, we shall select the Effective From column which has error and click on Remove Errors function. Now, it will give the following result. It has returned the empty table with 0 rows because it has skipped all the rows which has Error in Effective From column.
Summary
In this article, we have learned about implementing variable in DAX query in Power BI. We have also learned what Keep Rows and Remove Rows in Power BI are and implemented all these step by step.
Hope you will learn from and enjoy this article. You’re welcome to like, comment, share and any type of suggestion for my appreciation.