0

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?

MmVv
  • 553
  • 6
  • 22

1 Answers1

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.

Alexis Olson
  • 38,724
  • 7
  • 42
  • 64
  • Good point, but what if in my folders are .txt files, that needed to be Edited at first place? This coding is if I have workbook (xlsx) but what if not? – MmVv Oct 28 '20 at 20:25
  • I don't follow. If your folders are .txt files then you have a different question... – Alexis Olson Oct 28 '20 at 20:29
  • But it was stated in my question... "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." Subfodlers = text files inside – MmVv Oct 28 '20 at 20:38
  • Sorry, I misread your comment slightly. You should still be able to load and transform text files inside the subfolders using the load from folder process. The steps in the transformation will be different due to a different data type but that doesn't change the method of accessing the files I sketched above. – Alexis Olson Oct 28 '20 at 22:12
  • Yes..it can be done =) check also this video: https://www.youtube.com/watch?v=0NX-GctfZuU&t=163s&ab_channel=ExcelIsFun I found it even easier – MmVv Oct 29 '20 at 08:23
  • Yep. That video is doing what I suggested (but with much more detail). – Alexis Olson Oct 29 '20 at 13:40