M Is for (Data) Monkey: A Guide to the M Language in Excel Power Query - Softcover

Puls, Ken; Escobar, Miguel

Softcover

ISBN 10: 1615470344 ISBN 13: 9781615470341

Verlag: Holy Macro! Books, 2015

Inhaltsangabe

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.

Über diesen Titel

Über die Autorin bzw. den Autor

CA

Auszug. © Genehmigter Nachdruck. Alle Rechte vorbehalten.

M is for (Data) Monkey

The Excel Pro's Definitive Guide to Power Query

By Ken Puls, Miguel Escobar

Holy Macro! Books

Copyright © 2016 Tickling Keys, Inc.
All rights reserved.

ISBN: 978-1-61547-034-1

Contents

Foreword How Power Query Changed Our Lives, v, 
Chapter 0 Introduction: A New Revolution, 1, 
Chapter 1 Importing Basic Data, 11, 
Chapter 2 Overriding Power Query Defaults, 19, 
Chapter 3 Basic Append Operations, 27, 
Chapter 4 Importing All Files in a Folder, 33, 
Chapter 5 Aggregating Excel Worksheets, 43, 
Chapter 6 Unpivoting Data, 51, 
Chapter 7 Importing Nondelimited Text Files, 57, 
Chapter 8 Importing from Databases, 65, 
Chapter 9 Merging Tables and Queries, 79, 
Chapter 10 Query Loading Destinations, 87, 
Chapter 11 Defining Data Types, 97, 
Chapter 12 Importing Web Data, 103, 
Chapter 13 Loading Data from Exchange, 111, 
Chapter 14 Grouping and Summarizing, 117, 
Chapter 15 Transposing and Unpivoting Complex Data, 123, 
Chapter 16 Automating Refreshing, 133, 
Chapter 17 Power Query Formulas, 139, 
Chapter 18 Conditional Logic in Power Query, 153, 
Chapter 19 Power Query Objects, 157, 
Chapter 20 Understanding the M Language, 173, 
Chapter 21 Creating Custom Functions, 189, 
Chapter 22 Advanced Conditional Logic, 197, 
Chapter 23 Dynamic Parameter Tables, 209, 
Chapter 24 Dynamic Calendar Tables, 215, 
Chapter 25 Query Organization, 221, 
Index, 227, 

CHAPTER 1

Introduction: A New Revolution

Whether we are performing basic data entry, building simple reports, or designing full-blown business intelligence solutions using VBA, SQL, and other languages, we Excel pros all deal with data to a certain extent. Our skill sets vary greatly, but the overall jobs we are usually trying to perform include:

• Transforming data to meet our needs

• Appending one data set to another

• Merging multiple data sets together

• Enriching our data for better analysis

We may get tagged with the name "data monkey," but we are actually information workers. But no matter what we call ourselves in our formal job descriptions, our role is to clean up data and turn it into information. Our jobs may not be glorious, but they are essential, and without our work done correctly, the end results of any analysis are suspect.

While Excel has an amazing toolset to help us build business intelligence out of data, converting raw data into consumable data has been a challenge for years. In fact, it's this issue that we often spend most of our time on — prepping data for analysis and getting it into a nice tabular format to expose Excel's most powerful analytical and reporting tools.

Despite the moniker "data monkey," we information workers are often more like data magicians. Our data seldom enters our world in a ready-to-consume format; it can take hours of cleaning, filtering, and reshaping to get things ready to go.

Once our data is prepared and ready, we can perform a vast array of powerful analytical processes with ease. Tools including conditional formatting, filters, pivot tables, charts, slicers, and more let us work magic and impress our audience.

But getting the data prepped and ready is the hard part. We're served dirty data, held in collections of text and Excel files (maybe a database, if we're very lucky), and we somehow have to clean it up and get it ready to use. Our end goal is simple: Get the data into an Excel table as quickly as possible, while making sure it is scoped to our needs and accurate. And every solution needs a different combination of data coming from different sources ... which takes magic.

The Benefits and Dangers of Black Magic

The true wizards of Excel use many different techniques to make their magic happen — sometimes on their own and sometimes in combination. These types of magic include:

• Excel formulas — These are some of the first techniques that the magician will often reach to, leveraging their knowledge of formulas using functions such as VLOOKUP(), INDEX(), MATCH(), OFFSET(), LEFT(), LEN(), TRIM(), CLEAN(), and many more. While formulas tend to be used by most Excel users, the complexity of these formulas varies by the user's experience and comfort.

• Visual Basic for Applications (VBA) — This powerful language can help you create powerful and dynamic transformations for your data. VBA techniques tend to be used by advanced users due to the discipline required to truly master them.

