Excel Magic Trick 1349: Power Query with Input Variables from Excel Sheet to Extract Records

Download Files:

See how to use Power Query (Get & Transform) with Input Variables from Excel Sheet to Extract Records and create a Revenue Report. Here are the steps:
1. (00:15) Introduction including Preview of Finished Solution and Preview of steps necessary to accomplish this goal.
2. (02:00) Import and transform Data Set, including removing Columns, Filter For Records and adding an extra Column to calculate Net Revenue using the Number.Round Power Query Function.
3. (06:38) Load Report to Sheet
4. (07:09) Import each Parameter/Criteria Table.
5. (07:36) Convert each Criteria Table to a single Text Item using the Record.Field Power Query Function.
6. (11:38) Declare and Insert Variables into M Code for Report
7. (14:10) Test Reporting System
8. (14:30) Create Recorded Macro To Refresh All and assign it to a Form button
9. (16:40) Test Macro and Reporting System
10. (17:25) Summary

[jhanawa]

This was awesome. I was able to create a table, pull it in, convert it to a list and use List.Contains in my power query. This allows me to give my users control over some of the variables in the reporting!!!

[sudharshanr]

Amazing to see such videos helping folks even after 5 years after publishing! Great video as ever Mike!

[preetsingh]

Thanks for your valuable Transformation of knowledge through this video
I just practically learned which you had explained 7 years ago

[zt.]

I have opened a new playlist to save the Power Query Magic Tricks that I cover. In addition to that, and the by default in playlist videos: E-DAB, MSPTDA, Excel Dragons etc, I have two: one: ExcelIsFun general and one: ExcelIsFun Finance. Those Finance videos are great: you put just as much effort into teaching finance as into teaching Excel. And hand-written notes! Thank you.

[FredrikDahlberg]

Just found this!! You are my hero! 🙏

Next step is to learn how to make the parameter for the criteria dynamic.

[dcoltonbrown]

You are awesome. My go to when I am looking for an answer!

[TheHightower]

This honestly save my life on a project I am working on. If you ever think you are not helping people, know that you helped me TREMENDOUSLY.

[michaelresau]

Fantastic video. I made my selection list from another power pivot table slicer and used these steps to filter all my other power query tables successfully. This video addressed my challenge directly and walked me right into a solution. Thank you!

[joesmith]

Outstanding. I was able to trim a 10 MB excel file down to 40 KB. and the query runs WAY faster.
OLD: SQL query results loaded into excel and VLookup to get information.
NEW: Query with parameter.

AWESOME.

[houstonsam]

Just delivered something functionally similar at work using the conditional extraction worksheet formula (largely learned from you) with dynamic ranges to feed the output data to the appropriate charts. Have not yet parameterized my queries; need to learn this method now.


Excel is really an amazing tool. Thanks for the consistent patient clarity you provide in teaching us.

[cezaryczajka]

Great guide. As always recorded so that you can turn off the sound and still know what and how. You can see that it is a powerful tool for Excel & PQ. Thanks to such professionals like you, we can learn a lot. Thank you very much and I look forward to more.

[sayaligawade]

Yes, Excel is really fun bcoz of people like

[mattschoular]

You make challenging tasks so simple. That's why ExcelIsFun. Thanks Mike...

[CarloCatapano]

Awesome! Thanks to this video I met powerquery and managed to open a 2 GB CSV file by setting customized queries and outputs.

[LotfyKozman]

Everyday you take us much farer to show the beauty of the legal marrage between Excel and Power Query solutions. Thanks

[paspuggie]

As you know Mike, I always love your videos and I'm really grasping the Power of Power Query...it's BRILLIANT ! One observation I notice with most videos on YouTube is people navigating to the Data Tab then Refresh/RefreshAll etc....a more productive approach would be to add the icons to the Quick Access Toolbar...makes a huge difference to productivity...hope you agree :)

[OzduSoleilDATA]

THANK YOU! I was struggling to understand what people meant by parameters and "passing a variable" This video not only helped me understand, but also showed clearly how it's done. AMAZING. 🔥🔥🔥🔥

[danielsandovalarias]

Wow This is exactly what I was looking for. I am trying to filter a date column based on a cell value. Amazing! I'll try it!

[mirrrvelll]

Oh man...you know, every time when I open your video - first all first is to give you a like on the video, and then I am following it. Because I know I am going to learn something super useful. Thanks for this tip, I exactly needed this for my work and some tasks to automate it =)

[ahmedkammoun]

Your videos are just AMAZING ! thanks for all this effort, i'm an Architect, and this tricks helped me alot in my work, BIG thanks !