Artificial Intelligence with Power BI

Mary-Jo Diepeveen

• Publisher: Packt Publishing
• Year: 2022
• ISBN:
  9781801816250
• Edition: 1
• Topic: Business
• Subtopic: Business intelligence
• Index: Business

Learn how to create your own AI model and consume it in your Power BI reports to gain better insights from your dataKey Features• Learn how to gain better insights from your data by applying different AI techniques within Power BI• Save time by creating machine learning models independently and integrating them within your BI reports• Understand how to combine Cognitive Services and Azure Machine Learning together with Power BIBook DescriptionThe artificial intelligence (AI) capabilities in Power BI enable organizations to quickly and easily gain more intelligent insights from unstructured and structured data.This book will teach you how to make use of the many AI features available today in Power BI to quickly and easily enrich your data and gain better insights into patterns that can be found in your data.You'll begin by understanding the benefits of AI and how it can be used in Power BI. Next, you'll focus on exploring and preparing your data for building AI projects and then progress to using prominent AI features already available in Power BI, such as forecasting, anomaly detection, and Q&A. Later chapters will show you how to apply text analytics and computer vision within Power BI reports. This will help you create your own Q&A functionality in Power BI, which allows you to ask FAQs from another knowledge base and then integrate it with PowerApps. Toward the concluding chapters, you'll be able to create and deploy AutoML models trained in Azure ML and consume them in Power Query Editor. After your models have been trained, you'll work through principles such as privacy, fairness, and transparency to use AI responsibly.By the end of this book, you'll have learned when and how to enrich your data with AI using the out-of-the-box AI capabilities in Power BI.What you will learn• Apply techniques to mitigate bias and handle outliers in your data• Prepare time series data for forecasting in Power BI• Prepare and shape your data for anomaly detection• Use text analytics in Power Query Editor• Integrate QnA Maker with PowerApps and create an app• Train your own models and identify the best one with AutoML• Integrate an Azure ML workspace with Power BI and use endpoints to generate predictionsWho this book is forThis artificial intelligence BI book is for data analysts and BI developers who want to explore advanced analytics or artificial intelligence possibilities with their data. Prior knowledge of Power BI will help you get the most out of this book.

Part 1: AI Fundamentals

This part of the book will help you understand what AI can do and how it can be used in Power BI. It will show you how to make sure your data is prepared and that you understand which AI feature to use.

This section includes the following chapters:

• Chapter 1, Introducing AI in Power BI
• Chapter 2, Exploring Data in Power BI
• Chapter 3, Data Preparation

Chapter 1: Introducing AI in Power BI

Everyone wants to be working with data. Organizations are keen on relying more on data-driven decisions instead of intuition-driven decisions. To be driven by data, we need to extract insights from data. Thankfully, Power BI is a great tool to visualize and share what the data tells us. To better understand what trends and learning we can derive from data, we can use techniques from the field of data science.

Data science and artificial intelligence (AI) are becoming increasingly popular approaches to extracting insights from data. Among other things, this is because these tools allow us to work with unstructured data, which we couldn't work with before. And this helps us to more quickly find complicated trends and patterns in the data.

In this book, we will focus on using Microsoft Power BI as a data exploration and visualization tool. And we will take some parts of the Azure cloud to give us the power to train models and integrate this with Power BI.

But first, let's start with some of the groundwork. We need to understand what AI is, to properly scope our projects and run them successfully. We need to know what is possible and how we go from a simple dataset to an AI model before we get into the details of each step of the process. That is why we will first dive into the following questions:

• What do we expect from a data analyst?
• What is AI?
• Why should we use AI in Power BI?
• What are our options for AI in Power BI?

Let's start with covering the basics.

What do we expect from a data analyst?

Every company is looking for different insights and is working with different types and sets of data. Even though you will find data analysts across several organizations, their actual day-to-day work can greatly differ. When reading this book, you will pick up whatever is useful to you, and you will probably skip over irrelevant parts. Nevertheless, it is good to go over what we expect you to know and be familiar with.

First, we will discuss what it means to be a data analyst, the assumptions we make, and why we chose to use this job title. Next, we will go over what you should already know about Power BI and where you can find this information if you feel you need to brush up on that knowledge.

What is a data analyst?

You could call yourself a business intelligence engineer, a business intelligence specialist, a database administrator, or simply a data analyst. Whatever your job title is, you picked up this book because you work with Power BI and want to learn more about it. With all these different titles nowadays, it becomes challenging to understand what your base knowledge should be. For simplicity and consistency, we will refer to a person working with Power BI as a data analyst.

Why a data analyst? Because in this book, we will assume that you are familiar with working with data in Power BI and are able to do the following:

