Exploring artificial intelligence insights 143
You can see how, with a few easy steps, you can manipulate a data structure that would be difficult to manage with other tools. This means that end users can push themselves to do advanced data preparation without the need to request changes at data source level.
The logic on JSON parsing applies to the XML structure as well.
Exploring artificial intelligence insights
Power Query allows us to enrich data with a data engineering approach. It also provides us with artificial intelligence (AI) tools to access cognitive services, which are pre-trained machine learning (ML)models provided by Microsoft, usually used by data scientists and app developers to apply cognitive capabilities to reading and interpreting data.
In this recipe, you will explore how to leverage these features in order to run, with a no-code approach, consistent text analysis, thanks to the use of cognitive services.
Getting ready
For this recipe, you need to have Power BI Desktop running on your machine. You need to download the following file in a local folder:
• IMDB-Dataset CSV file with movie reviews data
In this example, we will refer to the C:\Data folder.
In order to access cognitive services resources, you need to have a running Power BI Premium capacity.
144 Reshaping Your Data
How to do it
Once you open your Power BI Desktop application, you are ready to perform the following steps:
1.Click on Get Data and select the Text/CSV connector.
2.Browse to your local folder where you downloaded the IMDB-Dataset CSV file and open it. The following window with a preview of the data will pop up. Click on
Transform Data:
Figure 4.54 – CSV data preview
Exploring artificial intelligence insights 145
3.Browse to the Home tab and click on Use First Rows as Headers. Select the first column, review, where you can find movie reviews, browse to the Add Column tab, and click on Text Analytics in order to see the following window displayed:
Figure 4.55 – Detect language
Now, click on Detect language, select the review column and click on OK. This way, you will be applying the text analytics function to detect the language type of the selected column.
4.You will see two new columns, Detect language.Detected Language Name and Detect language.Detected Language ISO Code, with information about the language detected:
Figure 4.56 – Detect language output
146Reshaping Your Data
5.Click again on Text Analytics and click now on Extract key phrases:
Figure 4.57 – Extract key phrases
6. You can see the following two columns with key words for every single review:
Figure 4.58 – Extract key phrases output
Exploring artificial intelligence insights 147
As well as text analytics services, you can also do analysis on images using vision services. If you have a column with links redirecting to images, you can enrich that content by applying image tags.
Moreover, you can recall custom machine learning models developed on Azure Machine Learning by data scientists. The idea is to have Power Query features that allow business users and analysts to collaborate with more technical users, such as data engineers and data scientists.
Artificial intelligence insights can be seen as the entry point to recall pre-calculated artificial intelligence services such as cognitive services and custom ML models developed on Azure Machine Learning.
5
Combining Queries for Efficiency
Business analysts need to perform complex transformations that usually involve a combination of multiple queries. They often need to join data horizontally or to append tables.
With different ways of combining data, you can transform and model tables in order to optimize the information included. By leveraging different methods, you can perform merge and join transformations in order to enrich data or append and combine queries to scale and increase data volume automatically. The main aim is to create queries with relevant data that can serve different purposes, such as reporting, loading to Dataverse through Power Apps, or loading to Azure Data Lake to make this data available for other applications.
In this chapter, you will explore the following combining options within Power Query:
•Merging queries
•Joining methods
•Appending queries
•Combining multiple files
•Using the Query Dependencies view