The TRUTH about Filter - Advanced filter techniques in Excel and Power Query

[@crabby786]

i love this channel

[@IvanCortinas_ES]

Excellent comparison between Excel and Power Query. I think it's a very interesting way to get familiar with Power Query, because when you see it like this, a person understands the logic. Thanks for the tutorial, Mark.

[@ExcelOffTheGrid]

Thanks, I’m glad it was helpful. 😀

[@TariqAli-ro4ko]

Very interesting! Thanks

[@kebincui]

Happy new year Mark. Excellent video as always. In particular this one helps us to understand better the work mechanism of filters in Excel and PQ. Thanks again for your awesome work 👍❤.

[@ExcelOffTheGrid]

Happy new year! Kebin. I’m glad you liked it.

[@chrism9037]

Epic, thanks Mark!

[@ExcelOffTheGrid]

Thanks. 😀

[@GeertDelmulle]

I use both but I find PQ-M more straightforward and thus more intuitive to use, also in no small part because of the Excel naming convention. Thanks for the video; Mark.

[@ExcelOffTheGrid]

Interresting - I would have thought FILTER more intuitive. You’re a bit of a legend with both, so I guess you can switch between either.

[@GeertDelmulle]

@@ExcelOffTheGrid Thanks for the compliment - I hope I deserve it. Yes we can do this both in Excel Classic and PQ-M. It’s just less intuitive in Excel because you need functions like Search, IsNumber or ISNA, and things like that (so, it’s not FITLER as such) whereas in PQ-M the logic and the functions are more straightforward, IMO. I mean: what to think of terms like REDUCE, SCAN, MAP, lifting, pairwise lifting and broadcasting? I LOVE the concepts and using them, but who invents those names? Then I prefer terms like List.Accumulate and List.Generate. :-)

[@tyjulianks]

Without the CHOOSECOLS function, is there another solution to get the Non-Adjacent Columns with FILTER?

[@ExcelOffTheGrid]

CHOOSE, INDEX & HSTACK can all be used to return non-adjacent columns.

[@vadimcherepanov3339]

Actually first time u filter even earlier on the server side when querying data from data source e.g in sql query

[@JoseAntonioMorato]

Dear Mark, I have added, to your formulas in Excel, instructions for the name and region data to be sorted in ascending order, the values in descending order and, in case of a blank cell, all the data to be displayed and sorted in the order cited: =SORT(FILTER(Example1,ISNUMBER(SEARCH(String[@String],Example1[Item]))),{1,2,3},{1;1;-1}) - Example 1 =SORT(FILTER(Example2,ISNUMBER(XMATCH(Example2[Item],List[List])),Example2),{1,2,3},{1;1;-1}) - Example 2 =SORT(FILTER(Example3,(Example3[Item]=Show[@Show])+ISBLANK(Show[@Show])),{1,2,3},{1;1;-1}) - Example 3 I hope you like it. 🤗

[@ExcelOffTheGrid]

Sort is a nice addition. 👍