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

Thanks Mark very useful.

I am using vstack and filter function ( vstack 2 filtered datasets). The data has numbers, names, dates, etc. the data, even though i press right click and format cells, does not show as numbers, dates,etc and i can not use manually filters on my columns. What can i do in order to make vstack work for me and have the right formats? I am using regular excel without power querries. Thank you❤

Excellent demonstration! Very useful to see the same technique in both Excel and Power Query! - Thank you Mark!

Fantastic, short video very clear explanation, thanks.

Great exposition. Thanks Mark!

absolutely amazing... Thank you sir for sharing such a wonderful information

Really useful to see the different methods in Excel and Power Query, wouldn't it be nice if the order was the same with the code).

Hi Mark ❤
Is there any solution to share a workbook has a pivot table
As I tried a lot but everytime a message unable to share because the sheet has a table or mxl map

Amazing. Thank you very much!

At my work, we have Excel 2016, so the easiest option is because using Table.Join works very well for filtering data as well. The second option would be VLOOKUP using arrays or helper columns. For practicality and maintenance, PQ wins this case without discussion

wow. 🥰😍

"Absolutely amazing! I can't express my gratitude enough. Such an enlightening experience! Thanks to you, we've undergone an incredible transformation."

Amazing video Mark, so love your work and your professionalism, you are just a person easy to fall in love with!

This is the solution for LAMBDA:

=LAMBDA(data_range, column_range, search_text,
FILTER(data_range,ISNUMBER(SEARCH(search_text,column_range)))
)

FORMULA:
=FilterBySearch(A4:C19,A4:A19,E4)

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

i love this channel

Very interesting! Thanks

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. 🤗

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 👍❤.

Epic, thanks Mark!

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.

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

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.