Using Cognitive Services as part of PowerBI dataflows has been made really easy, as long as you have Premium or Embedded capacity. But if you have only Pro/Free account or you need the Face Recognition API , then you need to work some magic to get through. In this post I’ll describe the steps to import data, returned as JSON from the Microsoft Face API and extract facial features into a nice little report. So let’s start with the setup – I have an Azure Blob container, where I have all my pictures and a deployed Face API, using the free tier. Then:
Import in PowerBI the contents of your Azure blob to get the full image path (hint: you’ll need to do some column merging to get the full path)
Make sure you’ve set proper permissions on the blob container
Open the Face API testing console and build your call to the Face API (hint: you need to use your API KEY from Azure Face deployment and a full path to an image, that the face API can access – I’ve done it with a sample image, uploaded in the Azure Blob folder). You API call should look like this:
Make sure you add/adjust all the properties you want from the API to detect i.e. “returnFaceAttributes = age,gender,smile,facialHair,glasses,emotion,makeup “
Now comes the difficult part – constructing the API call in PowerQuery and then transforming the input to meaningful data. The first step here is to open a Blank Query in Query Editor:
Then add the following text, where you need to replace the Ocp-Apim-Subscription-Key value with your API key:
let DataOutput = (ImageURL as text) =>
This creates a function, that accepts an image path and sends it to the Face API, which in turn provides a JSON result with all the faces, recognized on the picture.
Next step is to remove the Errors from the Data column (right click on the column -> Remove Errors) and then Expand the sub-tables (using the expand column in the top right corner of the Data column)
Result should be something like this:
And that’s it. Enjoy!
Exciting data platform announcements at Microsoft Ignite 2019
Leave a Reply
Want to join the discussion?Feel free to contribute!