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:
https://www.zazzle.com/excelonfire
For an intro to Power Query try my Lynda.com course:

Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition
http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336
My old blog: http://datascopic.net/blog-2-2

When asking for help with a spreadsheet, it’s best to distill the need down to concepts. It might not be the real work that you need help with, or even the real data. However, by distilling a challenge down to the fundamental concepts and building nonsensical models, we can see our way past technical jargon, complex workflows and data spread across multiple sources.

Otherwise, we’re trying to wrestle an octopus. And for those of you who’ve have that misfortune, you know that wrestling an octopus is miserable.

For an intro to Get & Transform (Power Query) try my Lynda.com course:

Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition
http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336
My old blog: http://datascopic.net/blog-2-2

ExcelWeekend 4 was a blast! Here, the founder, Cristiano Galvão and I review the event, and you get to see pictures from the event and my visit in São Paulo, Brazil.

For an intro to Get & Transform (Power Query) try my Lynda.com course:

Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition
http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336
My old blog: http://datascopic.net/blog-2-2

Here is a how-to for importing Excel files from a folder using Power Query (Get and Transform). It’s fairly easy to do.

HOWEVER, in this example, I’m sharing a folder with someone who starts putting goofy stuff into the folder and they break the Power Query query and the process. Now what?

One move is confrontation. Tell the person to ” knock it off!” Another move is to modify the query.

Check out the video. Please comment. Share your thoughts. Ask questions.

The song in the video is “Quintal do Céu” by Seu Jorge

For an intro to Get & Transform (Power Query) try my Lynda.com course:

Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition
http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336
My old blog: http://datascopic.net/blog-2-2