Sentiment Analysis in Power BI, analyse text reviews and a Premium tip.

Thumbnail

Power BI is great at analysing numeric data. Numbers like values, costs, stock holdings and averages can all be worked up into informative charts and visualisations. But what if your data isn’t like that? What if your data is more textual, like product reviews, staff evaluations or survey results? How can you visualise that data with Power BI ? This post explains how you can work with that sort of data in Power BI using the AI Insights in Power Query. This is a Premium feature, but I’ll also explain how you can use a cheaper licence than full Premium if you just want this text analysis.

How to Analyse Textual Data ?

The data I’m using is a set of reviews of wines and looks like this..

Image
Spreadsheet

This is not easy data to build charts with. The review column contains a great deal of text for each entry, how can I tell what is good and what is bad ? I could count word occurrences, and look for words like “Bad”, “Sour”, “Good” or “Sweet”, but it’s going to be really difficult. I’d have the same problem with any textual data like this, these Wine reviews are just an example, this could be Evaluations, Survey Responses, Facebook Posts or Tweets as just some examples.

Power Query to the Rescue !

As usual, when we import data into Power BI it goes through Power Query, so I can bring this data into my data model. As I transform this data I can use a new function that turned up in recent updates to Power Query, AI Insights.

Image
Screenshot

AI Insights provides a number of “Artificial Intelligence” transformations to data. The one I want to use is the text analysis function “Score Sentiment” this will read my reviews column and measure the positive or negative sentiment of the words and phrases in the review. For each row in the reviews column it will generate a number on a scale of zero to one, with one being the most positive. Once I’ve got the sentiment score I can begin to analyse the “Best” and “Worst” reviews.

But wait, there’s a fly in the ointment.

The particular problem here is that to use AI Insights you need a Power BI Premium Licence! If you have one great, carry on. If not, don’t despair, let me show you how Sentiment Analysis works, then I’ll explain how that Premium Licence may not be as expensive as you thought.

Image
Screenshot

Selecting Text Analytics on the AI Insights menu brings up the Text Analytics dialog. I simply select “Score Sentiment” and the Text in the “Review” column. I don’t need to select a language; Power Query will work that out for itself. I do need to have a working Premium Capacity to get this far.

Clicking OK runs the Sentiment Analysis on my reviews and I end up with a new column “Score Sentiment

Image
Screenshot

Oddly the sentiment value is returned as data type “Any”, so I turn it into a decimal number in Power Query. Straightaway I can have some fun with this data. Sorting this column from lowest to highest puts the “Worst” review first.

Image
Screenshot

Eww, that Chardonnay sounds really bad “Unpleasant and flinty”, “like someone watered down a bad vintage”, “Not at all nice”. I won’t be looking for that in the wine store! Obviously Sorting Highest to Lowest puts the “Best” review at the top.

Image
Screenshot

 

With a sentiment score of 0.994294879 (remember the maximum is 1) that Sauvignon Blanc sounds delicious!

Now I’ve got my sentiment score in the data, I can load it into the Power BI Data Model and start building some charts.

Image
Screenshot

A couple of clicks and I’ve built a bar chart showing average Sentiment score by Country. Using the Average of each Country’s Sentiment shows that Austrian wines are best with an average score of 0.95. Whereas Portuguese wines only average out at 0.53. (Hey sorry Portugal, this is random data, no reflection on your fine viniculture!)

A Premium Problem.

Of course as I said at the beginning, the AI Insights in Power Query require a Premium Licence. The “Standard” Premium licences, and there are several, are aimed at large corporate Power BI Users and are quite expensive. Each Premium licence gets more powerful as you go up the P numbers, allowing manipulation of millions of gigabytes of data and hundreds or thousands of users, but as you’ll see this power comes at a price.

Product

Premium P1

Premium P2

Premium P3

Premium P4

Premium P5

Cost per month

£ 3,766.70

£ 7,537.20

£ 15,078.20

£ 30,160.20

£ 60,324.20

Yes, unless you’re a large company with several hundred Power BI users you’re probably not going to afford a Premium Licence.

(Prices correct in July 2020 Check with Microsoft as these may change over time)

However, there is another, lesser known set of Premium licences which may work out more cost-effective for SME’s. These are the “Azure Power BI Premium Licences”. These are licenced through Microsoft’s Cloud based service Azure, and the prices look a little different.

Licence Type

Dedicated Infrastructure?

Virtual Cores

Memory

Price Per Hour

A1

No

1

3 GB RAM

£ 0.7514

A2

No

2

5 GB RAM

£ 1.4967

A3

Yes

4

10 GB RAM

£ 2.9992

A4

Yes

8

25 GB RAM

£ 6.0046

A5

Yes

16

50 GB RAM

£ 12.0152

A6

Yes

32

100GB RAM

£ 24.0367

Hmm, now the numbers look more my size! As Azure is a “Software as a service” (SAAS) type product these licences are charged by the hour, not all the time. So if you don’t need the Premium features, just pause the A licence you have and you won’t be charged. Turn it on when you need it and you’ll be charged a minimum of 1 hours usage. Of course it’s never totally simple, different Power BI features need different A Premium Licences. For instance, Sentiment Analysis needs at least an A2 licence. (Paginated Reports – about which I’ll blog later, needs an A4 or greater licence). But for me, just about £1.50 per hour to analyse text data with Sentiment Analysis looks pretty good. I’m only going to refresh the data once a week or so, so if I turn on the A2 licence for five hours a week that’s… £7.50 a week or just over £30 a month, that’s affordable.

A final caveat

Now of course There Is No Such Thing As A Free Lunch! If you opt for this approach you’ll be responsible for starting and stopping the “A” Premium Licence you’ve chosen, leave it running and it might prove expensive. You’ll have to do a bit of reading to fully understand all that these A licences can and can’t do. But if you need these facilities, I think it’s worth it. I’ve added some resources below that I’ve used to get this approach working and I’ll be blogging about it more in future posts.

What is Power BI Premium? (Microsoft)

Testing Power BI Premium Features with Power BI Embedded (Chris Webb)

Understanding the Power BI Capacity based SKUs (John White)

Burningsuit are Trainers and Consultants in Power BI, specialising in teaching business people how to use Power BI for Data Analytics. We believe that this tool can be used by all types of business large or small, and love teaching SME’s how they can punch above their weight and use the same tools large corporates use, without the expense or complexity. If you’d like more information on us or our courses get in touch with us on 0800 0199 746 or email us.

 

Add new comment

The content of this field is kept private and will not be shown publicly.