Good evening,
I know how to open a folder where all the files are in the same format and one big table results. But now I have a HelloKitty_Folder which contains 16 pipe separated value files. Each files has different columns so I don’t want one merged table as a result. But rather, 16 tables added to power query. In other words - each row below really needs to be it’s own table where I can then “open it up” by clicking on attributes (the screenshot of course only showing 3 files for this example)
Question: Could anyone tell me how to make each of the files in the HelloKittey_folder result in a table in their own right? HelloKitty_Folder.zip (2.4 KB)
Thanks as always,
Michelle
Hi @michellepace,
Hope I understood your query correctly… To the best of my knowledge (at this time) it’s not possible to dynamically add “new queries” but you could try something like this.
Disable load for the FolderContents query you’ve depicted above, use it as a staging query.
For each row in the table you want to add as a separate table to the model, create a new query by Referencing FolderContents, drill down into the [Content] and add additional transformation steps if required. If you have 16 files, you’ll have to repeat this set up for each file.
Create a new supporting query and disable its load.
Create another query to report on the any differences between the number of queries in your file and the number of files in the folder. In the service you can create a trigger, so you’ll get notified if a file is added and a new query needs to be incorporated in the model.
Important you will notice that in my example this relies on the fact that all query with a name starting with “Folder” are excluded from the count.
Here’s the sample (just update the FolderLocation parameter):
eDNA - Open files from folder.pbix (28.2 KB)
I hope this is helpful
Thank you so much for that detailed answer @Melissa
We hope this helped you @michellepace 
If not, how far did you get and what kind of help you need further?
If yes, kindly mark as solution the answer that solved your query.
Hi @ @michellepace, we’ve noticed that no response has been received from you since October 25.
Checking if you still need any help with your question?
In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.
Hi Melissa, thanks very much for the solution! I have a stable number of files in my folder so I’m the name of simplicity I’m just going to do them one by one. It’s off to me though that this functionality doesn’t work, I guess most people have files with the same format in one folder. Thank you though
