Power BI

Getting TEXT Data Ready 1.

Once you have connected Power BI to the text data that you wish to analyse, we can get started on preparing the data for Cognitive Services.

Make a reference copy of your data query and rename the query.  We will work from this query from now on.

There are two columns required by Microsoft Cognitive Services. A column named id and a column named text. Use the query editor to remove any columns that you don’t need, leaving the two correctly name columns

Each item of text in the data table needs to have its own unique ID number.  This is the id column.  The column named text is the column that contains the text you want to analyse. You also need to ensure that there is no duplicates in the text and there are no blank rows.

To remove duplicates and Blank rows, select the column, then in the Home ribbon select Remove rows and Remove blanks and remove duplicates.

We now have our text data almost ready for analysis.

Setting up the Query Parameter

Next we will set up the Query Parameter to hold the API key that we copied from Microsoft Cognitive Services.

On the Home table Select Manage Parameters, and then select new to add a new parameter.  You need to give the Parameter a name, for example TEXTAPIkey.  It is important that you name the parameter as you will need this name again later.

After this we can skip down to the current value field and here we can paste in the API key we copied from Microsoft Cognitive Services.  Finally we can select ok.

Once you press ok, you will see the parameter will appear in the queries pane.

Setting up the Custom Function

Next we will use the M programming language to create a custom query. This will not really be a query but a function. On the Home tab select ‘New Source’ and then select Blank Query.  Then, also in the Home tab, select advanced editor.

In the query editor the following code needs to be pasted.

(Source as table) as any =>

let

    JsonRecords = Text.FromBinary(Json.FromValue(Source)),

    JsonRequest = “{“”documents””: ” & JsonRecords & “}”,

    JsonContent = Text.ToBinary(JsonRequest, TextEncoding.Ascii),

    Response =

        Web.Contents(“https://westus.api.cognitive.microsoft.com/text/analytics/v2.0/KeyPhrases?”,

            [

                Headers = [#”Ocp-Apim-Subscription-Key”= APIkey,

                           #”Content-Type”=”application/json”, Accept=”application/json”],

                Content=JsonContent

            ]),

    JsonResponse = Json.Document(Response,1252)

in

    JsonResponse