Power BI – Quick Tips working with multiple large CSV Files
I have recently been working a lot with multiple larger type CSV files, and thought it would be good to share what I have found that makes the process run faster. I am always looking at ways to make sure that not only is the model efficient, but also the development experience.
Name your query first before applying steps in Query Editor
I have found that when working with csv files, if you rename your query in the Query Editor, it will then go and complete all the Applied Steps in your query. This in my case meant waiting a few minutes for a simple rename to take effect.
So always remember to give your query a name first, before doing any additional steps.
If possible combine all csv files from separate files into ONE file
I found that when using the Folder to import my CSV files, that when I put all the data into One file it loaded the data significantly faster.
Disabling Background Data in Options
When working with multiple tables that all load off the same CSV files, I found that Power BI desktop would start trying to refresh data in the background, and it would either make the Query Editor I was working on very slow. Or I would have to wait until the background data preview to complete. By turning this off it meant you only would refresh the data you are working on.
To disable this click on File, then Options and settings
Then click on Options
Then go down to the section called CURRENT FILE and click on Data Load
Now in the right hand pane under the section Background Data, remove the tick from Allow data preview to download data in the background. So that once complete it looks as shown below.
Then click Ok.
Now to ensure that it does take effect I would suggest closing and then opening Power BI Desktop.