If you are using a push dataset in Power BI for a real-time dashboard, every time new data is generated, your dashboard will be updated. However, what if you want your dashboard to also display historical data? That is, the data that already existed before you created a flow to add new data to your push dataset. Currently it will only contain data created after the flow was activated.
In this article I will show you an example of how you can bring your past data into your Power BI dataset, so that you can see both historical and new data in your real-time dashboard.
Overview of what you are doing:
- Put the historical/past data in a place where you can access it.
- Build a Power Automate flow which will run through each row and add it to the push dataset in Power BI.
- In turn, this will update the real-time dashboard with the past data.
The example shown below is using Microsoft Form submissions as the source for the push dataset/real-time dashboard. Similar to the form created in this Tutorial: Create your first Power BI real-time dashboard.
1. Put the historical/past data in OneDrive for Business
If it is Microsoft Form submissions, you can export the results to Excel and keep only the rows of the form submissions you need to add into the dataset. Save it in a cloud location such as OneDrive for Business where it can be accessed via Power Automate. Make sure you already have the push dataset created Power BI (see here for how). We will use the Excel Online connector in Power Automate to retrieve the data. Make sure the data in Excel is in a table, as shown below.
2. Build Power Automate flow to add the data into your Power BI dataset
Go to Power Automate and create a new instant cloud flow. Select the trigger Manually trigger a flow.
Add an action to get your data from step 1. In this case, the List rows present in a table action from Excel Online connector has been used. In the action, fill in the details so that it points to the table you have in your Excel Online spreadsheet. Click the folder icon to find the file.
For the next action, add an Apply to Each control. For the output, add the value dynamic content from the previous Excel Online action. Using this will apply the same actions to each row listed in your table. Within the Apply to each control, you can add any other actions as needed before the row is sent to your Power BI dataset. For example, and AI Builder or time zone conversion step.
The last action in the Apply to Each loop should be the Power BI action Add rows to a dataset. Fill in the details so that it points to your push dataset in Power BI. For the values, add in dynamic content of the corresponding Excel Online column.
You can now save and manually test the flow once.
Make sure it is only run/tested successfully ONCE. If you keep on testing or triggering this flow, it will keep on adding the same data into your Power BI dataset.
3. See your updated Power BI real-time dashboard
When you now check on your Power BI real-time dashboard, it should be updated with the past data you just added in. Now, your dataset will contain all new incoming Microsoft Form submissions (based on another active flow you have running) and the older submissions.
Rishona Elijah 