Case Insensitive Filtering Using Power Query
Excel in Office 365 has a new set of a feature known as Get & Transform. It provides a platform to perform a series of steps to help you to transform your excel data. Even you can do case insensitive filtering.
In this tutorial, we will mention the process of doing case insensitive filtering by using power query or get & transform in excel this going to be helpful for you.
You can also browse our Power Query Tutorial Catalog here.
NOTE 1: You can create random excel workbook online here for practice purposes as I did in this tutorial.
NOTE 2: If You use Microsoft Excel Prior to 2016 Version, then You Need to download Power Query Separately.
Here is our workbook example;
STEP1. Firstly, select your data and press the shortcut Ctrl + T or by going to Insert > Table then it will turn into Excel Table.
STEP2. Now in second step follow this procedure:
Go to Data > Get & Transform > From Table
STEP3. After completing the second step you will open up the Power Query Editor.
Here we are going to filter out Vietnam in the column of country. It has different capitalizations so it will be difficult to filter this out in one go.
So we need to do add a new column. Make sure that country column is selected, and then follow this:
Go to Add Column > From Text > Format > Uppercase
Now this will make easy to do filtering because all values are now VIETNAM:
STEP4. Make sure VIETNAM is unticked and click the down arrow of the new Upper-case column, and then click OK.
STEP5. Now you don’t need this column anymore so remove this column by right-clicking and then selecting the remove option.
STEP6. Now from home option-click the Close & Load, this will open up a new worksheet in the Excel workbook with all the updated records.
Now you have successfully done by your case insensitive filtering of excel.