Power Query or In Excel 2016 ‘Get & Transform’ is helpful for you to transform your Excel data. One benefit you have with this is that you can perform: filter records. And the actual beneficial part is you can use an OR condition.
This is definitely not easy to do in Excel, but with Power Query it will easiest with only four steps.
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 I need to filter records which have at least one of the following two conditions:
First starts with the letter “R” OR has a country of “India”.
STEP 1: firstly you have to select your data and press the shortcut Ctrl + T or by going to Insert > Table to turn it into an Excel Table.
Version:1.0 StartHTML:000000222 EndHTML:000182228 StartFragment:000181166 EndFragment:000182139 StartSelection:000181166 EndSelection:000182130 SourceURL:https://exceltutorial.net/extract-length-using-power-query/ How To Extract Length Of The Text Using Power Query | Excel Tutorials
STEP2. Now in the second step follow this procedure:
For Office 365 or After 2016: Go to Data > Get & Transform > From Table
For Excel 2013 & 2010: Power Query > Excel Data > From Table
STEP 3: Now you have an open Power Query Editor so follow this:
Select the Text Filters > Begins With by going to the Filter Arrow of Name.
Now you are going to see the main thing.
Select Advanced but first get ensure about your conditions are:
- Full Name begins with R
- Or Country equals India
Now Click the OK option.
STEP 4: In last step from Home tab Click Close & Load.
Here this will opens the new worksheet in your Excel workbook which will be having the updated table.
Finally you got your new table with the filtered rows.