cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MAHEswe
Frequent Visitor

Power BI DataSet to Sharepoint on-line List

This post is partly a description on how to do this, but it also includes some open questions to welcome a discussion on how to do it better and more effective.

 

The objective is to move data from Power BI DataSets to Sharepoint on-line Lists, to support use-cases like

 - Store snapshot status of aggregated data, as historical records from where trends then can be presented.

 - Create deviation notifications to act on, e.g. identified with PAFnow Process Mining analysis.

 - Request comments on the data, which is a very common business scenario.

 

For a single datapoint that is easy:

PBI DataSet => PBI Report => PBI Dashboard with Card => PBI Alert => Power Automate [Triggered by PBI Alert ; Create Sharepoint item].

The value from the Card is included with the Alert, and other columns in the Sharepoint list item can be populated too.

 

For multiple datapoints (Rows and Columns) there are several options, but with various levels of complexity.

Question: I wonder if there are less complicated ways than those mentioned below?

 

The most easy solution would be if the Power BI connector in Power Automate included an option like "Get Data from Power BI DataSet", and in that allowed to define a query on data to extract. It could be similar as in Power BI Report Builder, where a query created by Power BI Desktop's Performance analyzer for one table is a quick way to define complicated queries with just drag-and-drop, and then copy and paste the generated query string.

But that is unfortunately not available.

 

A possible solution with many variants are instead:

PBI DataSet => Paginated report => Power Automate [By PBI Alert or Recurrent ; Export as Excel or CSV] => Power Automate [When the file is updated ; Read file ; Transform ; Create items in Sharepoint]

 

Challenges:

Exporting the Paginated Report as Excel don't format the data as an Excel Table, and therefore Power Automate's "Steps" (or "Add an action") based on Excel Tables can't be used. The alternative "Steps" like 'Get File Content' don't enable the data on a format appropriate to enter direct as Sharepoint list items.

Question: Can a Paginated Report be defined so that the exported Excel-files include named Excel tables with the data?

 

Exporting the Paginated Report as CSV enables to use techniques to parse the CSV file in to Rows and Columns. 

"Parse CSV" by Encodian is available, but it is often preferred to avoid use of third party components.

Other paths forward are provided by e.g.

https://medium.com/@anoopt/different-ways-of-getting-excel-data-using-power-automate-e21292b1d02a

https://www.youtube.com/watch?v=lZDTTVnK8vw

These are the best options I have found.

 

However, parsing the CSV without use of 3rd party product includes many steps that both takes time to implement and makes the solution somewhat complex. It become even more complex if the data includes "," as separators, like CSV-files from PBI do, or when adding validation for non-empty values etc.

A shorter solution would be preferred to be able to easily implement these generic use-cases in multiple contexts.

 

It would therefore be interesting to learn from the community if there are shorter solutions for what at first seems like a simple ambition: To get data from Power BI DataSets and store in Sharepoint on-line Lists?

2 REPLIES 2
Syndicate_Admin
Administrator
Administrator

Have you considered using Office Scripts, specifically Office Scripts with Power Automate? This is the 'Run Script' action on the Excel connector for Cloud Flows. With Office Scripts you can use the 'Run Script' action to execute javascript against a workbook. So if the exported Excel has a consistent structure every time, there should be a script that can be used to do what you want,

Thank you GeoffRen for your reply.
I will look in to your suggestion that I did not know about before. Thank you.


Exporting the Paginated Report as CSV, Save it to Sharepoint Online, Read the CSV, and then parse the CSV data in to (looped) Rows and named Columns worked well.

While it had some steps to implement, it has worked flawless once per day since Feb 16th, with about 200 rows and 20 columns added to the Sharepoint list each day. Then I read that data back in to PBI to plot trends. I will likely use that solution next time too, now when I have tried it once with good result.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors
Top Kudoed Authors