How to automate data

[Anonymous]

Hi All,

I have a question regarding automating data in powerbi. I have developed a dashboard for my company to measure various KPI's both internally and for our clients. However, I am currently updating the data myself, by first downloading the relevant CSV files from our SQL database, uploading them to dropbox, and then replacing the source manually. I was wondering if there is an easier way to do this? We need data that tracks from two years back till today. 

Our data provider has offered to email us the data every hour, and I was told you somehow could power automate this into PowerBi - not sure though?
 

My manager wishes the data to be updated every hour, but since the files are on the north side of 100MB, he said it may conflict with the capacity of the data provider - but proposed the idea that we have a base dataset, which we hourly add onto, thereby only having to upload few MBs. Is this solution optimal and/or possible? I was wondering, how we could add data to an existing dataset - I am only familiar with appending data in the query.

To narrow down, I need help with automating the data. 

Thank you in advance for your advice! 

Best,

Ckolby

[amitchandak · Accepted solution]

@Anonymous , Directly connect with SQL server, define incremental refresh -> Schedule refresh on power bi service using on premise gateway or cloud connection details

for csv

replace at same place

Use on premise gateway and schedule refresh in power bi service 

https://docs.microsoft.com/en-us/power-bi/connect-data/refresh-data

https://docs.microsoft.com/en-us/power-bi/report-server/configure-scheduled-refresh

Gateway
https://radacad.com/the-power-bi-gateway-all-you-need-to-know
https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-onprem

Incremental Load
https://radacad.com/all-you-need-to-know-about-the-incremental-refresh-in-power-bi-load-changes-only
https://thinkaboutit.be/2020/02/how-do-i-implement-an-incremental-refresh-in-power-bi-free-or-pro/