Text Entity Extraction in Power BI, Text Analytics Service

Another service to Microsoft Cognitive Text Analytics API is Text Analytics API, which is about Entity Linking, and Named Entity Recognition (NER).

Entity Linking: Entity Linking able to identify the disambiguate the identity of an entity found in the text (for example, determining whether an occurrence of the word Mars refers to the planet, or to the Roman god of war).

Entity Linking refer to Wikipedia website as the main knowledge base. for example:

The result of the Entity Linking extraction is the link to the wikipedia about Mars and Sun

The other service is

Named Entity Recognition (NER):

This service is also included in the Entity Recognition in Text Analytics that able to classify different entities in a sentence to prebuild class as :

Person, Location, Product and Organization, Quantity and so on

in the above example, it able to recognize three different Entity types as Quantity, Location, and Other…

How to use it in Power BI desktop?

To extract the entity, you can use the same Text Analytics API in Microsoft Cognitive Service as we use for Sentiment Analysis, Keyword Extraction and language detection.

We can use the same API and Endpoint for the Text Analytics.

1- Open Power BI desktop

2- Navigate to Transform Data to Power Query

3- In the Power Query, click here to create a new blank Query

4- in the new Query click on the Advance Editor, paste the below code

(text) => let    
apikey      = "API Key",
    endpoint    = "https://AustraliaEast.api.cognitive.microsoft.com/text/analytics/v2.1/entities",
    jsontext    = Text.FromBinary(Json.FromValue(Text.Start(Text.Trim(text), 5000))),
    jsonbody    = "{ documents: [ { language: ""en"", id: ""0"", text: " & jsontext & " } ] }",
    bytesbody   = Text.ToBinary(jsonbody),
    headers     = [#"Ocp-Apim-Subscription-Key" = apikey],
    bytesresp   = Web.Contents(endpoint, [Headers=headers, Content=bytesbody]),
    jsonresp    = Json.Document(bytesresp),
    doc=jsonresp[documents]{0},
    result=doc[entities]
in result

It is a function we use for the Text Analytics, to connect to the Microsoft Cognitive Service, Text Analytics Service.

Just write “Auckland is a nice and fun city”.

It will return a list as record

To make it functional I need to expand the record and drill down to the small granularity.

Next click on the Advanced Analytics, and copy the transformation part.

,
#"Converted to Table" = Table.FromList(result, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"name", "matches", "wikipediaLanguage", "wikipediaId", "wikipediaUrl", "bingId", "type"}, {"name", "matches", "wikipediaLanguage", "wikipediaId", "wikipediaUrl", "bingId", "type"}),
#"Expanded matches" = Table.ExpandListColumn(#"Expanded Column1", "matches"),
#"Expanded matches1" = Table.ExpandRecordColumn(#"Expanded matches", "matches", {"wikipediaScore", "entityTypeScore", "text", "offset", "length"}, {"wikipediaScore", "entityTypeScore", "text", "offset", "length"})
in 
#"Expanded matches1"

Then in the query, click on the Advanced Editor, and replace the code as below

Then just in the editor test it as below

“Auckland and Sydney are big cities”

The result sends a link to Wikipedia and identifies the type of entity as location.

Leila Etaati

Trainer, Consultant, Mentor

Leila is the first Microsoft AI MVP in New Zealand and Australia, She has Ph.D. in Information System from the University Of Auckland. She is the Co-director and data scientist in RADACAD Company with more than 100 clients in around the world. She is the co-organizer of Microsoft Business Intelligence and Power BI Use group (meetup) in Auckland with more than 1200 members, She is the co-organizer of three main conferences in Auckland: SQL Saturday Auckland (2015 till now) with more than 400 registrations, Difinity (2017 till now) with more than 200 registrations and Global AI Bootcamp 2018. She is a Data Scientist, BI Consultant, Trainer, and Speaker. She is a well-known International Speakers to many conferences such as Microsoft ignite, SQL pass, Data Platform Summit, SQL Saturday, Power BI world Tour and so forth in Europe, USA, Asia, Australia, and New Zealand. She has over ten years’ experience working with databases and software systems. She was involved in many large-scale projects for big-sized companies. She also AI and Data Platform Microsoft MVP. Leila is an active Technical Microsoft AI blogger for RADACAD.