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?
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’.
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.