Sentiment Analysis with Power BI and Microsoft Cognitive Services

Sentiment Analysis is the analysis of text to see if the overall sentiment is Positive or Negative. It would be commonly used by brands when listening to what their customers are saying across social media, such as Facebook and Twitter comments and posts.  It’s also often used to analyse survey results.  In my example below, I have used Microsoft cognitive Services Text Analytics and Power BI to find the sentiment of reviews left for my online courses.

Power BI (Desktop) is free to download and use from Microsoft.  Basically it is a self-service Business Intelligence tool. (Get Power BI Now).  If you are totally new to Power BI then it might be worth taking the time to complete our free course ‘Power BI – the Ultimate Orientation’ which is also available on Android (download free app now)

Microsoft Cognitive Services is an online services that allows you run analytics such as sentiment analysis with the use of an API key.  There is a free version that will allow you send 5000 calls per month to the Text Analytics API

This article covers

  • Obtain a Text Analytics API Key from Microsoft Cognitive Services
  • Power BI – Setting up the Text Data
  • Setting up the Parameter
  • Setting up the Custom function Query(with code to copy)
  • Grouping the text
  • Running the sentiment analysis by calling the custom function.
  • Extracting the sentiment from the returned Json file.

A PDF version of this article is available to download further down in the article

Sign up for my newsletter – Don’t worry, I wont spam. Just useful Excel and Power BI tips and tricks to your inbox

Microsoft Cognitive Services

To use any of the Microsoft Cognitive services you must first sign up.  Sign up is free and easy. 

https://www.microsoft.com/cognitive-services.

Obtain a Text Analytics API Key from Microsoft Cognitive Services

Once you have an account and you have signed into Microsoft Cognitive Services, select API’s.  You will find the Text analytics under the Language API’s

microsoft cognitive services text analytics api

When you select Text analytics you will be brought to a new screen, where you can select ‘Get started for Free’.

This will then bring you to a new screen that contains your unique API.  (It is best practice not to share your API keys, keep this code to yourself)  From here you can take a copy of your API key as you will need this later in Power BI.

text analytics with Power BI and microsoft

Microsoft Cognitive Services will all you make 5000 calls to the service free each month.  Each call can be a batch of 1000 texts.  That means that you can analyse 5M comments per month.  The free service should be enough for most of us.

Power BI

Getting TEXT Data Ready 1.

Once you have connected Power BI to the text data that you wish to analyse, we can get started on preparing the data for Cognitive Services.

Make a reference copy of your data query and rename the query.  We will work from this query from now on.

There are two columns required by Microsoft Cognitive Services. A column named id and a column named text. Use the query editor to remove any columns that you don’t need, leaving the two correctly name columns

Power BI desktop

Each item of text in the data table needs to have its own unique ID number.  This is the id column.  The column named text is the column that contains the text you want to analyse. You also need to ensure that there is no duplicates in the text and there are no blank rows.

To remove duplicates and Blank rows, select the column, then in the Home ribbon select Remove rows and Remove blanks and remove duplicates.

remove duplicates in powerbi

We now have our text data almost ready for analysis.

Setting up the Query Parameter

Next we will set up the Query Parameter to hold the API key that we copied from Microsoft Cognitive Services.

On the Home table Select Manage Parameters, and then select new to add a new parameter.  You need to give the Parameter a name, for example TEXTAPIkey.  It is important that you name the parameter as you will need this name again later.

After this we can skip down to the current value field and here we can paste in the API key we copied from Microsoft Cognitive Services.  Finally we can select ok.

Query parameter in Power BI

Once you press ok, you will see the parameter will appear in the queries pane.

Setting up the Custom Function

Next we will use the M programming language to create a custom query. This will not really be a query but a function. On the Home tab select ‘New Source’ and then select Blank Query.  Then, also in the Home tab, select advanced editor.

query editor in Power BI

In the query editor the following code needs to be pasted.

(Source as table) as any =>

