Power BI automation

[Mark-JZ-Yeap]

Assume a user uploads a file in a onedrive.

A refresh on the report should automatically trigger on workspace.

Power BI should automatically import that file as raw account and compare it with clean account file

and the results should be visible in a table visual

and efficiency in card visuals

and after the refresh is done, take the export from the table visual and upload it on onedrive.

Everything should be automatic.

Please assist how to solve this problem.

[Mark-JZ-Yeap]

First, I upload the empty raw account file in OneDrive.

Then, I import the OneDrive folder in Power BI Desktop and selected "Combine & Transform Data".

In Power Query, I filter out the empty raw account file and enable fuzzy matching and set threshold = 0.8 when merging queries as new. Join: Left Outer (all from first, matches the second).

I create the table visual and create the efficiency measure for the card visual.

I have save and publish the report.

Power Automate:

Recurrence -> Refresh a dataset in power BI -> Delay: 15 seconds -> Run a query against a dataset ->Parse JSON -> Create CSV table -> Create file in OneDrive

Once the user uploads the raw account file, power automate will activate automatically.

When the power automate finishes successfully, Power BI report in workspace automatically refreshes and shows the results in table visual and the efficiency value in card visual.

After that, it also create a file in OneDrive and display the result from the table visual.

[lbendlin]

There is no guarantee that the refresh completes successfully in under 15 seconds. Make that delay much longer, or change the flow to include refresh status polling.

[v-mdharahman]

Hi @Mark-JZ-Yeap,

Thanks for reaching out to the Microsoft fabric community forum.

It looks like you are facing issue while trying to set up automatic refresh for your data which is in onedrive. As @lbendlin already responded to your query, please go through his response and mark it as solution if it answers your query.

I would also take a moment to thank @lbendlin, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.

[v-mdharahman]

Hi @Mark-JZ-Yeap,

As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help.

If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.

If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.
Thank you for your patience and look forward to hearing from you.

[Mark-JZ-Yeap · Accepted solution]

I have solved the problem.

First, I upload the empty raw account file in OneDrive.

Then, I import the OneDrive folder in Power BI Desktop and selected "Combine & Transform Data".

In Power Query, I filter out the empty raw account file and enable fuzzy matching and set threshold = 0.8 when merging queries as new. Join: Left Outer (all from first, matches the second).

I create the table visual and create the efficiency measure for the card visual.

I have save and publish the report.

Power Automate:

Recurrence -> Refresh a dataset in power BI -> Delay: 15 seconds -> Run a query against a dataset ->Parse JSON -> Create CSV table -> Create file in OneDrive

Once the user uploads the raw account file, power automate will activate automatically.

When the power automate finishes successfully, Power BI report in workspace automatically refreshes and shows the results in table visual and the efficiency value in card visual.

After that, it also create a file in OneDrive and display the result from the table visual.

[v-mdharahman]

HI @Mark-JZ-Yeap,

Glad to know your issue has been resolved. Can you please mark the helpful reply as solution so that other community members can solves their related issues easily.

Best Regards,

Hammad.

[lbendlin]

What's the problem you are encountering?  If a data source for a Power BI semantic model is located on SharePoint/OneDrive and that data source changes then allegedly the semantic model refresh is initiated "within the hour".  If you don't want to wait for that you can use a Power Automate trigger "When file is modified" to initiate the refresh request.  Make sure to debounce it though to avoid 429 errors.

[Mark-JZ-Yeap]

First, a user uploads an excel file in onedrive. Then, a refresh on the Power BI report should automatically trigger on workspace. After that, I need to make Power BI automatically import that file in onedrive. How can I do it?

[lbendlin]

Use the Power Automate trigger "When a file is added" rather than "When a file is modified".