Forum Discussion
dynamically import files from a folder WITHOUT combining them
Hi all,
I'm looking to upload multiple files of the same type from a single folder into Power BI Desktop. However, in this case, I don't want to combine them into a single query. How can I dynamically import each file as a separate query/data source?
Please help!
-cad
21 Replies
I wonder if you could create a query/function with a parameterized file source. Then use a Folder query and call your other query/function. ImkeF might be able to help here, this sounds right up her alley.
Thx Greg_Deckler!
cdok1091: This is not possible. The only source where I've seen this kind of behaviour is SQL-server DB.
Thanks for looking into my request -- I appreciate your insight.
I was able to find a work-around by using an R script to dynamically create dataframes for each file source in the folder, then saving that R environment (.RData) and calling the environment into Power BI using R Script as a data source: load(filename.RData). But when I attempted to select all the dataframes, I ran into a problem with loading multiple dataframes -- apparently not enough memory. The file size does not seem excessive (if exported to text files, the data frames' total file size is ~86MB). See this post for more details on my new challenge.
-cdok1091
Must admit that I'm a bit lost with that new approach. I still doubt that you will be able to create multiple queries at once with it. But in order to nail your current problem down to RAM-issues, I'd suggest that you try your new method on a folder with just 2 files in it to test the general setup.
Hello,
Have you had any luck with that? I am trying to do exactly the same thing and had no luck so far.
BR,
Mohamed
Has anyone found a solution to this problem? ImkeF is there any way to build a custom function in M that takes a list (or column) of tables and "adds new query" for each of them? I have 300+ csv files in a downloaded folder I need to import into Power BI on a regular basis. It would be a nightmare to have to click "add new query" for every one of them and then, worse, track if any additional csv files are added to the folder over time.
Using the folder import option from "get data" I get far enough that I have a list of tables (see pic below) with each table representing one of the csv files in the folder. But I don't know of a method that can split that single query into multiple queries without manually clicking the "add new query" button.
Is this possible? Any help would be appreciated.
Best,
Yisroel
Hi caninetiger ,
that' not possible through any clever M-function.
The only mechanism that allows such a thing is the navigation dialogue in the Power Query UI and that is controlled by Microsoft.
Connectors like Excel or SQL allow such a procedure, but for CSV this is not possible.
If your csv-files are have less than 1 Mio rows, you could consider creating an xlsx-files with one sheet for each csv-file. But not sure if that would save you much time at the end.ImkeF Hey, you make a great point here. There's no reason why one couldn't write a custom folder connector to do this. I just wrote one for The Definitive Guide to Power Query (M) so I wonder if I could write a connector that presented all files as "leaves" and just allowed you to select them all. Thanks for the idea Imke!
ImkeF and Greg_Deckler, right now, I think it makes most sense to write a simple python script that takes all the csv files in a folder and writes them either to multiple sheets on an excel file or to a simple database (like SQLite) and then have Power BI/Power Query point to that. This solves the inefficiency of the, clicking "add new query" option, for all the files and allows easier tracking when new tables are added.
If anyone has a better, Microsoft product related idea, I'd love to hear it.
