Unanswered

Data extraction from Power BI

(0) Share

Report

Posted on by algu84

Hi there, I have already built a flow that, basically, opens an Excel which connects to a Power BI dataset, updates the data and copies it to another Excel file (values only). The flow is always running and I made it repeat its actions every hour.

The problem is that it sometimes does not update old data while bringing the new ones into the destination file!! If I stop the execution and restart it, the old data get up-to-date.

It seems some sort of cache issue. What can we do?

Thanks

Categories:

Building flows

I have the same question (0)

All responses (6)

Answers (0)

algu84 6 on at

Like (0)

Report

I forgot to mention that I'm using Power Automate Desktop 2.30.109.23075

Was this reply helpful? Yes No
AndreasN 318 Super User 2024 Season 1 on at

Like (0)

Report

How have you done the connection to the Power BI dataset?

A new implementation came in January, allowing you to directly query the dataset to an excel sheet, which can be updated without opening the excel document.

Here's a blogpost about it:
Announcing Connected Excel Tables from Power BI (Public Preview) | Microsoft Power BI Blog | Microsoft Power BI

I suggest using that technique, then use this script
function main(workbook: ExcelScript.Workbook) {
// Refresh all data connections
workbook.refreshAllDataConnections();
}

to update the excel sheet, prior to running your flow. You can also implement the script into the flow, but scripts sometimes load slower than Power Automate which could give some issues. I currently run the scrip every morning at 8 AM, through a timed flow, and then I run my data collection flow from the excel sheet at 9 AM, to ensure the data is updated and refreshed.

Was this reply helpful? Yes No
algu84 6 on at

Like (0)

Report

Hi Andreas and thanks the quick response.
The aim of my flow is to make the data available for other systems outside Power BI. Is the solution you suggest suitable for that?I'll give it a try anyway.

Was this reply helpful? Yes No
AndreasN 318 Super User 2024 Season 1 on at

Like (0)

Report

Sure, you can either run the refresh flow, then copy the excel document and send them a copy, or just send them a directly connected excel sheet to the system outside Power BI.

The blog goes into details about sharing the document, it will then be in view only mode, but they are still able to refresh the data themselves, or you just run the refresh flow for them every once in a while.

Was this reply helpful? Yes No
algu84 6 on at

Like (0)

Report

Another question: which is the difference with an Excel created through the dataset "Analyze in Excel" function?

Was this reply helpful? Yes No
AndreasN 318 Super User 2024 Season 1 on at

Like (0)

Report

Analyze in excel gives you a pivot table, and as far as I know, there is no direct data connection. This means, if data in Power BI changes, you need to export a new excel sheet.

With the connection export, if changes in Power BI is made, the same excel sheet will collect those changes, all you have to do is refresh the document. Also, you get the data in a table format, which looks identical to your Power BI data set, so you don't have to do any reformatting of the pivot table.

Was this reply helpful? Yes No