How to save combine table in PBI without loading again when re-opening PBI file

[nminh_phuong]

I combine 3 large csv files (Around 5 mil rows each) in PowerBi by this way:
1. Put them all in one folder

2. Oper PBI desktop, Get Data -> Folder -> browse the folder containing 3 files -> Select transform -> Combine 3 files into one 1 final table (for example call this table "Combine table") -> Apply and close (this step takes long time since the 3 files are large). 

Howerver, after I save and close the file, when I open it again, PBI has to LOAD 3 CSV FILES AGAIN and it takes long time again. More than that, the "Combine table" doesn't keep the same format and value as before, I cannot use the "Combine table" again. 

Can anyone help me to solve this problem? 

Like, I can import the "Combine table" into PBI and later, when I re-open, it doesnt need to load again (like PBI works when import 1 file) 

Many thanks

[Anonymous · Accepted solution]

Hi @nminh_phuong ,

If you don't need the three tables in the model, please uncheck the "Enable load" of these tables.

Performance Tip for Power BI; Enable Load Sucks Memory Up

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[nminh_phuong]

Hi @Anonymous,

Thank you very much for the advice. I did try Unselect Enable Load but when I need the data I also have to Reload. 
But I think 1 step I missed is Generate a REFERENCE for the "combined" table. 
Let me try again. Thank you again. 

[rubayatyasmin]

Hi, @nminh_phuong 

It sounds like your issue arises from Power BI having to process large CSV files every time you open your file. A potential solution to this problem could be to utilize Power BI's incremental refresh feature.

Incremental refresh allows Power BI to only load new or changed data from the source. This can significantly reduce the amount of time it takes to load data, especially for large files, as in your case.

However, there's a catch. Incremental refresh is not available for flat files like CSV out of the box. It's primarily used with SQL databases. But there's a workaround you could try:

1. Load the CSV files into a database: If you have access to a SQL Server database, you could import your CSV files into a SQL Server table. This could be done using SQL Server's import wizard, SSIS, or any other ETL tool that you have access to.

2. Set up an incremental refresh policy: Once you have the data in SQL Server, you can set up an incremental refresh policy in Power BI. When setting up the policy, you would need to specify a "filter" column. This could be a date column, for example, which would allow Power BI to load only new or changed data based on the date.

3. Connect to the SQL Server data source in Power BI: Instead of connecting to the CSV files directly, you would now connect to the SQL Server data source. Power BI would then use the incremental refresh policy to load the data, which should be much faster.
   
   Alternatively, you can make fair use of Power BI dataflow. Pull the data in a dataflow, transform it there, and then from your Power BI desktop connect to Dataflow to load your data. 
   
   If Power BI DF and SQL server is not an option then you use DirectQuery instead of import. This could reduce the loading time. 

Keep in mind that this is a bit of a workaround, and it would require having access to a SQL Server or similar database, and the ability to load the data into it.

In terms of the "Combine table" not keeping the same format and value, you might want to look at the steps in your query where you're transforming the data. You might need to adjust these steps to ensure that the transformations are consistent across different data loads. If the data in the CSV files is changing, you might also need to adjust the transformations to account for these changes.

Also, remember that Power BI stores data in a compressed, in-memory format. This means that when you load data into Power BI, it gets stored in the file itself. So, while the initial load of the data may take some time, once the data is loaded, you should be able to work with it quickly, even if the original data source was a large file.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.    

[nminh_phuong]

Hi @rubayatyasmin, Thank you very much for your detail advice.
Unfortunately, I dont have access to SQL server but I see the key feature that may solve this problem is using an incremental refresh. 
I will search more and try this approach. Thank you very much.