let

    JsonRecords = Text.FromBinary(Json.FromValue(Source)),

    JsonRequest = "{""documents"": " & JsonRecords & "}",

 

    JsonContent = Text.ToBinary(JsonRequest, TextEncoding.Ascii),

    Response =

        Web.Contents("https://westus.api.cognitive.microsoft.com/text/analytics/v2.0/sentiment?",

            [

                Headers = [#"Ocp-Apim-Subscription-Key"= APIkey,

                           #"Content-Type"="application/json", Accept="application/json"],

                Content=JsonContent

            ]),

    JsonResponse = Json.Document(Response,1252)

in

    JsonResponse

The only part of the code that requires changing is APIkey.  Replace this text with the name of the parameter that you have set up.

Let me explain this code a little to you.  First of all Microsoft cognitive services needs a Json file.  This query will create a Json file from a table, send it to Microsoft Cognitive Services Sentiment Analysis, and call the parameter with our API key and then expects a Json file to be returned.

When we press ok, this query will appear in the query pane as a function.

 

Getting TEXT Data Ready 2.

Power Bi Desktop training
Course Spotlight

 We still have a little work to do now on our text data table. You will recall that TEXT analytics allow you to send a call of 1000 comments to be analysed and this is classes as 1 call. For this reason it is best to group your comments into groups of 1000.

To do this select Add Column from the tabs and then select ‘Index Column’.  This will add a new column to the data, beginning with 0.

index column in Power BI

 We can now change the integer type from Standard to Divide(integer)

divide integer power bi

This opens a new dialogue box, where the value of 1000 is entered

integer divide in power bi

This will now create a new column.  In this column the first 1000 comments will have the value 0, the second 1000 comments will have a value of 1 and so forth.

Now we have the ability to group the comments or text to be analysed into groups of 1000.  To do this, on the Transform tab select Group By

Sentiment Analysis with Power BI and Microsoft Cognitive Services

This will open a new dialogue box.  First we need to select ‘Group by’ and change it to be the new column ‘Inserted Integer-Division’.  The new column name is a name that you wish the new table to be called.  Then change the operation to be All Rows.

Sentiment Analysis with Power BI and Microsoft Cognitive Services

This will now convert all of our data into a new table with a column newtable that contains a number of tables of data.  These tables each contain 1000 comments to be analysed by Microsoft cognitive Services Text Analytics.

Sentiment Analysis with Power BI and Microsoft Cognitive Services

Next we will remove all columns except the newtable column and we are now ready to run our Sentiment analysis through Power BI and Microsoft Cognitive Services. 

DOWNLOAD

Sentiment Analysis with Power BI and Microsoft Cognitive Services

AS PDF TO KEEP AS A HANDY REFERENCE

no email required

TEXT Sentiment Analysis using Power BI and Microsoft Cognitive Services

To run the TEXT Sentiment Analysis using Power BI and Microsoft Cognitive Services the next step is to invoke our custom function that we created earlier.

Select the Add Column tab and then select Invoke Custom Function.  You will then get a new dialogue box.  Select the Function Query that we set up.  This will then give a new drop down for Source. Here you need to select the newtable that we set up earlier

Sentiment Analysis with Power BI and Microsoft Cognitive Services

Our custom function now sends the text data off using the API to Microsoft Cognitive services and it returns a Json file.  You will see this as a new column containing a record.  We need to do just a little more work to extract our Sentiment values.

Sentiment Analysis with Power BI and Microsoft Cognitive Services

First remove the Column with the Table as we have already passed this table to the text analysis. Next the Record needs to be expanded by clicking on the small box in the column header with the arrows on it.

Sentiment Analysis with Power BI and Microsoft Cognitive Services

This will return a table with two columns. One with named documents and the other named errors. Both columns contain lists.  We can remove the errors column.  The next step is to expand the documents column to new rows which will turn the list into records.

Sentiment Analysis with Power BI and Microsoft Cognitive Services

We then need to expand the value again and we will be returned with a table that contains the sentiment score and the comment id.

Sentiment Analysis with Power BI and Microsoft Cognitive Services

How to read the Sentiment Score

The score returned by Microsoft Cognitive Services Text Analytics is between the value of 0 and 1.  It’s a decimal number.  Behind the scenes Microsoft runs a series of machine learning text based algorithms to come up with this score. A value of 1 would indicate a very positive sentiment, where as a value of 0 shows negative sentiment.

Once you load this data into Power BI, it can be connected via relationships to other tables of data, analysed with DAX and visualized with any of the many visualizations available in Power BI.

If you found this article - 'Sentiment Analysis with Power BI and Microsoft Cognitive Services' - useful I hope that you will take the time to share and link back to this article

6 thoughts on “Sentiment Analysis with Power BI and Microsoft Cognitive Services

  1. Hi
    When I paste the code

    let
    JsonRecords = Text.FromBinary(Json.FromValue(Source)),
    JsonRequest = “{“”documents””: ” & JsonRecords & “}”,
    JsonContent = Text.ToBinary(JsonRequest, TextEncoding.Ascii),
    Response =
    Web.Contents(“https://westus.api.cognitive.microsoft.com/text/analytics/v2.0/sentiment?”,
    [
    Headers = [#”Ocp-Apim-Subscription-Key”= APIkey,
    #”Content-Type”=”application/json”, Accept=”application/json”],
    Content=JsonContent
    ]),
    JsonResponse = Json.Document(Response,1252)
    in
    JsonResponse

    into the Power BI editor it returns an error.

    I substituted ‘APIKey’ with my paramater (SteParam1) bu the editor returns, “Expression.SyntaxError: Token Comma expected.”.

    Please could you advise what I may be doing wrong.

    Thanks

    Steve

    1. Steve,

      Please add (Source as table) as any => to the start of your code. (this was missing from the code in the post above but has now been updated)

      Should work now

      Paula

  2. Hi Paula,
    I’m following the sentiment analysis configuration – everything’s fine until I attempt to connect to MS servers. I get the following error & I’m not sure where I change the connect to anonymous. Any ideas? Thanks, Martyn

    An error occurred in the ‘’ query. DataSource.Error: Web.Contents with the Content option is only supported when connecting anonymously.
    Details:
    DataSourceKind=Web
    DataSourcePath=https://westus.api.cognitive.microsoft.com/text/analytics/v2.0/sentiment

    1. You need to change your settings Martyn. Please go to file, then options, then privacy settings. Then select Always ignore privacy settings. This should get rid of the error

Leave a Reply

%d bloggers like this: