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.
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.
Power Query Editor will launch, displaying your data.
Filtering by Date and Time
Click the down arrow in the header of the date/time column you want to filter.
You’ll see various filter options based on your data type. Use options like Equals, Before, After, or Between to filter by specific dates or date ranges.
Click OK to apply the filter.
Filtering Text Data
Click the down arrow in the header of the text column you want to filter.
Choose Text filters.
Utilize options like Contains, Does Not Contain, Begins With, or Ends With to filter based on text patterns.
Enter the desired text criteria and click OK to apply the filter.
Filtering Numbers
Click the down arrow in the header of the number column you want to filter.
Select Number Filters.
Choose options like Equals, Greater Than, Less Than, or Between to filter based on numerical values.
Enter the desired numbers and click OK to apply the filter.
Filtering with Limited Choices
For columns with a limited number of unique values (e.g., color column), click the down arrow in the header.
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.
Filtering Percentages
If your percentage data is imported as a decimal, click on the data type name and choose Percentage.
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.
Click on Keep Rows or Remove Rows under the Home tab in Power Query Editor.
Choose options like Top Rows, Bottom Rows, or Range to specify which rows to keep or remove.
Closing and Loading Your Filtered Data
Once you’re satisfied with your filtering, click Close & Load.
Power Query will create a new sheet in the Excel containing the filtered data.
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.
