Flow, Azure ML and Power BI: Achievement Unlocked

If you've been following Microsoft lately, you'll note Microsoft's self proclaimed, "mobile first, cloud first" strategy. In my opinion, there is no better beneficiary of this new direction than Microsoft's Power BI offering. Power BI, a self-service BI solution allows you to create and author slick reports and publish them to a a web portal to share with users inside your organization, outside of it or even the general public. An added benefit of the solution is its rapid release schedule, providing updates on a monthly basis, coupled with a growing user community providing support and custom visuals. Custom visuals allow you to extend the solution and visualize your data in new and unique ways specific to your business requirements.  While this at a high level is designed for mass adoption, what puts the solution over the top is the disruption pricing - free to 9.99/user/month. The price schedule provides you with additional features and functionality mostly around refresh and content authoring.

The purpose of this post however is to introduce you to a new solution, appropriately called Flow, from Microsoft. Flow allows you to create custom workflows based on specific conditions or actions you dictate - without code. In unison Power BI and Flow are, I believe "game changers."

In the next few paragraphs I'm going to show you how to build a "Flow" and bring in the Tweets for analysis using Power BI and Azure Machine Learning services - with very little code.

The use case: As an objective citizen, you want to analyze tweets containing the hashtag #Trump and perform text analytics to better understand Tweets containing this hashtag.

I've split this post into several sections to explain how and where to setup each service and application required to replicate this example. Feel free to skip along and reference as you might need. I also want to point out that I used an iphone to write the "flow" - responsive design is a nice thing.

Create a Destination File for Tweets
If you have OneDrive for Business, you will want to create a file that we'll use later. I was interested in analyzing tweets with the #Trump hashtag, so I created a file called "Trump Tweets.xlsx."

Within this file, create three tabs:

• Tweet Text
• Tweeted By
• Created at

Create a table in Microsoft Excel:

Save the file to a OneDrive folder within your root directory:

Sign up for Microsoft Flow Preview

Navigate to: https://flow.microsoft.com/en-us/    and click the sign up link:

Follow the instructions to crate a flow account using your office 365 credentials.

NOTE: Microsoft has created several templates for you to use. It's outside of the scope of this post to investigate

Once you've created an account, you'll see something like this. We'll want to navigate to the My Flows page, illustrated below:

Click on the "Create from Blank" button to begin creating your flow:

Create a Flow for #Trump Tweets

1. Click on Create new Flow:
   
2. On the "How would you like start?" search box, scroll down to:"Twitter - When a new tweet appears" as shown below:
   
3. Add your "query text" - in this case, our hashtag, #Trump will be appropriate:
   
4. Click on the "+" icon to add an action. The button will expand and you will want to click on "Add an action:"
   
5. Once you've clicked the "Add an action" icon, scroll down the available list of actions to the Excel - Insert rows option:
   
6. Enter in the file destination you used previously and select the file within the "File name" dialog box:
   
7. Enter in the table name from above into the "Table name" dialog box:
   
8. Match the appropriate Twitter attributes returned from the query to their respective column definitions:
   
   
   
9. Save the flow and give it an appropriate name:
10. Update the flow

This flow should now be ready for big time. We now have an excel file, stored in the cloud and updated via the Twitter API based on a query we defined - with little code.

Use Power BI to connect to the Excel Spreadsheet created from the flow

Now that we have the file updating dynamically, we need to access the file. Doing this requires a little bit of Office 365 know-how to get the URL from the file and then connect using your organizational credentials in OneDrive for Business. Follow the steps below to retrieve your specific organizational URL. Note: For security reasons I have not specified my organization

1. Get the URL to the file:

Files in OneDrive have public URLs that will allow privileged users direct access to them. Typically, this path looks something like this:

https://companydomain-my.sharepoint.com/personal/username/Documents/FolderName/excel.xlsx

Replace the bolded text with your domain name, username, and path to the file. For me, the path to the file, as depicted above was:

personal/cwebb/Documents/Tweets/Trump Tweets.xlsx

2. In Power BI Desktop, click on "From Web" and paste in the URL to the file.

3. When asked for credentials, click "Organizational Account", then "Sign in" using your office 365 account.

4.  Select the table from the list of available files and utilize the preview to make sure it is the correct table:

5. Edit the table and apply the appropriate transformations to your data model to get it in the desired format:

I performed some minor data cleansing tasks to split the [Created at] column into Date and Time functions

6. Close and apply the changes you have made to the model and save.

Sign up for Microsoft Azure ML (Free)

1. Navigate to https://datamarket.azure.com/dataset/amla/text-analytics.
2. Right click the ribbon on sign in to utilize your organizational, school or work account
3. Subscribe to the Text Analytics API as shown below:

For this demo, we'll subscribe to the free subscription which is limited to 10,000 transactions/month:

Create a parameter to call the Azure Text Analytics Service:

1. To use the single response API in Power BI, we need to create a parameterized function. To do this, we’ll first go to the Get Data ribbon in Power BI and type in "Odata" into the dialog box:
   
2. In the URL, enter the following URL: https://api.datamarket.azure.com/data.ashx/amla/text-analytics/v1/GetSentiment?Text=microsoft
   
   
   
3. You will be prompted for credentials. Select "Marketplace Key" and enter your key as shown below (Don't worry - click Get your Marketplace Account Key to retrieve this secure data and then copy and paste it into the dialog box):
   
4. After entering in the account key, click Save. You should see a sentiment score, which is the sentiment score for the phrase “microsoft”. We need to turn this into a function and parameterize the function.
5.  Click Edit Queries, go to the Home ribbon, click Advanced Editor and use the following text to create the function: 

    let

       Sentiment = (inputtext) =>

   let

       Source = OData.Feed("https://api.datamarket.azure.com/data.ashx/amla/text-analytics/v1/GetSentiment?Text="&inputtext),

       ToTable = Record.ToTable(Source)

   in

       ToTable

   in

       Sentiment

   Now we have parameterized the web service call using a function in the Query Editor. By invoking the function we can now use the Azure service to determine a sentiment score for the data:
   
6. Now, we want to add a custom column to our query in Power BI desktop and reference the function to generate a score for each column:
   
7. We'll also utilize the Azure service's key phrase extraction by repeating steps 5-6, but changing the query:

    let
       KeyPhrase = (inputtext) =>
   let
       Source = OData.Feed("https://api.datamarket.azure.com/data.ashx/amla/text-analytics/v1/GetKeyPhrases?Text="&inputtext),
       ToTable = Record.ToTable(Source)
   in
       ToTable
   in
       KeyPhrase
   

8. We'll also add the another column like we did previously:
   

Now, we have the data we need to continue our analysis - sentiment values and key phrases from our tweets.

Make Sense of Sentiment data

The API returns a numeric score between 0 & 1. Scores close to 1 indicate positive sentiment, while scores close to 0 indicate negative sentiment. This however, may not be super useful to an end-user.

1. Now that each row has received a sentiment score, let's use a conditional column to further assist users of this data. We can do this by utilizing Power BI conditional columns:
   
2. Walk through the dialog to specific an appropriate threshold to dictate positive or negative values:
   

Visualize your data

We've arrived to the fun part of this work - visualizing the data:

Keep in mind that because this is tweet data, it comes as is and can be *quite* inappropriate depending on the raw data you are choosing to visualize. You can always modify the data to remove data that contains poor language.

For the visual above, note that I used several custom visuals. Custom visuals can be had at:
https://app.powerbi.com/visuals

Share your data

One of the most important value propositions to data visualization is the ability to collaborate and share insights. It's outside of the scope of this post to describe how to share and distribute this data, but note that with Power BI - I can share these insights with colleagues, friends and the general public with just a few clicks.

The ability to visualize data using free tools is something that I think is worth sharing. Perhaps this can inspire you to think about the ways you might use Power Apps, Flow, Power BI and Azure ML in unison or explore their capabilities uncoupled. Thanks for reading!