How to Fix “Begins with”, “Ends With”, “Contains” Missing in Power Query Filter

If you use PowerQuery (if you don’t learn as quickly 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?

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.

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.