Home Tab in Query Editor: "Keep Rows" option in home tab of query editor in Power BI: Lesson-7 P-20

Home Tab in Query Editor: "Keep Rows" option in home tab of query editor in Power BI: Lesson-7 P-20

IT 25.03.2024   0 Comments  Add to Reading List

The "Keep Rows" option in Power BI of Query Editor:

The "Keep Rows" option in the Home tab of the Power BI Query Editor allows you to filter your data table by keeping specific rows based on various criteria. Here is an error:

Detect option:

  1. Open Power BI Desktop.
  2. Go to the table you want to edit.
  3. Click "Transform Data" to launch the Power Query Editor.
  4. In the Question Editor ribbon, find the "Home" tab.
  5. Within the "Home" tab, find the "Keep Queues" drop-down menu.

Using "keep rows":

The drop-down menu offers several ways to filter and place specific rows:

  • Keep Top Rows: This option allows you to keep a specified number of rows from the beginning of your table. Enter the required number in the "Number of Rows" field.
  • Keep Bottom Rows: Same as above, but keeps the specified number of rows from the end of the table.
  • Keep Rows in Range: This option keeps rows within a specified range that you specify. Enter the start and end row numbers.
  • Keep Duplicates: It keeps rows where a specific column has duplicate values. You need to select the relevant column first.
  • Keep Errors: It keeps rows where there are errors or invalid data in a particular column. Again, select the relevant column first.

Choose the correct option:

The best option depends on your goal:

Analyzing Early Trends: Use "Keep Top Rows" to focus on the beginning of your data.
Identifying outliers: Use "keep rows below" to check for possible anomalies at the end of the data.
Focusing on a specific time frame: Use "Keep Rows in Range" to analyze data within a specified time period.
Analyzing Duplicate Data: Use "Cap Duplicates" to understand data inconsistencies.
Troubleshooting Data Quality: Use "Cap Errors" to identify rows with incorrect values for further cleaning.

Additional tips:

  • You can combine the "Keep Rows" option with other filtering methods in Power Query for more complex data manipulation.
  • The Power Query Editor provides a preview window to see how your selected option affects the data table before applying the changes.

By effectively using the “Keep Rows” option, you can refine your data in Power BI to focus on the most relevant information for your analysis.

Lab Work of Keep Rows

"Keep Rows" drop-down menu.

1:- Keep Top Rows

  • Keep only top N of rows from table

  • write number of rows & Click OK

then you have only 3 top rows in table

2:- Keep Bottom Rows

  • Keep only the Bottom N of rows from table

  • write number of rows & Click OK

then you have only 3 bottom rows in table

1:- Keep Range of Rows

  • Keep only from-to Rows

 

  • write start N row and number of rows & Click OK

then you have only 3 rows start from 2nd row

1:- Keep Duplicates 

  • Keep only same Rows
  • Select a column (we select city column from table)

  • Lahore & Gujrat are duplicate cities 
  • Keep Rows --> Keep Duplicates

then you have

1:- Keep Errors

  • Keep only buged Rows
  • Select a column (we select city column from table)

  • this column have no any error
  • Keep Rows --> Keep Errors

Then you have no any row

I hope you wil enjoy this document:

 

 

Tags: