June, 2019 - INSPIRIT

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) =>
let
url = “https://northeurope.api.cognitive.microsoft.com/face/v1.0/detect?returnFaceId=true&returnFaceLandmarks=false&returnFaceAttributes=age,gender,smile,facialHair,glasses,emotion,makeup&recognitionModel=recognition_02&returnRecognitionModel=false”,
body = “{ “”url””: “”” & ImageURL & “””}”,
Parsed_JSON = Json.Document(body),
BuildQueryString = Uri.BuildQueryString(Parsed_JSON),
Source = Json.Document(Web.Contents(url,[Headers = [#”Content-Type”=”application/json”,#”Ocp-Apim-Subscription-Key”=”XXXXXXXXXX YOUR API KEY HERE XXXXXXXXXXX”], Content = Text.ToBinary(body) ] )),
#”Converted to Table1″ = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#”Expanded Column1″ = Table.ExpandRecordColumn(#”Converted to Table1″, “Column1”, {“faceId”, “faceRectangle”, “faceAttributes”}, {“faceId”, “faceRectangle”, “faceAttributes”})
in
#”Expanded Column1″
in DataOutput

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!

За четвърта поредна година конференцията SQL Server Discovery Day събра технологичната общност и представи новите тенденции и предстоящи възможности на излизащата в края на годината версия – SQL Server 2019. Покрити бяха теми, засягащи производителността на базите данни, интеграцията и имплементацията на big data клъстъри, както и множество теми, свързани с изкуствен интелект, анализ на данни и архитектурни решения в контекста на облачните инфраструктури. Техническата част от конференцията имаше три основни лекции, които може да намерите по-долу.

What’s new in SQL Server 2019, presented by Magi Naumova

Discovery Day 2019 Sofia – What is new in SQL Server 2019 from Ivan Donev

Big Data clusters in SQL Server 2019 by Rozalina Zaharieva and Dimitar Zahariev

Discovery Day 2019 Sofia – Big data clusters from Ivan Donev

BI and AI in the Microsoft Data Universe, presented by Ivan Donev

Bi and AI updates in the Microsoft Data Platform stack from Ivan Donev

How to use the Microsoft Face API in PowerBI

SQL Server 2019 Discovery Day wrap-up