cancel
Showing results for 
Search instead for 
Did you mean: 

Find the sweetest and sourest compliment using Flow and Power BI

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.

 Capture1.JPG

 

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

 

 

Capture2.JPG

 

 

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.

Capture3.JPG

 Capture4.JPG

 

 

 

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.

Comments

Nice, Microsoft Flow, Power BI, and Cognitive Services is excellent convination. 

Meet Our Blog Authors
  • Working daily with Microsoft Cloud to deliver the needs of my company, my customers and various Microsoft communities and forums. | Office 365 | Flow | PowerShell | PowerApps | SharePoint |
  • Co-founder of https://plumsail.com, Office 365 and SharePoint expert. Passionate about design and development of easy to use, convenient and flexible products.
  • Microsoft Business Apps MVP. Owner of ThriveFast, an Office 365 consulting company.
  • 7x Microsoft Business Solutions MVP (CRM)
  • Solution Architect with Slalom, and organizer of the Boston Office 365 User Group, and long term SharePoint/Office 365 veteren. Find more at http://www.davidlozzi.com. Follow @DavidLozzi
  • I'm keen in MS technologies, SharePoint, Office 365 and development for them
  • Daniel is a Business Productivity Consultant & Microsoft Business Solutions MVP who is very enthusiastic about all things Office 365, Microsoft Flow, PowerApps, Azure & SharePoint (Online). Since the preview, Daniel has been working with Microsoft Flow and later on with Microsoft PowerApps. That led to him being awarded an MVP Award for Business Solutions. He loves to blog, present and evangelize about improving productivity in the modern workspace with these amazing tools!
  • Michelle is an Office 365 solution architect in Twin Cities, MN. She has been delivering business collaboration solutions for years with her focus on SharePoint and Office 365. Michelle is a recent board member of the Minnesota Office 365 User Group and has been a member of the SharePoint community since 2009. She is a frequent speaker at MNSPUG and SharePoint Saturday and co-chaired the Legal SharePoint User Group for 4 years. Her most frequent projects have involved rolling out a large deployment of Office 365, SharePoint Online intranet, build of a "CHAMPS" Office 365 user adoption program and most recently, SharePoint On-Premise to Online Migration. Michelle is very excited about cloud technology as it is shifting her IT Pro focus to collaboration strategy and technical adoption.
  • I'm a Microsoft Office Servers and Services MVP with a special interest in SharePoint, Office 365, Microsoft Flow, Microsoft Teams and PowerApps. I work at Triad Group Plc ( https://triad.co.uk)
>