importing a huge csv file with historical time series data; what are alternate ways to do this?

[Anonymous]

Hi all,

I have a lot of data similar to stock market data. Using an API from Reuters we get all price history data on a daily basis in a csv file. A direct link between Reuters and Power BI is not possible.

Every day I import a csv export with the historical data so every day we have a full history including yesterday's price.

To deal with this huge file, I export a csv file with hisotrical data up to January 2021 and disables the refresh. Then, I append a seperate csv which only has data from January 2021 onwards (this one does refresh). However, this appending process still takes a lot of time. How can I solve this?

I don't have power BI premium so incremental refresh isn't an option. I was thinking maybe I could store the data up to January 2021 in a database and use directquery and then append the csv file with data from January 2021 onwards? But I have no idea how to do this. What is the best practice in such situation? 

To give some context: The file is so big because I have a lot of "price types". See below an example of three stocks from january 1st 2020 up to and including january 3rd 2020. In reality I have hundreds of stocks and data from 2010 resulting in a huge file. 

Date	Company	Price type	Value
01/01/2020	Microsoft	open	188,06
01/01/2020	Microsoft	high	188,16
01/01/2020	Microsoft	low	186,485
01/01/2020	Microsoft	close	187,33
02/01/2020	Microsoft	open	185,61
02/01/2020	Microsoft	high	187,7
02/01/2020	Microsoft	low	185,5
02/01/2020	Microsoft	close	187,23
03/01/2020	Microsoft	open	183,25
03/01/2020	Microsoft	high	185,41
03/01/2020	Microsoft	low	182,65
03/01/2020	Microsoft	close	185,35
01/01/2020	Apple	open	75,09
01/01/2020	Apple	high	76,15
01/01/2020	Apple	low	76,08
01/01/2020	Apple	close	75,14
02/01/2020	Apple	open	75,18
02/01/2020	Apple	high	75,5
02/01/2020	Apple	low	75,17
02/01/2020	Apple	close	75,45
03/01/2020	Apple	open	75,31
03/01/2020	Apple	high	75,41
03/01/2020	Apple	low	75,21
03/01/2020	Apple	close	75,23
01/01/2020	Netflix	open	329,51
01/01/2020	Netflix	high	330,84
01/01/2020	Netflix	low	329,42
01/01/2020	Netflix	close	330,75
02/01/2020	Netflix	open	331,21
02/01/2020	Netflix	high	332,52
02/01/2020	Netflix	low	330,56
02/01/2020	Netflix	close	330,86
03/01/2020	Netflix	open	331,11
03/01/2020	Netflix	high	332,65
03/01/2020	Netflix	low	332,18
03/01/2020	Netflix	close	332,61

[negi007]

@Anonymous You can call API directly in powerbi instead of importing you can connect to API using weblink and have direct connectivity to the source system.

In case you wish to import data using CSV file, you can do so. You can keep all histroical information in one file and latest information in another file. You can disable load option for the file which has histroical information and enable it for the file which has latest information. 

Below are some links for reference on api calls

https://medium.com/@ux.emily.kim/importing-data-to-power-bi-using-rest-apis-and-automatically-updating-the-dashboard-c3d018897b1c#:~:text=In%20the%20desktop%20Power%20BI,to%20pull%20the%20data%20from.&text=You%20can%20now%20import%20the%20data%20to%20Power%20BI!

https://community.powerbi.com/t5/Service/Import-data-using-API-into-Power-BI/td-p/519111

[Anonymous]

Hi @negi007 ,

The problem with the first solution is that we only have a subscription for one specific computer.

For the second solution, keeping all historical data in one file and disable refresh, and enable refresh for the file that does updates; this doesn't work. Because when you append, Power BI starts importing both files automatically, despite that you turned off refresh for one of them. 

[amitchandak]

@Anonymous , For incremental please check this "

Note

Incremental refresh is now available for Power BI Pro, Premium, and shared subscriptions and datasets.

"

https://docs.microsoft.com/en-us/power-bi/admin/service-premium-incremental-refresh

[Anonymous]

Hi @amitchandak 

Indeed thanks; I am reading more about incremental refresh but as far as I can see it works when you have one huge csv file. But I still have 2, one with the historical data and one with data only for this year. So I still need to append both files and I believe incremental refresh is only when you have one file.