Power Query with data validation: Change source data from a list (drop down) based on value

Question

Before I start to explain - I am not providing any data or else, because I need an idea first, to see if it is possible (if it is easy).

Imagine you have one folder with X different subfolders inside, and every of them is filled with twenty text files (.txt), with same structure and length. What I normally do is uploading one folder Data > From File > From Folder in Excel and then doing certain transformations and saving it in an Excel file. That is what I am doing like X times for all subs. It is not super time consuming because I know how to change "Path" in Advanced editor, and with Refresh button is exceptionally smooth. BUT...

If I want to have for example one drop down list with those "subs" and every time when I change it on drop down with refresh button - my data set is in a minute refreshed. How to do that? Parameters or function in PQ? That means I could avoid going into PQ editor or Changing source manually...

Any ideas or suggests?

Answer 1

You can create a named range in Excel which is just a cell with the subfolder name. Using data validation you can make that cell into a dropdown based on a list you define in a separate range.

Once you've done that, you can load that named range into Power Query and insert it as part of the folder path as in this question related to using a cell value in a query URL.