Filter unnecessary rows

Non-data rows, such as ones that separate orders or show page numbers, can get in the way. For example, our sample data has summary rows after each sales rep's daily transactions. But these rows make it harder to summarize the data, so you can remove them with a simple filter. In more complex scenarios, you might end up performing a series of filters to get what you want.

1. Select the filter drop-down list from the Product Name column.

2. Select Text Filters > Does Not Begin With, and then enter the word "Total". You can also clear  the Total Sales Rep option.

3. Select OK.
   
   Power Query will filter out those rows that contain the word "Total" and keep just the records you want.

4. To return the transformed data to an Excel worksheet, Select Home > Close & Load.