If your organisation has a Power BI premium license, there are a number of ways to share and distribute reports and data but what if your organisation doesn’t want to or isn’t in a position to pay for premium. A little bit of DAX (which you can copy from Power BI desktop) and a logic App or a Power Automate flow can fill parts of that gap.
In this blog post we will show you how to get the DAX from Power BI desktop and how we can use this to save to SharePoint using a flow and how to pop in Teams using a Logic App.
First open your PBIX file in Power BI desktop and create a table of the data to distribute. Once you have created your table we can add filtering and sorting which may be desired in the export.
Once you are happy with the table click on 'View" in the ribbon and select 'Performance analyzer'. This is how we will retrieve the DAX.
Start recording and refresh the visuals
You can now expand on the relevant table and copy the query, we will copy the query into Notepad ++ to have a look at it.
In the resulting DAX we can see the filter we applied on the ID in the top section in the TREATAS section, this is very useful for when you want to pass variable filtering.
So we now have a query without having to write a single line, now lets automate this.
Login to Power Automate and let's create an instant cloud flow and add an action called 'Run a query against a dataset'
Select the workspace and Dataset published to the Power BI service and simply paste the DAX retrieved earlier into the Query text field.
To save this data into SharePoint we will need to translate the response into a CSV table which we can then save to SharePoint. To create this CSV table simply add the Create CSV table action and use the output 'First table rows' from the 'Run query against a dataset' action.
Use this output to create a file in your SharePoint library.
If on the other hand you would prefer to use Logic Apps the same steps are followed, and the actions expect the same input.
To round the blog post out we will show how to post the data into a teams chat.
Run the query against the Power BI dataset the same way as show in the Flow however, this time we will add a 'Parse JSON' action to the output and paste our response in as a sample payload.
To post the data to a teams post we need to make the output a little prettier, if we post this without any processing the output will look similar to this:
This is not humanly readable and quite ugly. Let's use the 'Create HTML table' action to format the data better.
We have added an if statement to differentiate the totals column better:
if(equals(length(string(item()?['CSVTest[ID]'])),0),'Total',' if(equals(length(string(item()?['CSVTest[ID]'])),0),'Total','')
Now the data should be formatted in a nice tidy way it is time to pop it into MS Teams.
Lets run our flow and check for the teams pop
There we have it, a nicely formatted table with the data freshly exported.
If you have any questions or wonder if this might be useful to you business, please reach out using out contact us page.