Edit: It’s not a very intuitive process to do sentiment analysis in Power Query. Good news is that from sometime next week text analytics will be accessible directly from Power BI Desktop. Read below for the announcement from Microsoft or you can read my new post to see how this works in Power BI November updates.
In my last post of book review, the author demonstrated how to use dataflow AI capability for sentiment analysis, but that’s a premium workspace feature. In this post I will show you how you can do sentiment analysis in Power BI desktop.
1. You need to have a Azure subscription. It’s free to sign up and you will also get bonus $280 credit to use Azure services for 30 days.
2. Log into Azure portal and click All resource in the left pane and click Add.
3. Search Text Analytics and click Create
4. Fill required fields and click Create. Note that I have selected the free F0 pricing tier.
5. Once deployed, click on Keys in the left navigation pane and you will see 2 keys. We will need one of them later.
6. To keep this demo simple, I have copied some restaurant review for a place near my office and I have added index column as id. All done in Power Query with a couple of clicks. You need to ensure that text column doesn’t contain empty values and id column doesn’t have duplicates. Also if your id and text columns have different names, rename them to id and text respectively or the Text Analytics will not work.
7. Now rename your query to Message. In the Queries pane right click on the Message query and select Reference, then right click on the newly generated query Message (2) and rename it to SentimentAnalysis.
8. With the SentimentAnalysis query selected in the queries pane, click on the fx button in the fomula bar, you will see a new step called Custom1 added in the Applied Steps pane.
9. Change the fomula to below and you will notice a Json file has been created. This is required because the Text Analytic API needs Json file as input and it will also output Json file later in the steps which we can easily convert to table format with Power Query.
10. Copy one of the Keys from step 5 and create a new Power Query parameter, name it APIKey or something you like and paste the key in Current Value and click OK.
11. Click fx in the formula bar and a new step Custom2 will be created and then replace the M formula with below formula (replace the red text with name from step 4). At this point you will see a Json output has been returned from the API call.
= Web.Contents(“https://textanalysis-demo.cognitiveservices.azure.com/text/analytics/v2.1/sentiment”, [Headers=[#”Ocp-Apim-Subscription-Key”=APIKey], Content=Custom1])
12. Double click the Json icon you will see a record with the documents and errors fields and with List objects as values.
13. Select the List object of the documents field and then click To Table to convert the list to a table.
14. Leave the To Table window as is and select OK.
15. Click the expand button, uncheck the Use original column name as prefix then click OK.
16. Now you will see the id of the text and score returned by the API call. Change the data type for id column to Whole Number by right clicking on id column and select Change Type then finally select Whole Number data type. What’s missing at this point is the text field which we can bring back easily with Merge Queries feature in Power Query.
17. With the sentimentAnalysis query selected in the query pane, click Merge Queries button in the Combine section of Home ribbon. When you see the Merge window, select the Message query from the drop down and make sure id column in both tables are selected then click OK.
18. Click the Expand button, uncheck id and click OK.
19. Now you will see the text along with id and score.
P.S. With the free pricing tier, you can only make 5,000 API calls for free. So don’t not make excessive API calls. Also limit each message under 5,000 characters to avoid any errors and pass less than 1,000 rows of text per API call. Please also disable Power Query background data refresh or you will reach the limit very quickly if you need to do some serious text analytics.
You can download the sample file here. You need enter your own Azure Text Analytics service Key to be able to use the file.