Find the sweetest and sourest compliment using Flow and Power BI

[darogael]

Introduction

As someone who simply cannot win a raffle price I decided to remove 'Luck' out of the equation and instead leverage Flow, Cognitive Service Text Analytics and Power BI to make price winning a little more interesting and fare. One would even say I added a new meaning to the phrase 'Luck favors the prepared'.

Description

This blog walks you through how event speakers or community organizers can give out prizes to the more sweetest or sourest compliment provided. The compliments can be gathered using email or comments posted on social media such as Twitter.

Requirements

• Microsoft Flow subscription.
• A place to store data. Either an Excel Spreadsheet stored in OneDrive, a SharePoint List or a SQL (Azure or On-Premises) table.
• Power BI Pro

If you choose to store the data in Excel then I've attached a copy of the Excel spreadsheet with the columns and some data in it. If you choose SharePoint List then I've attached a copy of an STP file with some items in it. If you choose to store in a SQL table then below is query you can run to create the SweetSourComments table.

CREATE TABLE SweetSourComments

(

Name nvarchar(100),

Email nvarchar(500),

Comment nvarchar(100),

SentimentScore DECIMAL(4,2),

Event nvarchar(100),

CreatedOn date

); 

Flow

In this example I'm saving the data to a SharePoint list and hence you can see SharePoint as one of the connections. I'm also using incoming email for the attendees to send me the compliments and hence I'm using Office 365 Outlook and the Content Conversion. The Content Conversion stripes off the HTML code from the email body and gives me text only. The text is then sent to Cognitive Service Text Analytics to give me the Sentiment values after which it is saved to the SharePoint list.

 

Here is a screenshot of what the Flow looks like. I've attached a copy of the Flow zipped file with this blog.

Power BI Report

In this section the key item to focus on are the 6 New Measure Calculations that were created, 3 for the Highest Compliment and 3 for the Lowes Compliment. Here are the formulas for the New Measure Calculations

Highest Compliment

HighestSentiment = MAX(EventComments[sentiment]) . This is used to find the highest sentiment score i.e. the most sweetest comment

HighLookupPerson = LOOKUPVALUE(EventComments[Title],EventComments[sentiment],[HighestSentiment]). This is used to find who the person is who posted the sweetest comment.

HighestComment = LOOKUPVALUE(EventComments[comment],EventComments[sentiment],EventComments[HighestSentiment])). This is used to find the sweet comment

Lowest Compliment

LowestSentiment = MIN(EventComments[sentiment]). This is used to find the lowest sentiment score i.e. the most sourest comment

LowestLookupPerson = LOOKUPVALUE(EventComments[Title],EventComments[sentiment],[LowestSentiment]). This is used to find who the person is who posted the sourest comment.

LowestComment = LOOKUPVALUE(EventComments[comment],EventComments[sentiment],EventComments[LowestSentiment]). This is used to find the sweet comment

The Power BI Report file has been attached to this blog.

 

Video

This video deep-dives in the Flow and the Power BI report

Conclusion

Something to keep in mind is when using the connection, the operations you do will count towards the quota set on your Cognitive Services key. When that quota is exceeded you may see errors in the connector, or your Cognitive Services account may be charged for additional requests, depending how your account is configured. More information you can click here.

SweetAndSourCommentsFlow.zip

SweetAndSourComments2.zip