Okay, the title looks like marking stuff! At my last session for the Power BI user group for the Netherlands, an attendee asks me, if it’s possible to use Power BI, the Azure Sentiment Analysis and the incremental refreshing functionality. I said yes, but to be honest, I was not sure. Now I have tested it, and now, I can say YES!
The story for this session was very easy, you get mails, put them to a storage, read the data to a Power BI report and use the Sentiment Azure Analysis API to get the Language and the sentiment of it and analyze the data. This blog post shows this step by step and also with the incremental refreshing of a Power BI data set.
The Mailbox:
I used for this approach an Office 365 mailbox for my received mails:
Next, I’ve created an Excel File into my OneDrive.
Then I created a Power Automate Flow which connects to the mailbox and also the Excel file:
It’s a good idea to convert the body to text, because when you receive HTML mails, it can be possible you get confusing results when you use the text analysis. You can test it, when you send an email to this box, the trigger should write the data to the file.
Next I’ve created a Text Analysis service at Azure. How to do it, please take a look to this post.
Now we need the Power BI report. Incremental refresh is now GA and also available with the Pro-license: https://powerbi.microsoft.com/de-de/blog/incremental-refresh-is-generally-available/
If you are more interested about this, please take a look to this blogs:
https://radacad.com/all-you-need-to-know-about-the-incremental-refresh-in-power-bi-load-changes-only
https://blog.crossjoin.co.uk/2020/04/13/keep-the-existing-data-in-your-power-bi-dataset-and-add-new-data-to-it-using-incremental-refresh/
To make a better test, I’ve created also with Powre Query (M) a calculated column with the imported date/time:
If you create this column with DAX, it will be refreshed on every refresh. This column will be only refreshed if the row will be imported or gets an update.
After I setup the incremental refresh, published the report to PowerBI.com and configured the refresh, it’s time to wait to get new data from your mailbox and for a new timeframe …
After this you can view your data:
When you check the metrics at Azure for the sentiment service, you see only the calls for newly received mails.
The Azure API are available for different pricing plans, also for a free plan without cost. But it is limited. But when you receive many emails, you reach very quickly the limit, because for every mail you need three calls (Language, Key Phrases, Sentiment). With the incremental refresh you only send new rows to the API and you can safe money. The refresh is also very quick, because not the whole data must be refreshed.
I think this can be a good solution for companies, to the sentiment of their customers who send them emails about their services, products etc. You only need a Power BI Pro license, a free Azure subscription and an Office 365 account.