Dependent Dropdown Lists in Excel with Power Query & Dynamic Arrays

/in /by

When we build models in Excel we must anticipate crazy entries. In this tutorial we try to prevent crazy matches between labels and containers. A 4×5 label won’t fit on our 3-ounce spray bottle, and a 1×1 label would look crazy on a 1-gallon jug.

First, we use Power Query and Unpivot to organize our source data. Then, we use the new functions UNIQUE and FILTER to make the dependent drop down lists, taking advantage of Excel’s new calculation engine.

Those work fine, but there’s a known problem with dependent dropdown lists (aka cascading dropdown lists): it’s easy to end up with crazy matches because the lists don’t re-set. It’s easy to select something with the child list and then change the parent list to something crazy. I handle this by using COUNTIFS and the WingDings font to flag crazy entries; i.e. create an alert.

Here is a link to Leila Gharani’s video where she solves this problem by using dependent combo boxes: https://youtu.be/aSPtWo3IiOM

#DynamicArrays
#DropdownLists
#DataValidation

======

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2