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
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors