Data Science and Analytics Blog
WHAT IS THE DIFFERENCE BETWEEN SUM() AND SUMX()? What is the difference between SUM() and SUMX()? When I first started using Power BI and was learning DAX, I always asked myself when to use SUM() and SUMX(). This is explained in an easy language. SUM() …
How to pivot text data? If you are curious to know how to pivot text data through Power Query in Power BI, have a look at the great post written by Matt Allington. Pivoting text data more blog posts How to find the hero in …
There are several ways in Power BI Desktop to group text or numbers. If you are curious how you can make use of the grouping feature without writing any DAX, you should definitely read this.
Grouping without DAX
HOW TO FIND THE HERO IN YOUR DATA STORY?DIFFERENT TECHNIQUES TO TELL YOUR DATA STORYVast amounts of data are captured daily in every organization. To use data to change the direction of your business strategy or make the right decisions,…
WHAT IS THE DIFFERENCE BETWEEN SUM() AND SUMX()? What is the difference between SUM() and SUMX()? When I first started using Power BI and was learning DAX, I always asked myself when to use SUM() and SUMX(). This is explained…
How to pivot text data? If you are curious to know how to pivot text data through Power Query in Power BI, have a look at the great post written by Matt Allington. Pivoting text data more blog posts How…
How to group without DAX? There are several ways in Power BI Desktop to group text or numbers. If you are curious how you can make use of the grouping feature without writing any DAX, you should definitely read this.…
How to make the right decisions and let experience not mislead you How can we trust our past experiences in order to predict the future and make decisions? Experience can also fool us instead of being a reliable teacher. When we want…
How to connect Power BI with the Cognitive Services and Text Analytics API? You can visualize and transform your data with Microsoft Power BI Desktop. In order to analyze natural language processing we will make use of the Text…
How to make the right decisions and let experience not mislead you How can we trust our past experiences in order to predict the future and make decisions? Experience can also fool us instead of being a reliable teacher. When we want to …
How to connect Power BI with the Cognitive Services and Text Analytics API?
You can visualize and transform your data with Microsoft Power BI Desktop.
In order to analyze natural language processing we will make use of the Text Analytics service which is part of Microsoft Azure Cognitive Services. Having an unstructured text, you can analyze sentiments by extracting the most prominent key phrases.
1. First step is to load the data
In order to load the data, go to Get data drop-down menu and select load CSV/ Text file.
Open the CSV file in the folder where you downloaded it to import the CSV file into Power BI.
Next you click on load since Power BI Desktop has detected the header rows, delimiter and column types.
2. Now you have to prepare the data.
In order to make use of the Key Phrases API of the Text Analytics service, transformation of the data may be required.
The CSV file has a comment and a subject column. We will merge both these columns in order to extract key phrases from both columns.
Therefore, you have to click on edit queries where you select both columns in the table by holding the Control key button.
In the text columns of the transform ribbon you will see the merge columns ribbon. Choose the following separator as below.
3. Get an understanding for the API.
4. Now you can build up a custom function.
What happens when creating the custom function is that data is converted from the JSON format and a HTTP request is made to the Key Phrases API which delivers a string with a comma-separated list of the extracted key phrases. Custom functions are written in Power Query M language that is based on F#.
You have to be in the Power Query Editor window where you select Blank Query. Query1 will show up which you have to name KeyPhrases. Go to the Advanced Editor where you insert the following code as stated below:
Insert your API key with your Text Analytics access key. This key can be found by logging in to the Azure portal, selecting your Text Analytics subscription. Do not forget the quotation marks before and after the key.
5. Make use of the custom function.
Now click on Invoke Custom Function to extract the key phrases from the customer comments and store it in a new column which you call keyphrases and store it in the custom function that you created.
What happens here is that Power BI calls your custom function once for each row in the table. Queries are sent to the Key Phrases API and a new column with the stored results show up.
Authentication and privacy settings are asked before this can take place. Choose Anonymous due to the fact that the Text Analytics service authenticates you using your access key, so credentials for the HTTP request itself are not needed. Click on Connect.
Next choose Public and save.
Now you can create a word cloud instead of using the full text of every comment. As a result we will see the most important words from the customer comments and not only the most common words.
HOW TO FIND THE HERO IN YOUR DATA STORY?DIFFERENT TECHNIQUES TO TELL YOUR DATA STORYVast amounts of data are captured daily in every organization. To use data to change the direction of your business strategy or make the right decisions,…
WHAT IS THE DIFFERENCE BETWEEN SUM() AND SUMX()? What is the difference between SUM() and SUMX()? When I first started using Power BI and was learning DAX, I always asked myself when to use SUM() and SUMX(). This is explained…
How to pivot text data? If you are curious to know how to pivot text data through Power Query in Power BI, have a look at the great post written by Matt Allington. Pivoting text data more blog posts How…
How to group without DAX? There are several ways in Power BI Desktop to group text or numbers. If you are curious how you can make use of the grouping feature without writing any DAX, you should definitely read this.…
How to make the right decisions and let experience not mislead you How can we trust our past experiences in order to predict the future and make decisions? Experience can also fool us instead of being a reliable teacher. When we want…
How to connect Power BI with the Cognitive Services and Text Analytics API? You can visualize and transform your data with Microsoft Power BI Desktop. In order to analyze natural language processing we will make use of the Text…
