Master Your Data with Excel and Power BI

Leveraging Power Query to Get & Transform Your Task Flow

Miguel Escobar, Ken Puls

• Publisher: Holy Macro! Books
• Year: 2021
• ISBN:
  9781615473588
• Edition: 2
• Topic: Business
• Subtopic: Business Intelligence
• Index: Business

Power Query is the amazing new data cleansing tool in both Excel and Power BI Desktop. Do you find yourself performing the same data cleansing steps day after day or month after month? Power Query will make it faster to clean your data the first time and a single click to clean your data every time after that. While Power Query is powerful, the interface is subtle - there are tools hiding in plain sight that are easy to miss. Go beyond the obvious and take Power Query to new levels with this book.

Chapter 1 - Power Query Fundamentals

The purpose of Power Query is to collect and reshape data into the desired format, before loading it into tables for consumption by the business analyst. The basic overview of the process, which Power Query will attempt to follow without your intervention, can be visualized as follows:

An overview of the Power Query process

Of course, we can manipulate any part of this process at any time. And indeed, in this book we will do a lot of that. But to begin with it is helpful to walk through and understand the overall method that Power Query is attempting to follow.

Before You Begin

Before you launch into your Power Query journey, there are some defaults that we suggest you change in the Power Query interface. Why? Microsoft turned certain features off to avoid overwhelming you, but unfortunately some of these items are critical in order to use the tool properly. And since you have this book, you’ll be guided through the correct use here anyway!

Adjusting Excel’s Default Power Query Properties

To adjust your default settings in Excel:

• Go the Data tab à Get Data à Query Options
• Under Global à Data Load, ensure that Fast Data Load is checked. (This setting will lock Excel’s user interface during a refresh but will ensure that you have up to date data before continuing.)
• Under Global à Power Query Editor, ensure that every box here is checked. We especially want to make sure the Formula Bar is showing but checking every box will make sure you have all the options that you’ll see throughout this book.
• Click OK

There are other options within this area, but for now the default settings will work just fine.

Adjusting Power BI’s Default Power Query Properties

To adjust your default settings in Power BI Desktop:

• Go the File tab à Options & settings à Options
• Under Global à Power Query Editor, ensure that every box here is checked. We especially want to make sure the Formula Bar is showing but checking every box will make sure you have all the options that you’ll see throughout this book.
• Click OK

🍌 While in the Power BI Desktop options, you may also want to check the Global à Preview Features tab to see if any new features look enticing. As features are released to Power BI Desktop first, this is a great place to see what is coming to Power Query in Excel.

Extract

In this chapter, we will look at importing a simple CSV file into Power Query in Excel or Power BI to show just how Power Query approaches the tasks above, how they look in the user interface, and how they are identical between the two programs.

The ETL process all begins with the Extract step. Inside this step are four distinct subtasks as follows:

The four sub-steps of the Extract process

Configure Connector Settings (Choose Your Data)

The first step is to choose and configure the data connector we want to work with. In this case, we’ll start by creating a new query that uses the CSV connector in Excel:

• Go to Get Data à From File à From Text/CSV

This would be equivalent to the following in Power BI Desktop:

• Go to Get Data à More… à File à Text/CSV

Connecting to a Text/CSV file in Excel (left) or Power BI Desktop (right)

It is worth recognizing that you could attach to a Text/CSV file in less clicks in either program. As Text/CSV files are a common data source, they surface in the user interface much more quickly than drilling down into the sub-menus. In Excel you’ll find this connector right beside the Get Data button on the Data tab. And in Power BI the connector is on the very first level of the menu, with no need to drill down into the More… sources. This, however, won’t always be the case as we move into other data sources later in the book, so we’ve standardized on the full path to the data source.

🍌 Power BI Desktop can actually connect to a larger set of data sources than Excel. The intention of the team here is to release beta connectors into Power BI and – once they have passed beta stage – eventually bring them to Excel.

Once we’ve selec...