M Is for (Data) Monkey

A Guide to the M Language in Excel Power Query

Ken Puls, Miguel Escobar

• Publisher: Holy Macro! Books
• Year: 2015
• ISBN:
  9781615473458
• Edition: 1
• Topic: Informatik
• Subtopic: Microsoft-Programmierung

Power Query is one component of the Power BI (Business Intelligence) product from Microsoft, and "M" is the name of the programming language created by it. As more business intelligence pros begin using Power Pivot, they find that they do not have the Excel skills to clean the data in Excel; Power Query solves this problem. This book shows how to use the Power Query tool to get difficult data sets into both Excel and Power Pivot, and is solely devoted to Power Query dashboarding and reporting.

Chapter 1 Importing Basic Data

If you’re an Excel pro, it’s highly likely that your life is all about importing, manipulating, and transforming data so that you can use it. Sadly, many of us don’t have access to big databases with curated data. Instead, we are fed a steady diet of TXT or CSV files and have to go through the process of importing them into our Excel solutions before we can start our analysis. For us, critical business information is stored in the following formats:

• TXT files, which are delimited by characters
• CSV files, which are delimited by commas
• Excel worksheets

Fortunately, Power Query was built for us, and it allows us to import our data from any of these sources.

Importing Delimited Files

The process of importing a delimited file such as a .CSV or tab-delimited .TXT file is fairly straightforward, and follows the basic ETL (extract, transform, and load) process, as described in the following sections.

Extract (from the File)

The download package for this chapter contains two delimited files, either of which will work for this example. Both are named Ch01-Delimited, though one is a comma-delimited CSV file and the other is a tab-delimited TXT file. To import either delimited file using Power Query you can:

• Open a new (blank) workbook
• Create a new query → From File → From CSV (or From Text if you used the TXT file)
• Browse to the Ch01 Examples\Ch01-Delimited file and double-click it

Note: In different versions of Excel, you use slightly different methods for creating a new query. To learn how to create a new query in your version of Excel, see the section “Creating New Queries” in the Introduction.

Excel launches a new window, the Power Query editor, which looks like this:

Figure 11 The Power Query editor with an imported delimited file.

Transform (into the Desired Output)

This Power Query editor view above shows some important pieces of information that are worth taking notice of:

• The data appears in a nice tabular format with column headers
• The query has been automatically given a name (the name of the file)
• There are three steps listed in the Applied Steps box

It’s the last point that is the most salient here. If you try clicking the Source step, you see a slightly different view of your data:

Figure 12 The data as Power Query originally imported it, shown by clicking the Source step.

The impact of this is fairly important. Power Query imported your data, analyzed it, and noticed some trends. What you don’t see is that Power Query determined that your data is columnar (based on the fact that CSV files have their columns separated by commas), so it (correctly) split it into columns. That data landed in the Source step.

Power Query then analyzed your data further and identified that the first row had text headers that were inconsistent with the values in the columns. It therefore added the step Promoted Headers to promote the first row to be the column headers for your table.

Next, Power Query attempted to identify and set the data types in the columns. It made the assumption that the TranDate column is dates, the Account and Dept columns are numbers, and the Sum of Amount column is values. It then applied those data types in the Changed Type step you see in the Applied Steps box.

The great thing here is that you can step backward and forward through these steps to see what Power Query did to the data.

You can make some modifications to clean up the data . . . like fixing the terms in the column headers to be more...