Forum Discussion
dynamically import files from a folder WITHOUT combining them
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.
Disclaimer: I'm new to both Power BI and R (only a couple months into learning both).
I wrote an R script that dynamically generates dataframes for each file of a specified file type from a specified folder and then saves those dataframes to an R environment '.RData' file. Then, in Power BI Desktop, I use R Script as a datasource and use 'load("filename.RData") to load the dataframes from that environment without running the entire underlying script that generated those dataframes -- which gives me control over 'refresh' (manually accomplished in R) and saves processing time in Power BI. When an R script is used as a data source, it pulls compatible R objects (dataframes, matrices, lists) into Power BI as separate queries/data sources. This is what I wanted to achieve. However, there seems to be a limit to the number of R objects Power BI can load into a Desktop session. I can't load all ~120 dataframes, so now I'm using trial and error to find my limit for successful dataframe loading. Regardless, there does seem to be a limit, but I'm not sure if the limit is due to an internal Power BI constraint (number of objects and/or the size of the objects), or due to my own system constraints (RAM, etc).
Cool - that's a very interesting discovery!
I'm new to R as well and haven't spent much time with it yet. Also haven't seen a lot of R-scripting in PowerBI yet, so don't know which expert to call in here. But as you seem to discover already: This doesn't seem to be an R-issue, but more a PowerBI limitation.
You must have a very specific use case here that seems worth to make a big efford to skip the first step of the query design - for queries that then will be totally different. Normally I would have said that it is a good thing that we cannot import multiple files the way you want it. As if you apply the same steps in each query, you should instead create one query with the source-names as attributes in an additional column.
So sorry, no further ideas or help from my side here.