Please ensure you have installed Power BI Desktop on your device so that you can follow along with the instructions and navigation provided in the chapter.

Follow this link to install Power BI Desktop: https://www.microsoft.com/en-us/download/details.aspx?id=58494.

Data preparation typically involves cleaning, transforming, and structuring data into a format that is suitable for analysis. Power BI offers several tools to help with this process, including the Power Query Editor, data modeling, and DAX formulas. In the later chapters of this book, you will dive deeper into each of these tools. Here is an example of the Power Query Editor window accessed from within the Power BI Desktop application:

Figure 1.1 – User interface (UI)/toolbar of the Power Query Editor

The Power Query Editor is a powerful tool that allows you to clean and transform data. It provides a user-friendly interface to perform various data transformation tasks, such as splitting columns, merging tables, filtering data, and removing duplicates. It also has several built-in functions to help you transform your data, such as date and text transformations.

Data modeling is another important aspect of Power BI...

As mentioned, Power Query is a powerful data transformation and preparation tool within Microsoft Power BI. It allows users to extract, transform, and load (more commonly known in the industry as ETL) data from various sources, enabling efficient data cleaning, shaping, and integration for analysis and reporting. In this chapter, we will delve into the details of Power Query, exploring its functionality, features, and UI.

Rather than a query language as such, Power Query is primarily accessed and used through the Power Query Editor UI. An example of this view is shown next:

Figure 1.3 – UI of the Power Query Editor

This UI is the hub for cleaning and preparing data within Power BI. It allows users such as yourself to connect to a wide range of data sources and apply transformations within the UI. As you begin to clean and prepare data, Power Query then tracks the steps of your cleaning process.

The actual language...

DAX is a formula and query language that plays a pivotal role in Power BI, helping users of Power BI to perform complex calculations and analysis on their data. It’s a language created by Microsoft for their suite of products and was first introduced in 2009 along with Power Pivot for Excel, something that was then also incorporated into Power BI. Helping to create and define custom calculations and formulas goes beyond the capabilities of traditional Excel functions.

Interestingly, it originated from the need to bridge the gap between relational database systems and traditional spreadsheet tools to help lower the barrier for professionals by providing a formula language that was more user-friendly for business analysts who may not be SQL experts, hence why DAX has been designed to work with tabular data models. Microsoft recognized the limitations of Excel at handling large sets of data and complex calculations, and this then led them to develop DAX, which...

As you progress through this book, you will learn how to use these technologies together to clean and prepare your data for performant data visualization. However, before diving into some examples and learning how to actually carry out these transformations, it’s important you pick up a few best practices on what you should consider before getting started.

Key elements to consider here are what is meant when we say data quality, why it is important (outside of the obvious reasons), who’s responsible for it, and how to plan for this data preparation.

In summary, Power BI provides several tools to help with cleaning and preparing your data. The Query Editor allows you to clean and transform data, data modeling helps you to organize your data, and DAX formulas allow you to create custom calculations and measures. By using these tools, you can ensure that your data is ready for analysis and that your reports provide accurate and meaningful insights.

In this chapter, we have shone a light on the aforementioned technologies and provided an example of how to structure your DAX expressions.

The following chapters will provide you with a deeper understanding of why you should cleanse data in Power BI and key considerations in this planning. This is crucial learning because it will help you later down the line when it comes to implementing changes and managing the who/why/where of the data being cleansed.

1. What percentage of time is generally spent on data cleaning and preparation in a data visualization project?
   1. 20-30%
   2. 50-80%
   3. 10-20%
   4. 80-100%
2. Name three tools provided by Power BI for data preparation.
   1. Power Extract, data integration, data expressions
   2. Power Query, data modeling, SQL queries
   3. Data analytics, Query Editor, data mining
   4. Power Query, data modeling, DAX formulas
3. What is the primary function of Power Query in Power BI?
   1. Creating visualizations
   2. Writing SQL queries
   3. Data transformation and preparation
   4. Building relationships between tables
4. What is DAX, and how is it used in Power BI?
   1. As a data visualization tool
   2. As a programming language
   3. As a formula language for creating calculations and measures
   4. As a data storage format
5. Why was DAX created, and what problem did it aim to solve?
   1. To create charts and graphs
   2. To bridge the gap between relational databases and spreadsheet tools
   3. To replace SQL queries
   4. To handle big data efficiently
6. Explain the dual role of DAX as a formula...