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?
