cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cdok1091
Frequent Visitor

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

17 REPLIES 17
Mohamed_N
New Member

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

ImkeF
Super User
Super User

Thx @Greg_Deckler!

@cdok1091: This is not possible. The only source where I've seen this kind of behaviour is SQL-server DB.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

cdok1091
Frequent Visitor

@smoupre and @ImkeF,

 

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

cdok1091
Frequent Visitor

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).

ajuanto
Frequent Visitor

Hi,

 

Can you please have a look at myissue

http://community.powerbi.com/t5/forums/editpage/board-id/community-feedback/message-id/1162/load-aut...

I think I have the similar issue. Can you guide me through it?

 

Thanks.

Hi @ajuanto,

this is the error-message I'm getting when trying to open the link you've posted:

AccessDenied.jpg

So please post link that can be accessed publicly, thanks.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

ajuanto
Frequent Visitor

Hi @Imke,

Here is the message > \

 

 

Hi I am new to Power BI and I am facing an issue.

 

I have a Folder of csv files which I have to import. The csv files has readings of a electronic device,12-12-_2017_11-01-39.png

 

I imported the csv folder and I got such a screen

08-12-_2017_12-17-47.png

 

Then I tried to remove the unwanted columns 12-12-_2017_11-06-07.png

 

now I have a column that shows the sourse file that is in my case the csv file for eg-MFT_RB1_2017-08-08_11-26-56.csv

I am getting all the data i.e the combined data of over 100 csv file in one sigle table as shown above. I want to create diffrent tables for each csv files. I can do it manually by importing csv files seperately like this -

12-12-_2017_11-14-09.png

 But when I am importing a folder I am getting a sigle combined table, I want to split the main table into tables=number of csv files and visualiye the output as above

(I got the above visualization by importing 6 csv one by one, which works perfect)

But when importing a complete folder I am getting one big table with all 100 csv combined and I want to create diffrent tables for diffrent csv like I did with the above visualization.

12-12-_2017_11-34-46.png

Also, I have over 100 csv files in the folder and if I add 50 more in the folder it should automatically create tables of those newly added csv files.

 

Can anyone help me with this?

 

 

Import all your files into one table and drag the filename-field into the legend of your chart and you get what you've shown in the pic.

If you don't want to see all your files in one pic, filter them on the visual-level.

But DON'T  import identical tables into multiple queries!

You can also check this file with a mockup from some sample data: https://1drv.ms/u/s!Av_aAl3fXRbehbEZQCClJ9yv9MhfZQ

 

Imke Feldmann

www.TheBIccountant.com -- How to integrate M-code into your solution  -- Check out more PBI- learning resources here

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

ajuanto
Frequent Visitor

Hi ImkeF,

 

Thank you for the support so far. I came across an issue, could zou have a look?

 

 

I had a trouble visualizing my output in Power BI. I have imported more than 1500 CSV files but every time I visualize it, it only shows 10 CSV files that are randomly selected among the 1500 CSV files.

enter image description here

enter image description here

enter image description here

enter image description here

  1. How can I see all 1500 CSV visualization at once rather than just 10 CSSV?

  2. If it's not possible then I wanted to know how is 10 CSV files selected out of 1500 CSV files? Is there any calculation involved or is it just a random selection that Power BI dose on its own?

  3. In image4 I would like to know how the calculation is di=one for Average, Sum, Medium, and Maximum.

I have attached screenshots for reference. I tried using various filters but none has given me the desired output. In Image4 you can see that I can select the Sum Average Minimum Maximum and other filters....but neither worked.

I havent used visuals with that many series so far and cannot tell the limitations unfortunately.

I recommend to search specifically in this forum or create a new thread.

cheers, Imke

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Hello Imke,

I am facing similar issue, but with a twist, I need to import "Non-Identicale" CSV files into multiple queries. Any idea, how to do that?

BR,

Mohamed.

ajuanto
Frequent Visitor

Hi Imke,

 

It works.! Thank you for the support.

ajuanto
Frequent Visitor

Thank you for the reply. Ill try these out and let you know if it worked.

 

Thank you @ImkeF

ajuanto
Frequent Visitor

Is this possible or is there any other way that I could visualize the values as I need it?

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. 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors