Multi-Select files within folder

[rlaughli]

Hey everyone, 

Is anyone aware of a way to dynamically control which files within a folder to import in a query? So instead of importing all files from the folder at the same time, the idea would be to be able to pick and choose which files withing the folder to import at any given time. So, if there are 12 Excel files in the folder, one time I might like to import 3 of them, one time all 12, another just 1, etc. (the files are broken down by month). I'm aware that you can filter the files/records within the query steps but that is something I'd like to avoid having to do every time, if at all possible. 

The research I've done so far seems to point towards some kind of query parameter, but I haven't been able to find any examples online of someone explaining how this might be set up- it seems that the query parameter examples I've seen don't allow for multi-select from within a folder which is essentially the option I'd like to have. And without the query parameter, it seems that you have to just import ALL the files and nothing in-between. 

Any pointers would be greatly appreciated! 

[v-lionel-msft]

Hi @rlaughli ,

Actually similar questions have already been mentioned:

dynamically import files from a folder WITHOUT combining them  

Maybe you can submit an idea .

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rlaughli · Accepted solution]

Hey @v-lionel-msft ,

Thank you for the reply and the resource. The link provided was not initially useful for my use case, but an additional link within the comments gave me an idea of how to create a viable solution for this issue. I'm not sure if it's the most elegant way to handle this, but for my purposes, it does the job. Maybe someone else will be able to build on it.

Here's what I did:

- Let's say you had a parent folder and three subfolders that relate to the monthly data for 2020. I connected to the parent and then created three separate query parameters (only 3 because the end user would only like to look at any 3 months at a time, and mix and match from there) that each lists the subfolders as their values PLUS a fourth subfolder which I call "Blank" that has no sheet/data inside it.

- Once the parameters were set, I went to my main query and adjusted the M code in the source step to be something like this:

Folder.Files("C:\Folder\Folder_1\ParentFolder\"&Parameter) & Folder.Files("C:\Folder\Folder_1\ParentFolder\"&Parameter2) & Folder.Files("C:\Folder\Folder_1\ParentFolder\"&Parameter3)

- With this in place, I can set the respective parameters to include the files I would like to include in the main query. If I only want 2 of the 3, I set 2 of the parameters to the desired paths and set the third one to the "Blank" subfolder, which is then not included within the output. If I want all 3 files (all/any three months of the desired data), then I adjust the parameters to return what is needed and do not set any of them to "Blank".

As I said, this isn't the most elegant solution, but it works for what I need right now. If anyone comes up with anything better than this, I'm all ears! Thanks again for the entry.