Refresh Power BI Dashboard source from Excel/Web

[irfan_abdrhman]

Hello All,

I would like to know if it is possible to create a Dashboard by using Web (Suggestions on what third-party application non-payment), like google sheets, are available to use as a source. 

The idea is that in the website, app.powerbi.com, we refresh or schedule to refresh, new data on the source file will automatically reflect on the published dashboard. Right now, if I want the new data on the source to reflect on the Dashboard, I would have to publish again, this means that it requires manual work. Is there any way to close the gap on this issue? If there are anything else that I missed out and would benefit me in knowing, I would appreciate to be added on as well. Thank you! 

[irfan_abdrhman]

Hello @ibarrau and @Anonymous , I understand the concept of having the connection, but when I tested on my end, it appears that the data does not sync with the published dashboard. I wonder if the solution given requires to publish each time there is a new data in the source. I have a workflow that should explain the requirement of my question where the Excel on cloud will communicate with the Published Dashboard Online so that we can utilize the Refresher Rate Function in the Published Dashboard. The issue is that the data does not communicate with the published, where we would need to update the power bi desktop, and publish again each time for new data. This manual method should not be accepted. Please view the intended workflow below.


Thank you.

[Anonymous · Accepted solution]

Hi,@irfan_abdrhman .Thank you for your reply.
I have viewed the workflow diagram you have given. I have some questions about your description and would like you to address my doubts in detail, which will help you to solve the problem.
1. In your description and workflow, what is meant by “DashBoard”. Does it mean the report created in Power BI Desktop or the dashboard created in Power BI Service?
report:
 

dashboard:
 

Because in Power BI Desktop you can only create a report and publish it to Power BI Service, while for Dashboard, you can only create and use it on the service. They are different concepts.
So I think the “Dashboard” you mentioned actually refers to the report created in Power BI Desktop.

2. For your scenario: Is it possible to realize the following effect.
When “data change” occurs in the cloud data source (excel) used for the report, can the report that has been published to the service directly make the corresponding changes without having to click the Refresh button on Power BI Desktop to refresh to the latest state, and then re-publish the report to the “Publish to the Power BI Desktop” service? “Publish to the Power BI Service” (you want to bypass this intermediate step).
For your idea, trying to implement it needs to be discussed based on two scenarios.

1. When the data source is changed, the only thing that changes is the data itself (the amount of data changes, decreases or increases), the architectural definition of the data does not change (there is no appearance of an extra column of data in the table, or a change in the column names of the table, and the relationships between the table models are changed).
At this point only the data itself has changed, not the model architecture.
In this case, you can indeed achieve what you want: bypassing the republishing of the report on the Desktop to the service.
You can do this by configuring the correct gateway connection to the report's semantic model and setting the correct Scheduled Refresh.
The latest data will be connected through a gateway (either a local gateway or a cloud gateway, which one to use depends on whether your data source type is cloud or local).

Solution:Configure the correct gateway connection for the reports on the service and set the correct Scheduled Refresh.

URL:
Intro to dashboards for Power BI designers - Power BI | Microsoft Learn
Configure scheduled refresh - Power BI | Microsoft Learn

2. The second scenario: when the data source is changed, not only the data itself is changed, but also the architecture of the model is changed (the relationship between the tables is changed, some reports have a few extra columns of data, or the column names themselves are changed) this involves a refresh of the architecture.
You must first re-edit the report on Desktop, save it successfully, publish it to service, and then configure a gateway connection for it to properly connect to the data source. Finally configure the Scheduled Refresh.

The reason for this is that schema refresh is not supported on Power BI service, so if the schema changes, you will have to re-edit the report on Desktop and publish the latest version of the report to the service.
Note that when you publish a report to the web (after Power BI Service), you will have to configure a gateway connection for it in order to ensure proper data updates, and configure Scheduled Refresh if possible.

Solution: Update the report to the latest version in Desktop and republish to service.
Configure the correct gateway connection for the report on service and make the correct Scheduled Refresh.

URL:
Data refresh in Power BI - Power BI | Microsoft Learn
 

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hello,ibarrau ,thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.
Hi,@irfan_abdrhman . I am glad to help you.
I couldn't agree more with ibarrauti's suggestion, which is a good one.
In fact, there are two ways I can interpret your question.

1.Use OneDrive refresh.
If you want to realize that you want to reduce the number of Publish to Power BI service when you modify data in Power BI report file, including modifying the table relationship in the model of the pbix file or adding or modifying a specific DAX code (measure/calculate column), then you can minimize this operation by saving the pbix file to OneDrive/SharePoint Online. Then you can minimize this operation by saving the pbix file to OneDrive/SharePoint Online. That is, using OneDrive Refresh provided in Power BI, it will automatically detect the version change of the pbix file stored in the cloud, and update the report file stored in the Power BI service at regular intervals (once an hour by default) (the dashboard will be updated synchronously as well).
Through the cloud OneDrive refresh, you can realize the real-time version of pbix files, without the need to repeat the upload.

URL:
Refresh a semantic model from OneDrive or SharePoint Online - Power BI | Microsoft Learn

2.Configuration Scheduled Refresh
If you are trying to achieve synchronized update of reports(Dashboard) on service after data is updated in data source. Then you can take ibarrauti's suggestion. Use the cloud file (or other cloud data source) to create the report, publish it to the service, and then configure a refresh schedule for it. This will enable automatic data refreshing at regular intervals.
It should be noted that, regardless of the type of data source, the use of the gateway connection is a local data gateway or cloud data gateway, the process of refreshing the report data need to go through the Semantic Model, because the Semantic Model plays a key role in Power BI, responsible for defining the structure of the data, relationships and calculation logic.
Therefore, it is very important to keep the semantic model up-to-date.
You can use a cloud data source to create reports, and then use a cloud gateway or a local gateway for regular data fetching and refreshing the reports. You can also use other tools such as Power BI RestAPI and Power Autmate to automate the execution of the refresh. Increase your productivity by reducing the number of manual setups!
Below are some of the issues that have been resolved and we hope you find them helpful.

URL:
Solved: Re: Is there a way for instant refresh of PBI repo... - Microsoft Fabric Community

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ibarrau]

Hi. Power Bi is a microsoft tool so it talks better with its technology. You should try sharepoint or onedrive for business for the excel files. You can configure up to 8 schedule refreshes per day with free or pro account to keep your data up to date without any kind of manual interaction.

I hope that helps,