Automatic update at least every hour with editing in Power Bi Desktop before

[lukas1]

Hello, 

I have an excel file in Onedrive. I need to modify it in Power BI desktop (create new measures, etc.). 

In the end I want to make a dashboard. So I publish the Power Bi desktop file to power BI service or load it in Power Bi service by using get data (-> Onedrive Business -> power bi desktop file). 

Is there any way, that if in the original excel file (saved in Onedrive) new rows were added, a dashboard, in power bi service gets updated within the next hour. Without having to manually refresh it or scheduled refreshes (only 8 per day & night) or have to refresh it in Power Bi dektop?

The Context is: 

Right now, I have connected my Power Bi desktop file through sharepoint/Onedrive folder with the excel file. Then I have used get data (from Onedrive Business) and uploaded my Power BI desktop file in Power Bi Service.

The weird thing is if I update the Power Bi desktop file then in Power Bi Service everything works perfect, and an automatic refresh is triggered within the next hour. But when I change something in my excel file and don't manually update it in power bi desktop, nothing happens in Power BI Service. Even though the excel file is also in the Onedrive.

If I manually update my dataset in power Bi service, the new data from the excel file is also shown, so the connection is working. 

I have enabled the option in Power bi service that says onecloud refresh.

So the only thing, which is not working is the automatic refresh when something is changed in the excel file (also in onecloud, but not directly imported in Power Bi Service, but connected through the Power BI desktop file).

Has anybody any ideas what the problem might be and/or how to solve it?

Or has anybody in general an idea how to automatically update data at least once an hour when the data was modified in Power BI desktop before?

Thank you very much!

[lukas1]

Maybe a short form, because the text got pretty long:

Is it possible to automatically refresh a Power BI online dataset/report every hour, if it is based on a excel file/database (where the data is changed every hour) and afterwards modified in power BI desktop?

Order of Events/Structure:

Source: Example: Excel file (in Onedrive) --> Power Bi Desktop file where it gets modified (file also in Onedrive) --> Power BI Service (with report/dashboard)

Thank you very much in advance!

Lukas

(Power BI Pro License)

[v-yuezhe-msft]

@lukas1,

Do you want to change data in Excel data source and then sync the data changes in Power BI Service every hour? If so, you would directly connect to the Excel file in Power BI Service using Get data-> Files->OneDrive for busniess option, then create report in Power BI Service and configure OneDrive for Refresh.

 

However, if you want to make changes(e.g. add measure, add visual) in Power BI Desktop and then sync these changes in Power BI Service report every hour, then you can connect to the Excel file in Power BI Desktop, create report in Power BI Desktop, upload PBIX file using Get data-> Files->OneDrive for busniess option in Power BI Service and configure OneDrive refresh for the dataset. In this scenario, if you change data in Excel source file, you would need to set schedule refresh for the dataset in order to make data changes reflect in Power BI Service.

In addition, if your data source is SQL database, you can connect to SQL database in Power BI Desktop using DirectQuery mode, create report in Desktop. After publishing the PBIX file to Power BI Service, you are able to automatically refresh the report every hour.

Regards,
Lydia

[lukas1]

@v-yuezhe-msft Thank you very much for your answer!

Yes, I would need to take the second path you described, where I use Power BI desktop (to add measures, etc.) before the Power BI Service. But if I get this right, in this case it is only possible to update through scheduled refresh (which is only possible 8 times a day)?

But it would be possible with an SQL Database. Thank you very much for this Idea. Right now I only have a few excel files, but I might build a SQL Database, because I need the hourly update (for 24 h a day). Do you think to build up a SQL database would be the easiest way to achieve the automatic updates?

Regards and thank you,

Lukas

[v-yuezhe-msft · Accepted solution]

@lukas1,

"But if I get this right, in this case it is only possible to update through scheduled refresh (which is only possible 8 times a day)?"

In this case, when you change data in Excel source file, you are only able to set schedule refresh for your dataset to bring changes to Power BI Report. Unless you have Power BI premium license, you will be limited to refresh 8 times a day.

"Do you think to build up a SQL database would be the easiest way to achieve the automatic updates?"

Yes. you can use DirectQuery mode to connect to SQL database and achieve hourly refresh in Power BI Service.


Regards,
Lydia