A friend in need is Power Platform indeed!

Microsoft Power Automate (yes formerly known as MS Flow) allows updating the datasets in PowerBI in the latest updates. But just like good friends, PowerBI does not hesitate to speak to MS Flow too and can send data to open endless possibilities. In this blog, I have mentioned the steps that will allow you to send data from PowerBI Dataset to MS Flow and then our all-powerful friend, MS Flow can help in thousands of different ways! 

Last week, I was working for a customer in the Public utility sector that is using SAP as the core technology platform. The requirement was to get some key information related to projects from SAP and then use it to update the Apps running on the Power Platform. SAP currently supports SAP BW connector released by Microsoft. The connector works like a charm for PowerBI and we can use data gateway to securely get the on-premises hosted data in PowerBI dataset. The issue was, to date, there is no connector available for SAP BW for PowerApps and Flow, but you can vote for a Flow connector and PowerApps connector for new features release.  

In my case, the options were limited as the customer does not want to invest a lot of time and budget for developing SAP interface to share the data for the cloud and Power Platform world. But the solution explained below, can also be used for situations where you already have built and loaded the data from heterogeneous sources and want to have a consistent approach and reuse the data without doing rework on multiple layers again.  

MS Flow works as Web API too

The first step we need is to get the MS Flow available as the Web API. It is a very simple step and all we need to do is use the trigger “When a HTTP request is received”. Once you saved, the Flow, it will show you the URL for your Web API along with the sig (secure key). Copy the entire string for our next step.

Update PowerBI dataset to make the web call

The Web API generated by MS Flow can be invoked from the PowerBI dataset. The first step will be to connect to our data source normally and query the data that we are interested in. The next step will be to make a web call for the URL, we copied from MS Flow in the previous step. The below power query code snippet, you can use for your solution and the only thing that you will have to care about is to prepare the data from your data source and copy the correct URL for the Web API (MS Flow). 

let
    Source = Sql.Database("Your DB Server", "DB Name"),
    SAP_Project_data = Source{["Your Table"]}[Data],
    Projects = Table.SelectColumns(SAP_Project_data,{"ProjectName", "ProjectStartDate", "ProjectCost", "ProjectActualCost", "ProjectBudgetCost"}),
    ToArray = Table.ToRecords(Projects),
    ToJsonBinary = Json.FromValue(ToArray),
    WebCall = Web.Contents("Copy the Full URL here including the key", [Content=ToJsonBinary]),
    LineFromBinary = Lines.FromBinary(WebCall)

in
    LineFromBinary

Making MS Flow ready to catch the data

The data received in the MS Flow is in JSON format. We first need to parse this JSON data and then convert it into a table to be saved in CSV file. It sounds complicated but it is very simple as mentioned in the steps below with the help of few expressions highlighted in the hint text in the screenshots.

Endless possibilities!

Using SAP BW connector, we can load the data into the PowerBI service. Even if you have data scattered into multiple data sources, it is pretty trivial to get it transformed and loaded into the data sets. Once you identify your required data, not only we can present into the visualizations in PowerBI, we can also send it to MS Flow following the above steps. The data loaded from the PowerBI data set to the MS Flow gives several options depending on how you want to use it. We can send it in the CSV format as an attachment in an email to someone, save the file on Azure Blob storage, or just load it into CDS entities.