Chapter 4: Connecting to and Transforming Data

So far, we have learned about the basics of the Power BI interface. However, to truly unlock the power of Power BI, we need to expand our data model. To do that, in this chapter, we will learn about the Power Query Editor and how to relate multiple tables of data to one another to create a more complex data model. Every good visual report starts with a good data model, so we must learn how to properly ingest, transform, and load our data into Power BI.

The following topics will be covered in this chapter:

• Getting data
• Transforming data
• Merging, copying, and appending queries
• Verifying and loading data

Technical requirements

You will need the following to follow the instructions in this chapter:

• An internet connection.
• Microsoft Power BI Desktop.
• Download LearnPowerBI_CH4Start.pbix and the Budget and Forecast.xlsx, People and Tasks.xlsx, and Hours.xlsx files from GitHub at https://github.com/PacktPublishing/Learn-Power-BI-second-edition/tree/main/Chapter04.
• Check out the following video to see the Code in Action: https://bit.ly/3bVTQwc

Getting data

Power BI is all about working with and visualizing data. Thus, we must incorporate some additional data into our model. In Chapter 2, Planning Projects with Power BI, we covered the data and data sources that Pam required and would be using. To emulate this data, we will use Excel files, so ensure that you have the Excel files from this chapter's Technical requirements section downloaded and that your LearnPowerBI.pbix file is open in Power BI Desktop.

In this section, you will create your first query and then add some additional data to your data model.

Creating your first query

To determine whether or not Personal Time Off (PTO) should be approved, it is important to understand where the company is concerning budgets and forecasts. Whether or not the company, department, and/or location is on target in terms of its budget can be an important consideration when approving or denying time off.

Unlike the calendar table from Chapter 3, Up and Running with...

Transforming data

While Power BI did a good job of automatically identifying and categorizing our data, the data is not entirely in the format required for analysis. Therefore, we need to modify how the data gets loaded into the model. In other words, we need to transform the data. To do that, we will cover using a powerful sub-application known as Power Query Editor.

Touring the Power Query Editor

Similar to how we provided a tour of Power BI Desktop in Chapter 3, Up and Running with Power BI Desktop, this section provides a tour of Power Query Editor. Power Query Editor can be launched from the Home tab by choosing Transform data in the Queries section of the Ribbon. Once launched, the following screen will be displayed:

Figure 4.6 – Power Query Editor

As you might expect, the Power Query Editor interface is similar to, and shares common elements with, Power BI Desktop. The Power Query Editor user interface is comprised of eight main areas. Refer...

Merging, copying, and appending queries

Now that we have cleaned up our data, we still require some additional data and transformations to occur to prepare our data for analysis. Specifically, we need to add our additional month data around hours billed to customers, as well as some additional transformations.

In the following sections, we will be performing more advanced transformations of our data, including merging queries, expanding tables, and appending queries.

Merging queries

Note that in the January query, there is a column called TaskID. The values in this column match the values in the TaskID column from our Tasks query. This Tasks query has additional information about each of these tasks regarding whether or not the task is Billable, or some other category, such as PTO, Int Admin, Sales Support, and Training. It would be good to have this information included in our January table. To accomplish this, we will merge the two queries by performing the following steps...

Verifying and loading data

Now that we are finished connecting to and transforming the data, there should be three active queries and four intermediate queries listed in the Queries pane.

The active queries include Budgets and Forecast, People, and Hours. These should not be italicized. These are the active queries that will create tables in the data model. There should also be four intermediate queries for Tasks, January, February, and March that are italicized. These queries will not create tables in the data model but will be used by the active queries.

The Queries pane should look as follows:

Figure 4.19 – Queries pane

We can view how our sources and queries are related to one another by viewing the query dependencies. We can do this by performing the following steps:

1. In Power Query Editor, click on the View tab of the Ribbon.
2. Click the Query Dependencies button in the Dependencies area of the Ribbon. This displays the Query Dependencies...

Summary

In this chapter, we explored the Power Query Editor, the powerful sub-application that's used to ingest and shape data through the creation of queries. Queries are a series of recorded steps for connecting to and transforming data. We connected to multiple data files and learned how to clean up and transform the data to support further analysis. Next, we learned about more advanced operations such as how to merge, copy, and append queries. Finally, we explored some built-in data quality and profiling tools that summarize, visualize, and provide statistical information about the data we are ingesting.

In the next chapter, we will build a data model by connecting the tables created by these queries to one another via relationships. We will also build the necessary calculations that will complete our model.

Questions

As an activity, try to answer the following questions on your own:

• How many different connectors are available for ingesting data in Power BI?
• What is the powerful sub-application included with Power BI that's used for ingesting and shaping data?
• What is the name of the language that's used behind the scenes when creating queries?
• Turning columns into rows is called what?
• What icons are displayed in the headers of columns for text, whole number, decimal, and date columns?
• Joining two queries together based on columns is called what?
• What are the six different kinds of join operations that can be performed when joining queries?
• Adding one query to another query is called what?

Further reading

For more information on what was covered in this chapter, take a look at the following resources:

• Data sources in Power BI Desktop: https://docs.microsoft.com/en-us/power-bi/desktop-data-sources
• Connect to Excel in Power BI Desktop: https://docs.microsoft.com/en-us/power-bi/desktop-connect-excel
• Query overview in Power BI Desktop: https://docs.microsoft.com/en-us/power-bi/desktop-query-overview
• Perform common query tasks in Power BI Desktop: https://docs.microsoft.com/en-us/power-bi/desktop-common-query-tasks