Begins with, Ends With, Contains missing in PowerQuery Filter

If you use PowerQuery (if you don’t learn as quick as possible), you may notice that in the filter, some of the options you want to use seem to be missing. So why is ‘ Begins with, Ends With, Contains ‘ missing from some PowerQuery filters?

YouTube Get back the Begins with, Ends With, Contains missing

Watch, like, subscribe

Why are Begins, Ends, Contains Missing from PowerQuery options

In the example below I want to remove any row that CONTAINS the word ‘Total’, but when I click on the filter dropdown and choose FILTERS, it only allows ‘Equals’ and ‘Does Not Equal’.

Begins with, Ends With, Contains missing

This is due to the importance that PowerQuery places on the specified type of data in the column. Notice that on the left of the column label ‘Vender no’ it says ABC123. This implies that this column is General. With a General format you only get these limited filter options.

However, as shown below, when you change the format to TEXT (the label will now include ABC in the name area), the dropdown says Text Filters and all the useful options are now available. So in the case below I can now remove all the Total lines by choosing Does Not Contain.

Learn more about PowerQuery via the Online PowerQuery course.

Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course  or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.

Where to look for PowerQuery (or Get and Transform)

Switch off PowerQuery Conditional Column wizard

Easy fix for promoting headers when name can change issue in PowerQuery

Power Query close and load to greyed out