Microsoft Excel is a popular spreadsheet application widely used for data analysis and management.  One of the essential skills for working with filter data in Power Query in Excel. Filtering allows you to focus on specific subsets of your data, making it easier to analyze trends and identify patterns.

While Excel offers basic filtering options, Power Query provides a more robust and flexible way to filter data. Power Query, also known as Get & Transform (in older Excel versions), is a built-in data manipulation tool that empowers you to perform advanced filtering and cleaning tasks on your data.

Accessing Power Query Editor

Select the data range on which you want to apply filter using Power Query.

Filter Data in Power Query

Go to the Data tab on the ribbon and click on From Table/Range. If the selected data is not a table then Power Query will automatically convert the data into a table.

Filter Data in Power Query

Power Query Editor will launch, displaying your data.

Filter Data in Power Query

Filtering by Date and Time

Click the down arrow in the header of the date/time column you want to filter.

Filter Data in Power Query

You’ll see various filter options based on your data type. Use options like EqualsBeforeAfter, or Between to filter by specific dates or date ranges.

Filter Data in Power Query

Click OK to apply the filter.

Filter Data in Power Query

Filtering Text Data

Click the down arrow in the header of the text column you want to filter.

Filter Data in Power Query

Choose Text filters.

Filter Data in Power Query

Utilize options like ContainsDoes Not ContainBegins With, or Ends With to filter based on text patterns.

Filter Data in Power Query

Enter the desired text criteria and click OK to apply the filter.

Filter Data in Power Query

Filtering Numbers

Click the down arrow in the header of the number column you want to filter.

Filter Data in Power Query

Select Number Filters.

Filter Data in Power Query

Choose options like EqualsGreater ThanLess Than, or Between to filter based on numerical values.

Filter Data in Power Query

Enter the desired numbers and click OK to apply the filter.

Filter Data in Power Query

Filtering with Limited Choices

For columns with a limited number of unique values (e.g., color column), click the down arrow in the header.

Filter Data in Power Query

You’ll see a list of all unique values. Simply check or uncheck the boxes next to the values you want to include/exclude in your filtered data.

Filter Data in Power Query

Filtering Percentages

If your percentage data is imported as a decimal, click on the data type name and choose Percentage.

Filter Data in Power Query

Then, follow the steps for filtering numbers as mentioned earlier.

Keeping and Removing Rows

Select the column you want to use for filtering by rows.

Filter Data in Power Query

Click on Keep Rows or Remove Rows under the Home tab in Power Query Editor.

Filter Data in Power Query

Choose options like Top RowsBottom Rows, or Range to specify which rows to keep or remove.

Filter Data in Power Query

Closing and Loading Your Filtered Data

Once you’re satisfied with your filtering, click Close & Load.

Filter Data in Power Query

Power Query will create a new sheet in the Excel containing the filtered data.

Filter Data in Power Query

Conclusion

By mastering Power Query’s filtering capabilities, you can significantly enhance your data analysis workflow in Excel. This guide has equipped you with the essential steps to filter data by dates, text, numbers, and more. With practice, you’ll be able to leverage Power Query for complex filtering tasks, transforming your raw data into valuable insights.

Download Practice File

You can also practice this through our practice files. Click on the below link to download the practice file.

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *