Excel Magic Trick 1353: Power Query: Extract Records w Merge Feature & Inner Join on Parameter Table

Download Files:

See how to use the Power Query (Get & Transform) Inner Join Merge Feature to Extract Records (Data) from an Excel Table based on Criteria from cells in an Excel sheet (Criteria in a Parameter Table):
1. (00:16) Introduction
2. (01:49) Convert Criteria for Data Extract to an Excel Table (Parameter Table), including adding Data Validation List to the cells in the Table.
3. (03:27) Import the Two Excel Tables into Power Query (Get & Transform) using From Table button
4. (04:54) Use Merge feature with an Inner Join to merge tables and extract desired records (data). Trick thanks to pmsocho at YouTube.
5. (08:15) Test with a single Condition
6. (09:10) Test with two Criteria and Refresh to get Extract Records. See an OR Logical Test
7. (09:59) Compare Advanced Filter Method of Extracting Records using an OR Logical Test
8. (12:00) Summary

Reference Videos:
Excel Magic Trick 1346: Array Formula: Split Master Table into Sub Tables, Each Product on New Sheet
Excel Magic Trick 1347: Power Query Function: Split Master Table into Sub Tables for Each Product
Excel Magic Trick 1349: Power Query with Input Variables from Excel Sheet to Extract Records
excel-654 - Power Query - zapytanie z parametrami dotyczącymi jednej kolumny
pmsocho
Thanks to pmsocho at YouTube

[DougHExcel]

Just gotta love Power Query. I've found that I use this much more now than trying lookup functions.

[michaeljones]

Awesome example, Power Query is really a great tool
Thanks again for the example and yes, PQ does rock!!!

[zaighamuddinfarooqui]

Very nice as usual.
We can record a macro while extracting data through Advance Filter and same may be assigned to a button so by a single click desired data could be extracted. By a little editing in VBA we can also get results of different criteria on multiple sheets.

[pmsocho]

Great! Thanks for yet another way to have fun with Excel!

[gcnewd]

Thank you You Guys! that is awesome.
I will have to find a use for this and do it as soon as possible... so cool

[BillSzysz]

I'm working with Mike.... hooray!!!

[Sal_A]

Thanks for sharing.  I would create a macro to refresh the PQ output based on the change in the drop down....to avoid right clicking and refreshing.  It would be more robust.

[marounsader]

that was smart!!
is it possible to make two variables from different columns using this method maybe we can make another query and hit refresh so both queries work together to give us the result.

[mohamedchakroun]

Very Coolll looking for the next video :-)

[noticebomber]

Nice tip
But why are a pivot and slicers not being used?

[donovannewton]

So if you use this method can you duplicate the information n times per person? And then concatenate the name to say “Name(1), Name(2)”....etc?

[vdpeer]

Where'd you get that "Design" tab? I have 2016, but can't find it as an add-in.