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..
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.
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.
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”
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.
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.
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.
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.
|Cost per month|
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.
Price Per Hour
3 GB RAM
5 GB RAM
10 GB RAM
25 GB RAM
50 GB RAM
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.
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.