This is the third post in a series of articles in which I explain how to integrate Power BI, Power Apps, Flow, Azure Machine Learning and Dynamics 365 to rapidly build a functioning system which allows users to analyze, insert, automate and action data.

In the previous article I covered building the Power App. In this article I will cover the Power BI report.

We will build out this system in the following order; Power App, Azure Machine Learning, Power BI and then last MS Flow to connect the components. Before you can begin this tutorial there are some required prerequisites.

Prerequisites

• Power Apps
  
• MS Flow
  
• Power BI Pro or Premium
  
• Access to Azure Active Directory to register Power BI App
  
• Dynamics 365
  

Create the API Enabled Dataset

Log onto Power BI and create a new app workspace called Customer Segmentation. This step is not required however if you are like me you create a lot of different content so it’s a good habitat to get into so that you can better manage your work.

In case you are wondering the screen clip above is using the new App Workspace experience. Next, we will create a new streaming data set.

On the splash page for the app click Skip at the bottom right corner of the page.

Now select +Create > Streaming dataset.

Select API and click next.

Next create the WholeSaleCustomer dataset.

It will have the following field names and data types

Field Name	Data Type
Customer Name	Text
Channel	Number
Region	Number
Fresh	Number
Milk	Number
Grocery	Number
Frozen	Number
Detergents_Paper	Number
Delicassen	Number
Category	Number

Click the Create button to generate the dataset.

Next, we will leverage the generated PowerShell script to create some test records in our newly formed dataset. Click on PowerShell and copy the code into Notepad.

We will create three test records by running the PowerShell code below. Modify the code you coped into Notepad so that it looks simlar to the code below. Before you can run this you will need to replace <Your Key> with the key displayed in your Power BI service.

$endpoint = "https://api.powerbi.com/beta/8c17d9d4-2652-4573-8a9c-d5dde0750715/datasets/13b74183-5eb2-480b-ba11-c0af0ecbdd26/rows?
key=<Your Key>

$payload = @{
"Customer Name" ="Test1"
"Channel" =1
"Region" =1
"Fresh" =98.6
"Milk" =98.6
"Grocery" =98.6
"Frozen" =98.6
"Detergents_Paper" =98.6
"Delicassen" =98.6
"Category" =0
}


Invoke-RestMethod -Method Post -Uri "$endpoint" -Body (ConvertTo-Json @($payload))
$payload = @{
"Customer Name" ="Test2"
"Channel" =2
"Region" =2
"Fresh" =98.6
"Milk" =98.6
"Grocery" =98.6
"Frozen" =98.6
"Detergents_Paper" =98.6
"Delicassen" =98.6
"Category" =1
}


Invoke-RestMethod -Method Post -Uri "$endpoint" -Body (ConvertTo-Json @($payload))
$payload = @{
"Customer Name" ="Test3"
"Channel" =3
"Region" =3
"Fresh" =98.6
"Milk" =98.6
"Grocery" =98.6
"Frozen" =98.6
"Detergents_Paper" =98.6
"Delicassen" =98.6
"Category" =2
}


Invoke-RestMethod -Method Post -Uri "$endpoint" -Body (ConvertTo-Json @($payload))

To do this launch PowerShell in Administrator mode and copy and paste the code into the PowerShell desktop app.

The data set now has three records in it and you can start to use it in Power BI. To do this go to the dataset and click the three dots beside the name of the dataset. This will open a new report with a blank canvas. Add a table and drop all of the fields from the data set into the visual.

As you may notice from the screen shot above the fields Fresh, Milk, Grocery, Frozen, Detergents_Paper and Delicassen are not formatted as currency but should be. Unfortunately, API enabled data sets only have three data types Text, Number and Date and no formatting options so we cannot specify that these fields are currency fields.

Thankfully we can leverage the Report level measures for live connections to Analysis Services tabular models & Power BI service datasets feature that was released in May 2017 to add new measures with the proper currency data type defined.

Add new measures to dataset

Open the Power BI Desktop, log in and select Get Data > Power BI > Power BI dataset.

Select the WholeSaleCustomer dataset.

You may notice that you do not have the ability to modify the data only the ability to report on it.

This is because connecting to a Power BI dataset is equivalent to making a live connection to an Analysis Services tabular model.

Lets add measures to our dataset. Click the more options button beside the dataset and select New Measure.

Create a new measure

$Fresh = SUM(RealTimeData[Fresh])

Format the new measure as Currency by selecting Modeling > Format > Currency general.

Next change the number of decimal places from Auto to 2.

Repeat this for the other fields Milk, Grocery, Frozen, Detergents_Paper, Delicassen. If you drop these new measures into a table, it should look like the image below.

Save the report, I called mine WholeSaleCustomer and publish it to the service.

Interestingly if you open and edit the report in the service after publishing it you will see the newly created measures however if you open the underlying dataset you will not see those new measures.

In the next post I will cover registering the app so that you can push data into it using MS Flow.

Hopefully you have found this to be another practical post.

Until next time

Anthony