Power Query Features Related to Rows

This article is about cleaning and transforming the data using the Power BI query editor. Power Query has many features that will help you clean and prepare your data for analysis. In this article, you will learn about the features related to rows.

To open Power Query Editor, select the Transform data option on the Home tab of Power BI Desktop.

Power Query Editor will open, displaying your data in a tabular form. Then, you can start using Power Query features.

The features related to rows are as follows:

Promote Header

A data source might have a first row that contains column names. You need to promote the first table row into column headers to correct this inaccuracy. You can promote headers in two ways: by selecting the Use First Row as Headers option on the Home tab or by selecting the dropdown button next to Column1 and then selecting Use First Row as Headers.

The result is shown in the below image:

Keep Rows

On the Home tab, select the Keep Rows dropdown. It will show you options to choose which rows you want to keep.

Keep Top Rows, Keep Bottom Rows, and Keep Range of rows options will ask you for the number of rows you want to keep irrespective of the column you have selected.

Keep Duplicates and Keep Errors depends on the column you have selected. For example: If you select Country column and select Keep Duplicates, it will keep the rows where there is more than one row of a particular country (USA and France in this example) and will remove all the rows where a country has a single row (like Norway in this example).

Remove Rows

On the Home tab, select the Remove Rows dropdown. It will show you options to choose which rows you want to remove.

Remove Top Rows, Remove Bottom Rows, and Remove Alternate rows options will ask you for number of rows you want to remove, irrespective of the column you have selected.

Remove Duplicates and Remove Errors depends on the column you have selected. For example: If you select Country column and select Remove Duplicates, it will remove the rows where there is more than one row of a particular country (USA and France in this example) and will keep all the rows where a country has a single row (like Norway in this example).

Remove Blank Rows will remove all the completely blank rows of the table.

You can use these features according to your requirement to clean and transform data and get more accurate and insightful results.