Within the past few years we’ve seen Power BI / Power Query get new features that are using Artificial Intelligence at its core.
This blog post is an overview of all of these features, what experience they try to provide and what common problems they try to solve.
Column from Example in Power BI / Power Query
AI in Power BI / Power Query first started with the “Column from Examples” feature (url) in which you can provide a sample of how you need the output value to look like based on the data from an existing column and Power BI / Power Query would be able to deliver that exact output that you need:
(an example on how you can type the decimals of the number and Power BI / Power Query would be able to interpret your example and create the code for you – values greyed out are the ones suggested by the algorithm)
They’ve been improving that experience ever since and it’s only getting more and more accurate with its suggestions. Not to mention that you can always look at the M code that it’s creating and jump in if you ever need to make any changes or amends to it.
Table from Example (for Web Scraping) in Power BI / Power Query
A few years ago (on mid 2018) they added this new functionality to the “From Web” connector where you can preview a webpage and be able to simply type some examples of what data you need from the webpage and Power BI / Power Query will take care of the rest so you can get the data that you need. You’re now even able to select any type of html attributes, which means that you can provide an example of the image urls that you’re looking for and Power BI / Power Query will take care of the rest (using CSS Selectors) to grab the specific values that you’re after.
(going through the table using examples for a WebPage – values greyed out are the ones suggested by the algorithm)
New AI Feature in Power BI Desktop November 2019
Microsoft has just added to the Microsoft Power BI Desktop a few experiences that were only available through Power BI dataflows. You can read the full feature release from the official Power BI Blog (url), but I’m going to give you a general overview of what this new version of Power BI Desktop brings to the table.
After you enable these features, they’ll show up in the Add Column menu AND the Home menu under a new AI Transforms group:
You’ll notice that these are basically 3 types of transformations:
- Text Analytics – which uses the Cognitive Services API
- Vision – also uses the Cognitive Services API
- Machine Learning – it exposes your Azure Machine Learning functions / models
Note: One super important requirement to use any of them is that you need to have a Premium Capacity on one of your workspaces in order to use these features.
These features are basically a modified version of the Connector User Experience that I’ve previously defined (here) where it exposes a set of functions that you can use against your current data.
Check more info about Parameters and Functions in Power BI / Power Query from here.
Let’s look at each of them one by one.
The moment that you click on the Text Analytics button you’ll be greeted with a new Navigation and authentication window that should look like this:
Once you finish the authentication process, you can hit the connect button and then wait a few seconds until the service is able to load the available functions from the service:
For now, we only have 3 available functions:
- Detect language – it just does that. It detects the language of a text string and give you the ISO code of the language
- Extract Key phrases – “sift through text and surface important phrases in your data”
- Score sentiment – it
calculates the sentiment score (from 0 to 1) based on the negative and positive phrases
I really wish that they would add a Translator Text function in the future so I can quite easily (and securely!) translate my text string from English to Spanish in most cases.
Putting the last function to the test, I tried this:
Just 3 text strings in Spanish, so it had to automatically detect the language and then do the score sentiment analysis and the result of that is intriguing:
The lower the value the worst sentiment score it go (closer to 0 is negative, closer to 1 is positive).
The first two make sense, but the last one is not so good, but at the same time it’s kind of a colloquial way of say “very bad” or “quite bad” in some countries in Latin America.
Here’s the value that I have in that table and what it translates to in English:
|eso es muy bueno||that’s really good!|
|muy malo||very bad|
|bien malo!||quite bad|
I guess that what I’m trying to say is that I wish that I could pass a query (with a table value) to add my own list of negative and positive keywords / phrases to this algorithm. That would be tremendous.
Similar to what I did before, I have a sample table with essentially two image urls from the last Microsoft Ignite.
It’s one function that requires the image to be either set as a binary or an url (as text).
The result of this function is actually a new table:
I really wish that they wouldn’t automatically expand this new column during the post processing of the response from the API, because now I end up with 1 row for every tag that every image has. I wish that I could do that on my own by having the json field be a record instead of a text value (as you can see on the Text Images.Json column).
Nevertheless, the function appears to be working. I still need to dig deeper into the use case of this one, since I’m not entirely sure of what tags are available to be assigned to an image, but it’s nice to know that this is possible and it determined that there was a person in the second picture.
You can read more about image tagging and how it works from the official documentation (here).
Azure Machine Learning
The last option is for Azure Machine Learning, but if you don’t have any models / functions that you can use then it’ll just give you an error like the following one:
However, if you do have any model then it’ll show like this:
If you still can’t see anything, I’d highly recommend that you read the full documentation about this feature from here.
About the authentication for these features
Here’s comes the extra steps for consultants and other users that work with multiple accounts / tenants at the same time. I initially had to authenticate with an account that has Premium capacity, but I don’t have any models under that one, so if I want to change to another I need to go through the Data Source Settings window like with any other connector and go there to change my credentials:
You can read this full blog post that goes in depth into how to change your credentials inside of Power BI / Power Query.
These are exciting times! If you have Premium capacity then I’m really jealous of you as this will only get better for you and I can imagine that Microsoft is working behind the scenes to enable more integrations / features like this one in the future where you’ll be able to simply invoke one of the Azure service on top of your data with just one single click – no more tedious setup. This is the dream for any business analyst.
Let me know in the comments section below what you think about these new features.
Excel specialist turned into BI specialist using the latest tools from Microsoft for BI – Power BI. He is the co-author of ‘M is for Data Monkey’, blogger and also Youtuber of powerful Excel video Tricks.
He has been recognized as a Microsoft Most Valuable Professional (MVP), is a Microsoft Certified Professional (MCP – MCSA: BI Reporting), a Microsoft Certified Trainer (MCT), and is one of the international pioneers in Power Pivot, Power Query and Power BI.