Magic of Lists in Power Query

Lists are a feature or output type in Power Query that doesn't get utilized nearly enough. I recently wrote three articles on my Excel Unplugged blog about the use of Lists to solve big tasks (links below). But with nearly all of those use the List.Contains function. But in this video, we will tackle the age-old problem. How to filter a list of dates to only working days. This is what HR needs for calculating days of leave. We will be utilizing two new Power Query Functions, List.Intersect and List.Count.

This tutorial was made using Excel 365, but all methods work with Excel 2010 and above. Keep in mind that for Excel 2010 and 2013 Power Query is a separate AddInn you have to install.

Original Excel files used in the Video if you want to follow along:
Start File: https://bit.ly/3ndT6sG
End File: https://bit.ly/3FewKgT

Links to other videos and blog posts that are relevant:
Filtering a Table to a Dynamic List of Values using Power Query and Power BI -    • Filtering a Table to a Dynamic List of Val...  
FIltering a Table to a Dynamic List of Values - Blog Post: https://bit.ly/2Pf7v9V
Filtering a Table to Top 10 items - Blog Post: https://bit.ly/3ndAoS8
Dynamic List of Columns to Keep - Blog Post: https://bit.ly/3kAuuJ6

Chapters:
0:00​ Excel Olympics Animation
0:08 Introduction to the Video
0:40 Introduction to the problem we will solve
2:52 Importing Tables to Power Query
3:44 Creating the full list of dates
4:06 Basic List Creation Syntax
5:21 Dealing with Errors with "try otherwise"
6:30 List.Intersect in Action
8:10 List.Count
8:35 "try otherwise" again
10:16 Conclusion
12:38 Outro


Gear Used on my end:

US Links
Camera: Canon EOS R6 - https://amzn.to/3e9zxML
Microphone: AKG C3000 (Analog Mic) - https://amzn.to/3tYd37w
Audio converter: Zoom UAC-2 - https://amzn.to/3dgM8wS
Laptop: Dell XPS 15 - https://amzn.to/3w8CyoK
Lights: Elgato Key Light - https://amzn.to/3rurte7

EU Links
Camera: Canon EOS R6 - https://amzn.to/3tubwGy
Microphone: AKG C3000 (Analog Mic) - https://amzn.to/32pi2m0
Audio converter: Zoom UAC-2 - https://amzn.to/3amDRXP
Laptop: Dell XPS 15 - https://amzn.to/3effMmT
Lights: Elgato Key Light - https://amzn.to/3vcs6v9
Green Screen (visible in bloopers): https://amzn.to/3ajUm6Y

Recorded With: Camtasia - https://www.techsmith.com/video-edito...

Subscribe for more Excel tutorials on the Excel Olympics YouTube Channel: http://bit.ly/EOYTsub

Subscribe for the Excel Olympics Newsletter: https://bit.ly/3w3rkjK

Disclaimer: Some links included in this description might be affiliate links. If you purchase a product or service with the links that I provide, I may receive a small commission with NO additional charge to you! I appreciate your support!

#Excel #ExcelOlympics #gasperkamensek