• SQL statements — SQL is another powerful language for manipulating data, and it can be extremely useful for selecting, sorting, grouping, and transforming data. The reality, however, is that this language is also typically only used by advanced users, and even many Excel pros don't know where to get started with it. This language is often thought of as being the sole domain of database professionals, although every Excel pro should invest some time in learning it.

All these tools have something in common: For many years, they were essentially the only tools available for cleaning and transforming data into something useful. Despite their usefulness, many of these tools also have two serious weaknesses: They require time to build a solution and time to master the techniques. While it's true that truly savvy magicians can use these tools to build solutions to automate and import raw data in a clean format, this takes years of learning advanced languages as well as a significant amount of time scoping, developing, testing, and maintaining the solutions. Depending on the complexity of the solutions built, fixing the solutions for a minor change in the import format or extending them to embrace another source could be horrendous.

One hidden danger of having a true wizard in a company is that the person may build an incredible solution that works until long after he or she has left the company. At some point, though, others at the company realize that they don't understand the solution and don't have anyone to fix it when it eventually breaks.

On the flip side, many people tasked with this data cleanup didn't have time or opportunity to learn these advanced magic techniques. And while we could say that maybe they're better off never having a massive system collapse without anyone to fix it, instead they waste hours, days, weeks, months, and years of labor time and money performing repetitive data cleanup and imports on a regular basis.

Take a moment and think about how many hours are consumed on a monthly basis in your company simply performing repetitive data import and cleanup tasks in Excel. Multiply those hours by the average wage rate in your company ... and by the number of companies in your industry worldwide and ... you get the idea. The cost of productivity in this area is staggering.

Enter a product that tackles all these problems — one that is easy to learn and that others can pick up and understand with limited instruction. It's a product that lets you automate the import and cleanup of data, so you can focus on turning that data into information, adding true value to your company. That product is called Power Query.

The Future Transforms

Power Query solves the problems related to the toolsets just described. It is very easy to learn and has one of the most intuitive user interfaces we've ever worked with. It's easy to maintain, as it shows each step of the process, which you can review or update later. And everything done in Power Query can be refreshed with a couple of clicks.

We have spent years building solutions using black magic techniques, and we see Power Query as a game changer for many reasons. One of those is the speed with which it can be learned.

When it comes to importing, cleaning, and transforming data to get it ready for analysis, you can learn Power Query faster than you can learn Excel formulas, and it handles complex sources much more easily than VBA.

Its ease of use makes Power Query the answer to the vanishing data magician problem that many businesses face. Even if a modern-day magician builds something complex in Power Query, you can have someone up to speed and able to maintain or fix the query with minimal training — we're talking hours, not weeks.

As hard as it is for true Excel pros to understand, many users actually don't want to master Excel formulas. They simply want to open up a tool, connect it to their data source, click a few buttons to clean it up and import it, and build the chart or report they need. It's for exactly this reason that Power Query can reach even further than formulas. With the menu-driven interface, in many cases a user can avoid ever having to learn a single formula or line of code.

There is no doubt in our minds that Power Query will change the way Excel pros work with data forever.

We want to make it quite clear that we are not discounting the value of formulas, VBA, or SQL. In fact, we couldn't live without those tools. You can quickly knock out formulas to do many things outside the transformation context that Power Query will never do. VBA has a far greater reach in sheer capability and power, allowing you to reach to other applications, create programs to pull and push data, and so many other things. And a SQL query written by a SQL wizard will always be faster and better than one created by Power Query.

In the context of simply connecting to, cleaning, and importing data, however, Power Query offers more for less, allowing you to automate the job more quickly and in less time.

The good news for true wizards of data is that Power Query is yet another tool that you have access to. You can provide your own SQL queries if needed, refresh them with VBA when desired, load your Power Query–created queries directly to Power Pivot, and much more.

Why Power Query Is Magic

The number-one issue Excel pros face when building robust and stable solutions has been accessing, cleaning, and transforming the data. What we've needed, and yet many of us have never heard of, is an ETL tool — that is, a tool for extracting, transforming, and loading data.

Power Query is an ETL tool; its function is to extract data from almost any source, transform it as desired, and then load it. But what does that truly mean to us as Excel pros?

Extract

Extraction can be targeted against one or more data sources, including text files, CSV files, Excel files, databases, and web pages. In addition, the Power Query team has built many connectors to data sources that have otherwise been tough to get at — Microsoft Exchange, Facebook, Salesforce, and other Software-as-a-Service (SaaS) sources.

Transform

When we talk about transformation, we include each of the following areas:

• Data cleaning — Data cleaning could involve filtering out departments from a database or removing blank or garbage rows from a text file import. Other uses include changing cases from uppercase to lowercase, splitting data into multiple columns, and forcing dates to import in the correct format for a particular country. Data cleaning is anything you need to do to your data to clean it up so it can be used.

