Dependent Dropdown Lists in Excel with Power Query & Dynamic Arrays

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 4x5 label won't fit on our 3-ounce spray bottle, and a 1x1 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:


#DynamicArrays
#DropdownLists
#DataValidation

======

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



Website:

My book: Guerrilla Data Analysis 2nd Edition


My old blog:

dependent drop down lists,excel drop down list,dependent drop down list,Oz du Soleil,Power Query,Unpivot in Power Query,prevent crazy entries in Excel,COUNTIFS in Excel,data validation,cascading drop down lists,modeling in excel,wingdings font in excel,wingdings font,Excel on Fire,drop down list,spilled arrays,excel array formulas,leila gharani,excel tips,dependent dropdown lists,