Passing a Parameter in Power Query: Find reviews that need to be done in x days

In this video we want to look up whose annual review is due within x days. Quickly list who's due in 30 days, 14 days, 90 days ...

To make this easy--especially if this Excel workbook is for someone unfamiliar with Excel--we'll set things up to "pass a variable" or "pass a parameter" into Power Query.

It requires a little M-coding. A little!

Passing a parameter or variable into Power Query seemed like a strange idea. Why not just use a filter? That was my thought until I came across an odd situation:

We need to import data from specific files, but never all at once. But it's a hassle to navigate from one file to another. It's better to go ahead and import all the data and then pass a variable to Power Query and let Power Query filter what we need and don't need.

Excel on Fire shirts and mugs:

My book: Guerrilla Data Analysis 2nd Edition

[joelabacherli]

I love this. What you described in the beginning about running out in the street was spot on to me just recently. These aha moments are what we live for and people like you are making those moments easier to attain. Thank you so much for your awesome videos.

[kebincui]

Thanks Oz for sharing. I use more drill-down of the paramter and then use the drill-down in the query editor

[robertlevak]

This is one of the most helpful videos I have watched. Thank you so much for the video!

[chrisbjork]

This is outstanding! I watched 5 other videos and read 2 articles on how to use parameters in power query and this is the best!! All the others were confusing or unclear in one area or another. You demonstrate this powerful tool simply and clearly. BAM! Great job!

[felixicecream]

Great presentation - that's just what I need . Thanks man.

[chavelooo]

You are a legend, first time that I watch a video from your channel but I just simply enjoyed it a lot. Thanks for the trick.

[vickieallen]

This was an awesome way to learn how to do this! I used the method and it worked, plus it was fun to watch!! Thank you!! MAKE MORE.

[wayneedmondson]

Hi Oz.. just came across this video from 2018. Very cool.. I solved it with a simple Drill Down on the Due Within table and then referenced that query from the dummy filter criteria that was set up on the Data table (just substituted the reference right in the formula bar). Worked as advertised. Thanks for all the great videos.. having fun and learning something new with each one. Thumbs up for Excel On Fire!

[PradipPardeshi]

I generally do this by merging queries and then choose the option -- Full outer join then I filter out null from the newly added column after expanding.

[adelawilliams]

Thank you so much for the video! You are very informative and entertaining. You do an excellent job of breaking down the whole process!

[barttitulaerexcelbart]

Great done Oz. Although I find M difficult. I tried to make a simpler solution: Merge Queries, Append (row of the parameter is added), fill up (all lines have now a parameter). then new column: If days <= parameter then 1 else 0. Filter for 1 and ready !! I will send the file by mail. Greetings, Bart

[MaisQuePlanilhas]

Amazing Oz! I love this Trick! I Used with path of the files to load. Thanks to share!

[enricodecrescenzo]

Great stuff Oz, as always!
I like opening the advanced editor and looking for customized solutions! PARAMETEEERS!!!!

[harshalpatil]

thanks, also liked the presentation method. good job

[DIGITAL_COOKING]

indeed it 's a beautiful technique 👍👍👍 I use it to select a product from the list
please can you give me the link for the video of excel is fun you talked about

[michaeldiamond]

Nice video as always. I always look forward to your videos because you are also "quite" the Entertainer!

You mentioned that you saw one of Mike's parameter videos, can you recall which on it was (could you provide a link to it! Oh, Leila told me that she was able to hang out with you at that the Microsoft summit a couple of weeks ago and she mentioned that she really enjoyed meeting and hanging out with you, kudos to you.

Michael

[kutra]

Greetings from Toronto! Great video like always Oz!
Also to clarify, could the same result have been achieved via Drill down on the "Due Within" table and then referencing the query name in the larger data table? My question stems from trying to understand the advantage of one method over the other. Thoughts?

[BEDSTUYKEV]

Hi Oz, what about adding multiple parameters? Adding Another filter based on the last name

[dasrotrad]

I have to book mark this! I'll never remember all that. But, that's ok. The Wizard Oz is here!

[sktneer]

If I am not wrong, you didn't need to declare the 'DaysDue' variable as the first line...

DaysDue = Days,


You could only replace the 30 with the query 'Days' like you did and it would still work because the variable 'DaysDue' was declared but was not used.