• Data integration — If you use VLOOKUP() or INDEX()/MATCH(), then you're probably integrating multiple data sets. Power Query can join data in either vertical or horizontal fashion, allowing you to append two tables (creating one long table) or merge tables together horizontally, without having to write a single VLOOKUP() function. You can also perform other operations, such as grouping.

• Data enrichment — These tasks include adding new columns or doing calculations over a data set. Power Query makes it easy to perform mathematical calculations like creating Gross Sales by multiplying Sales Quantity * Sales Price or add new formats of dates based on your transaction date column. In fact, with Power Query you can even create entire tables dynamically, based on the value in an Excel cell. Need a dynamic calendar table that runs five years back from today's date? Look no further than Power Query.

Power Query allows you to perform many transformations through menu commands rather than having to write formulas or code to do them. This tool was built for Excel pros, and with no coding experience whatsoever, you can use Power Query to perform transformations that would be incredibly complex in SQL or VBA. That's a great thing!

If you're the type of person who likes to get under the covers and tinker with formulas or code, however, you can. While there is no requirement to ever learn it, Power Query records everything in a language called M. (Languages A through L were taken.) And if you're a wizard who decides to take advantage of this language, you can build even more efficient queries and do even more amazing things than without it.

Load

With Power Query you can load data into one of four places:

• Excel tables

• The Power Pivot Data Model

• Power BI

• Connections only

The last point might seem a bit mysterious, but it simply means that you can create a query that can be used by other queries. This allows for some very interesting use cases that we'll explore more fully in the book.

While it's interesting to look at where the data loads, that really isn't the important part of the loading process in this ETL tool. It's how it loads or rather how to load it again.

Power Query is essentially a macro recorder that keeps track of every bit of the extract and transform steps. You can define a query once and determine where you'd like to load it. After you've done that, you can simply refresh your query to run it again.

Consider this for a moment: You need a particular TXT file, and it takes you 20 minutes to import and clean it before you can use it. Power Query enables you to accomplish the same task in 10 minutes, which saves you 10 minutes the first time you use it. Then next month comes along, and you need the new version of the same TXT file. Without Power Query, you have to roll up your sleeves and relive the 20 minutes of Excel exuberance where you show Excel that you're a master at reliving the past, performing those exhilarating steps over and over again each month. Wait ... you don't find that exhilarating? In that case, just save your new TXT file over the old one, go into Excel, and click Data -> Refresh All. You're finished. Seriously.

This is where you see the real power of Power Query. It's easy to use, and it's also easy to reuse. It changes your hard work into an investment and frees up your time during the next cycle to do something worthwhile.

Power Query Versions

Before we tell you where to get Power Query, let's talk about the updates. Yes, that may seem like putting the cart before the horse, but there is a pretty solid reason for this.

The Update Cycle

The Power Query team releases monthly updates. We're not talking bug fixes (although those are certainly included); we're talking new features and performance enhancements. While some are small, others are much larger. In February 2014 the team added the ability to connect to Microsoft Exchange as a data source. In early 2015 the team released an update that cut query load time by 30%. In July 2015 the team released an update that solved some very serious issues with refreshing to Power Pivot.

Are there risks involved in installing the latest updates as soon as they become available? Sure there are. Bugs happen, particularly in complex software. But the reality is that the Power Query team works very hard to address serious bugs in the software. If you're particularly concerned, download the installer and save it rather than installing directly from the web. This will allow you to roll back if the need ever arises.

If you currently have Power Query installed, make sure you update it. This book was written using version 2.24, released in July 2015, and you should be on at least this update.

Where Do I Get Power Query?

The answer depends on the version of Excel that you have:

• Excel 2010 and Excel 2013 — Download it from http://go.microsoft.com/fwlink/?LinkId=317450.

• Excel 2016 — You already have Power Query installed, but the entry point is a bit different than in the Excel 2010 and Excel 2013 versions.

• Power BI Desktop — Wait, what? What does this have to do with Excel? A little and a lot, really. The short story is that Power BI Desktop is a standalone program for sourcing and modeling your data. As it happens, Power Query is the tool used to source and transform the data with Power BI Desktop, so you're going to be learning a skill in this book that is portable to other applications. The Power BI Desktop can be downloaded from www.powerbi.com.

(Continues...)

Excerpted from M is for (Data) Monkey by Ken Puls, Miguel Escobar. Copyright © 2016 Tickling Keys, Inc.. Excerpted by permission of Holy Macro! Books.
All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.

Details zur Ausgabe

• Verlag: Holy Macro! Books
• Erscheinungsdatum: 2015
• ISBN 10: 1615470344
• ISBN 13: 9781615470341
• Einband: Tapa blanda
• Sprache: Englisch
• Anzahl der Seiten: 212
• Kontakt zum Hersteller: Nicht verfügbar