{"id":677,"date":"2019-12-27T12:43:20","date_gmt":"2019-12-27T10:43:20","guid":{"rendered":"http:\/\/www.flip-design.de\/?p=677"},"modified":"2019-12-27T12:43:20","modified_gmt":"2019-12-27T10:43:20","slug":"text-analysis-with-power-bi-in-different-languages","status":"publish","type":"post","link":"https:\/\/www.flip-design.de\/?p=677","title":{"rendered":"Text Analysis with Power BI in different languages"},"content":{"rendered":"\n

According to the msdn article „How to integrate Text Analysis into Power BI<\/a>„, I needed to detect the language of an comment and make a sentiment analysis of it. The reason to make it parametrized and not change the language key is easy, mostly in Germany I have comments in English, German Spain etc.<\/p>\n\n\n\n

So, after I completed the Howto above, i created a new M Function named „Language“ with this code:<\/p>\n\n\n\n

\/\/ Returns the two-letter language code (for example, 'en' for English) of the text\n(text) => let\n     apikey      = \"YOUR_API_KEY_HERE\",\n    endpoint    = \"https:\/\/<your-custom-subdomain>.cognitiveservices.azure.com\/text\/analytics\" & \"\/v2.1\/languages\",\n    jsontext    = Text.FromBinary(Json.FromValue(Text.Start(Text.Trim(text), 5000))),\n    jsonbody    = \"{ documents: [ { id: \"\"0\"\", text: \" & jsontext & \" } ] }\",\n    bytesbody   = Text.ToBinary(jsonbody),\n    headers     = [#\"Ocp-Apim-Subscription-Key\" = apikey],\n    bytesresp   = Web.Contents(endpoint, [Headers=headers, Content=bytesbody]),\n    jsonresp    = Json.Document(bytesresp),\n    language    = jsonresp[documents]{0}[detectedLanguages]{0}[iso6391Name]\nin  language<\/code><\/pre>\n\n\n\n
<\/p>\n\n\n\n
The code above is 1:1 from the msdn webpage to get a two letter code of the language of the key merged subject and body.<\/p>\n\n\n\n
\"\"
This is also similar to the other steps, but it must be the first invoke function. The next step is to get the Key Phrases, but depending of the language of the column which are created before.<\/figcaption><\/figure>\n\n\n\n
\/\/ Returns key phrases from the text in a comma-separated list\n(text,lang as text) => let\n     apikey      = \"YOUR_API_KEY_HERE\",\n    endpoint    = \"https:\/\/<your-custom-subdomain>.cognitiveservices.azure.com\/text\/analytics\" & \"\/v2.1\/keyPhrases\",\n    jsontext    = Text.FromBinary(Json.FromValue(Text.Start(Text.Trim(text), 5000))),\n    jsonbody    = \"{ documents: [ { language: \"\"\" & lang & \"\"\", id: \"\"0\"\", text: \" & jsontext & \" } ] }\",\n    bytesbody   = Text.ToBinary(jsonbody),\n    headers     = [#\"Ocp-Apim-Subscription-Key\" = apikey],\n    bytesresp   = Web.Contents(endpoint, [Headers=headers, Content=bytesbody]),\n    jsonresp    = Json.Document(bytesresp),\n    keyphrases  = Text.Lower(Text.Combine(jsonresp[documents]{0}[keyPhrases], \", \"))\nin  keyphrases<\/code><\/pre>\n\n\n\n
The JSon Body has no longer the hard coded en, is uses a new parameter language which is given on the function header. So you must edit the „invoke custom Function“-call with our new language column:<\/p>\n\n\n\n
\"\"
Now, we have our Key Phrases depending on the detected language and we can get the sentiment score also depending on the language with this code:
<\/figcaption><\/figure>\n\n\n\n
\/\/ Returns the sentiment score of the text, from 0.0 (least favorable) to 1.0 (most favorable)\n(text,lang as text) => let\n     apikey      = \"YOUR_API_KEY_HERE\",\n    endpoint    = \"https:\/\/<your-custom-subdomain>.cognitiveservices.azure.com\/text\/analytics\" & \"\/v2.1\/sentiment\",\n    jsontext    = Text.FromBinary(Json.FromValue(Text.Start(Text.Trim(text), 5000))),\n    jsonbody    = \"{ documents: [ { language: \"\"\" & lang & \"\"\", id: \"\"0\"\", text: \" & jsontext & \" } ] }\",\n    bytesbody   = Text.ToBinary(jsonbody),\n    headers     = [#\"Ocp-Apim-Subscription-Key\" = apikey],\n    bytesresp   = Web.Contents(endpoint, [Headers=headers, Content=bytesbody]),\n    jsonresp    = Json.Document(bytesresp),\n    sentiment   = jsonresp[documents]{0}[score]\nin  sentiment<\/code><\/pre>\n\n\n\n
That’s it, after we make another invoke function call, we get the sentiment score based on the given language. The order after that in the M Query Editor:<\/p>\n\n\n\n
\"\"<\/figure>\n","protected":false},"excerpt":{"rendered":"
According to the msdn article „How to integrate Text Analysis into Power BI„, I needed to detect the language of an comment and make a sentiment analysis of it. The reason to make it parametrized and not change the language … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0},"categories":[1],"tags":[],"_links":{"self":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/677"}],"collection":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=677"}],"version-history":[{"count":2,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/677\/revisions"}],"predecessor-version":[{"id":682,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/677\/revisions\/682"}],"wp:attachment":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=677"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=677"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=677"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}