• Prepare data
• Model data (creating a data model in Power BI, not a machine learning model)
• Visualize data
• Analyze data
• Deploy and maintain Power BI deliverables

On the other hand, we'll assume you're unfamiliar with the field of data science. We'll approach all AI features in Power BI from the point of view of this persona. In this book, we'll introduce the machine learning models behind the AI features, to make sure enough is understood to use the features correctly. However, we won't go into the full complexities of all models, as this is not a book targeted at the data scientist, who already has a lot of knowledge about mathematical and statistical methods used in AI.

There are two main skills important for when you are venturing into AI in Power BI: connecting to and visualizing data. Let's elaborate a little bit more on these two topics so that you know what is expected of you before you continue.

Connecting to data

So, assuming we are all data analysts, let's look at our core tasks. The very first thing we need to do to work with data, is get access to data. From a technical perspective, we can very easily connect Power BI to various data sources, whether we have data stored in cloud databases, Azure or otherwise, or whether we have local files we want to connect to. Power BI will allow us to do so and will even allow us to schedule an automatic refresh to visualize new data as long as we set up a gateway connection between the network within which the data resides and the Power BI service.

What kind of data can you work with? Any kind! You can connect to structured data, formatted nicely in tables, semi-structured data, often in the form of JSON, or even unstructured data to insert images into your Power BI reports. This also means data can come from a variety of sources. You can collect Twitter data (semi-structured) which contains tweet text, date of creation, number of retweets, likes, and hashtags used. You can collect sales and marketing data to understand which products you have sold, when you sold them, and which ad campaigns you were running that may have had an effect on your sales. Or maybe you are looking at the supply and demand for your warehouses and stores to make sure you plan the logistics of stocking your stores accordingly.

Since data can be generated by so many different sources, and can come in so many different formats, we also want to think about how we extract that data and get it ready to build reports on. Power BI has a lot of standard connectors to allow you to connect to data. A best practice here, however, is that you have a pipeline handling data orchestration before you even connect Power BI to it. Such a process is often called an ETL (Extract-Transform-Load) or ELT (Extract-Load-Transform) pipeline in which we connect to our sources generating data, extract the data, load it into a database, and transform it if necessary. Although similar tasks can be done by Power BI, we prefer working with ETL tools such as Azure Data Factory to handle these kind of orchestration pipelines when we are working with large amounts of data.

ETL or ELT?

ETL is an established and commonly used approach to extract data from sources. The purpose is often to transform it and load it into a structured database such as an Azure SQL Database and fit the data nicely into columns and rows. This is very well suited for transactional data, where we want to get quick results. However, with the emergence of cloud, the newer approach, ETL, is getting more traction. By extracting and loading the data into a data storage that can store unstructured data, such as an Azure Storage Account or Azure Data Lake, we can dump data in the cloud without having to worry about schemas. This also makes it easier to reuse the same data and transform it in different ways, depending on the insights you want to extract.

Long story short, a lot can and probably should already happen to your data before you even open Power BI. Be aware of the process that may have occurred before data enters Power BI. Whatever source we use for Power BI will influence the options we have within Power BI, as well as the performance of the reports we create. In this book, we will work mostly with preprocessed data, available through files stored on public websites or cloud databases. For some projects with AI, however, we will need to process data before bringing it into Power BI to make sure we can work with it.

Visualizing data

Lastly, the reason we are using Power BI is because we want to tell a story with our data. We want to convert data into valuable and intuitive insights that everyone within our organization can read for their own use. In the context of AI, this may be one of the most important skills of a data analyst, as will be explained in later sections.

So, what do we mean by telling a story? Humans are much more susceptible to stories than listening to data. If we say there is a 70% chance it is going to rain, do you bring an umbrella? It's hard to decide. If someone tells you to bring an umbrella, you will probably do it. Even though we want to make more data-driven decisions, humans are not naturally driven by data. We are driven by stories, which we find more intuitive. That also means that we cannot just give AI insights to humans. We need to translate the output of an AI model to make sure it is understandable for people. That means we need to use the data to tell stories.

The way we can do it within Power BI is by making use of the visuals it offers. We have the option to use standard visuals, import visuals from the marketplace, or create our own visuals using Python or R. Understanding when to use what and how to combine different visuals into a report is an important skill of a data analyst, and a skill we assume you have when reading this book. Throughout the book, remember that to gain people's trust, we need to speak their language and not just throw numbers at them to make them change their behavior.

In this book, we will focus on how we can use Power BI to perform AI. That means that you, as a data analyst, are already familiar with the different types of data you can get into Power BI from the differ...