Master Your Data with Power Query in Excel and Power BI E-Book

MrExcel's Holy Macro! Books

• Herausgeber: Packt Publishing
• Kategorie: Lebensstil
• Sprache: Englisch

Seitenzahl: 678

Veröffentlichungsjahr: 2024

Beschreibung

This book equips you with the essential skills to master Power Query in Excel and Power BI. Starting with the basics, you'll learn query management, data types, and error handling, establishing a solid foundation. You'll explore techniques to move queries between Excel and Power BI, ensuring seamless workflow integration. As the guide progresses, you'll delve into data import methods from flat files, Excel, web-based, and relational sources, while performing key transformations like appending, combining, and reshaping data.
Advanced topics such as conditional logic, Power Query values, and M Language fundamentals will enhance your ability to customize and optimize queries. The book also covers the creation of parameters and custom functions, alongside applying sophisticated date and time techniques.
Finally, you'll learn to optimize query performance and automate data refreshes, ensuring your analysis remains current. By the end of this guide, you'll have the confidence and expertise to effectively transform and manage data using Power Query, significantly enhancing your data analysis capabilities in Excel and Power BI.

Leseprobe

by

Ken Puls &

Miguel Escobar

Holy Macro! Books

PO Box 541731

Merritt Island, FL 32953

Master Your Data with Excel and Power BI

© 2021 Tickling Keys, Inc.

All rights reserved. No part of this book may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording, or by any information or storage retrieval system without permission from the publisher. Every effort has been made to make this book as complete and

accurate as possible, but no warranty or fitness is implied. The information is provided on an “as is” basis. The authors and the publisher shall have neither liability nor responsibility to any person or entity with respect to any loss or damages arising from the information contained in this book.

Authors: Ken Puls and Miguel Escobar

Layout: Jill Bee

Copyediting: Deanna Puls

Cover Design: Shannon Travise

Indexing: Nellie Jay

Ape Illustrations: Walter Agnew Moore

Cover Illustration: Pavel Goldaev

Published by: Holy Macro! Books, PO Box 541731, Merritt Island FL 32953, USA

Distributed by: Independent Publishers Group, Chicago, IL

First Printing: September, 2021, Updated with typo corrections November 30, 2021 & January 22, 2023.

ISBN: 978-1-61547-058-7 Print, 978-1-61547-241-3 PDF, 978-1-61547-358-8 ePub, 978-1-61547-141-6 Mobi

Library of Congress Control Number: 2021942985

Table of Contents

Foreword

Chapter 0 - The Data Revolution

Chapter 1 - Power Query Fundamentals

Chapter 2 - Query Management

Chapter 3 - Data Types and Errors

Chapter 4 - Moving Queries Between Excel & Power BI

Chapter 5 - Importing from Flat Files

Chapter 6 - Importing Data from Excel

Chapter 7 - Simple Transformation Techniques

Chapter 8 - Appending Data

Chapter 9 - Combining Files

Chapter 10 - Merging Data

Chapter 11 - Web Based Data Sources

Chapter 12 - Relational Data Sources

Chapter 13 - Reshaping Tabular Data

Chapter 14 - Conditional Logic in Power Query

Chapter 15 - Power Query Values

Chapter 16 - Understanding the M Language

Chapter 17 - Parameters and Custom Functions

Chapter 18 - Date and Time Techniques

Chapter 19 - Query Optimization

Chapter 20 - Automating Refresh

Index

Foreword

How Power Query Changed OUR Lives

Ken’s Story: “Coffee & Power Query”

It’s the name on the meeting in my Outlook calendar from back in November 2013. It was during one of the Microsoft MVP summits, the product had recently had its name changed from Data Explorer, and I was meeting with Miguel Llopis and Faisal Mohamood from the Power Query team over coffee to talk about the good and the bad of the tool from an Excel users’ perspective.

In that conversation, I told them both that Power Query was great, but it was a lousy replacement for SQL Server Management Studio. I distinctly remember that part of the conversation. I’d been working with SSMS and Power Query a lot at the time and was struggling with the fact that Power Query did some of the same tasks, but not all. I was frustrated, as I was struggling with the tool, trying to make it behave the same, but it just wasn’t doing it.

What happened after I laid out my concerns flipped my complaints on their head. I’m paraphrasing from my memory, but the response was something like this:

“Ken, this tool isn’t a replacement for SSMS. We built this for Excel people… our intent is that they never need to use or learn SQL at all.”

For anyone that knows me well, they know that I’m very seldom left speechless, but that was just about enough to do it. That statement upset the balance of my world.

Understand that I’m not a normal Excel pro. I know enough SQL to be dangerous, I’m extremely accomplished with VBA and had also worked with VB.NET, C#, XML and a few other languages. And while I love technology and challenges, the true reason I know as many languages as I do today is that I taught myself out of necessity. Typically, my needs were complicated, and that involved a painful journey of jumping into the deep end with a “sink or swim” approach.

That meeting changed my view of Power Query forever. I took a step back and looked at it in a new light. And I started to use it as it was intended to be used… on its own, driving everything through the user interface, avoiding writing SQL wherever possible. And you know something… it started working better, it allowed me to go more places, it allowed me to solve things I’d never been able to do before.

I love this tool. Not just because of what I can do with it, but because of how easy it makes it to get things done for a business pro without the need for coding. Yes, there is a coding layer within the tool that you can learn, but it is entirely optional. That is what makes this tool so special: it has one of the best user-interface designs I’ve seen in a long time, which essentially writes code for you as you click buttons. I love this tool because the people we teach can pick it up rapidly and build complex solutions that add real business value in an incredibly short amount of time. This product is truly centered around the business professional.

On a personal note, Power Query has allowed me to quit my full-time corporate job and build my own business. We deliver live training (in-person or online), as well as our own commercial Excel add-in – Monkey Tools – which can help make your life even easier when working with Power Query and Power Pivot in Excel. Ultimately, there is nothing that gets me more excited than seeing someone find that magic moment that significantly impacts their workflow and saves them time.

Miguel’s Story: A new beginning

Before starting my business as a freelancer in 2013, I had a reputation in my past jobs of being the “power user”, so I kept that nickname even after leaving those jobs and that’s how I named my YouTube channel and now my new website ‘The Power User’.

I was never in IT, but I was usually the guy trying to push things forward in terms of how technologically advanced we were and how much value we could get from the tools that we had at hand, which was usually just Excel (and not even the latest version). Pivot Tables and Excel formulas ended up becoming like second nature to me.

Fast forward to 2013 and I got introduced to Power Query. I can’t really remember how I got to it, but things like simply filtering data, removing columns, promoting headers, and unpivoting columns had a huge impact on my day to day. I had no knowledge of VBA (and I still don’t), so Power Query literally opened completely new data wrangling opportunities for me which were previously impossible. I no longer needed to become a VBA or SQL Expert – I just needed Power Query and my data preparation issues would be gone.

The Power Query user interface was the hook for me. It felt intuitive and is the type of interface that puts you right in the driving seat in front of what matters most to you - your data. However, since it was a new tool and a new language, there was little content or information on the web about how to get the most out of Power Query, so I started my journey to be “the very best, like no one ever was” on this new technology and started creating content for it.

Through this new content creation (blogging, videos, emails, etc.), I ended up meeting people like Rob Collie and Bill Jelen who later introduced me to Ken who was also big on Power Query. Ken and I never met in person, but we decided to work together because we felt we complemented our views on Power Query and we both wanted to “preach” about how amazing Power Query is. We started a project called PowerQuery.Training which ended up fostering the content that got published in the first edition of our book. During that period of writing the first edition and even before that, we realized the true potential of Power Query and how it could change the lives of most Excel users for the better. For us, Power Query was and still is a major breakthrough as far as self-service tools go.

Ever since we published the first edition of our book, readers, friends, and colleagues have reminded us that some of the pictures and the content in that first edition was getting outdated, but that the content was still a solid foundation, and it opened their eyes to see the potential that Power Query has. That has been our north star since the very beginning – we’re on a mission to change people’s lives the same way that this tool changed our lives and made data preparation simple and straightforward.

From 2015 to 2021, Ken and I received more and more confirmation from the readers of our book that Power Query is changing people’s lives; either directly or indirectly. Hearing that type of feedback always put a smile on our faces. It’s reassuring and it’s also the main motivation why we decided to write a second edition of the book the way that we did. We wanted it to be done just right and for that, we needed to wait for the right time.

In May of 2021, I was asked if I would be interested in joining Microsoft as a Program Manager on the Power Query team. To me, this means taking a new role but following the same mission – to bring Power Query to more people and have a positive impact in the way they work.

As this book is going to print we are still working out the formal contractual details and start date of the position, but I can’t put into words how motivated I am for this new role. And if you needed an example of how Power Query can change people’s lives, I’m one example of how much it can.

Author Acknowledgements

As with any book, there are a bunch of people who are quite influential with making things happen. Without the influence of the people below, this book would never have come to fruition:

Bill Jelen – We cannot imagine working with someone who is more accommodating than Bill. Writing a book involves a huge amount of time and effort. Just balancing that against our own business needs is tough, but when the book is based on technology that changes as quickly as Power Query is changing… And then, with very short notice, we dropped a book on his lap that was twice the length of what we had originally promised. Bill accepted every delay and change with grace and understanding, encouraging us on a regular schedule to get the book finished.

Miguel Llopis – From the very first meeting over coffee, Miguel has been our go-to guy at Microsoft, even joking that his full-time job is answering Ken’s emails. He’s been incredibly supportive since day one, has responded to feature design requests, bugs and so much more.

Curt Hagenlocher, Ehren Von Lehe, Matt Masson, and all the others on the Power Query/Power BI team – We cannot begin to express how willing this team has been to talk to us and answer our questions and emails. Their help and clarifications have been incredibly helpful in turning out the finished product.

Wyn Hopkins, Cristian Angyal and Matt Allington – For the feedback and opinions on some material that we were particularly concerned that we had pitched correctly.

The countless people that have commented on our blogs and videos, attended our training sessions, and shared their own creative and alternate solutions with the world. Each of you has helped challenge us to explore new methods, develop better techniques, and have a lot of fun with this program.

Ken would like to thank:

Our previous book started with an email on Mar 6, 2014, introducing me to Miguel Escobar. He had a dream; to write a book on Power Query. Despite the fact that we’d never met in person – and still wouldn’t for several years – Miguel’s ideas and energy have had a profound impact on me. They led to the creation of our initial book (M is for Data Monkey), an online Power Query Workshop, our Power Query Academy, and now a second edition of the book. Without his inspiration and devotion to these projects, none would have come to fruition. His passion has continued to push my personal growth with Power Query, particularly when working with the M language itself. I am still trying to figure out how he can work 24 hours per day though!

This book would never have been finished without the support of my family. Even more than being a rock in my corner, my wife Deanna did the initial proof-read of every page of this book, (several times), fixing my spelling and clearing up the odd wording that I sometimes write down when my brain is a paragraph ahead of what my fingers are typing. I also need to give a shout out to my daughter Annika who taught me all about the Oxford comma (including the fact that Taylor Swift doesn’t use them). I only wish she’d shared that wisdom with me more than 72 hours before the manuscript was submitted!

We now have a team of people at Excelguru who held down the fort as I locked myself away to complete this manuscript: Rebekah Sax – who handles everything we throw at her with grace, Abdullah Alharbi – who gets tossed a rough idea for Monkey Tools that he has to bring to fruition in code, and Jim Olsen – my friend, mentor and former manager who now looks after our accounting for us. Without each and every one of you doing what you do, there is no way that we would be as successful as we are, or that I would have been able to finish this project.

Anyone who works on the Excel team can tell you that I deliver fairly passionate feedback about the product. I’m fairly certain no one has been on the receiving end of that more than Guy Hunkin, who lives at the nexus of both Power Query and Excel, tasked with the integration of the two technologies. Guy’s endless patience absolutely amazes me, and I can’t thank him enough for always taking my feedback professionally, but never personally. Beyond our emails and calls, I’m lucky enough to have had Guy attend a couple of my training courses where he took copious amounts of notes which have led to several things being fixed or changed.

Finally, I’d like to thank our business partner Matt Allington. Matt joined Miguel and I at the beginning of the COVID pandemic in mid-2019, in order to expand the Power Query Academy and our business operations. Since then, we have re-branded to become https://skillwave.training – and now offer both self-paced and coached training in Power Query, Power Pivot, and Power BI (among other topics). Matt has been a friend for many years, but of particular importance to this book was some advice on scheduling and prioritizing that actually allowed us to get it over the finish line.

Miguel would like to thank:

I’d like to thank YOU for reading this. Yes…YOU! You’re a crucial part of our main objective and our intention with this book is to provide you with the resources so you can become a Data [M]aster, Data [M]agician, and, above all, a great Data [M]onkey in the most positive way possible. I’d like to thank you in advance for making this world a better place – at least in the context of business decision making and the world of data.

I’d also like to thank all of the Excel and BI practitioners worldwide that have shown their support towards our book and our Power Query related endeavors. It is truly an honor to be part of this worldwide community and I invite you to join us by simply using this tool.

Let’s not forget about a crucial part of my life: Friends and Family. I’m not putting names in here as I’m afraid I might leave someone out of it – so I’m playing it safe here! :)

Special thanks to Ken for being extremely supportive and being able to overcome the language barrier at times with me! “Spanglish” gets me sometimes, yet Ken distinguishes what I’m trying to say and makes a better version of it.

Also, special thanks to Curt Hagenlocher, Ehren Von Lehe, Matt Masson, and Miguel Llopis from the Power Query team who I’ve been spamming with questions, bugs, rants, suggestions, ideas and overall complaints about Power Query since 2013 and, to this day July 4th 2021, they still haven’t ignored me or just told me to stop – if you need some classes about patience and customer service, you should speak with them someday :). They are the real MVPs.

Our Loyal Supporters

There are a great many of you who pre-ordered this book when it first went on sale at Amazon, and/or you signed up for the Power Query Academy at https://skillwave.training (or https://powerquery.training). Each of you has been promised a copy of this book and has been waiting a LONG time to actually see it arrive at your door. THANK YOU for both your support and your patience. We truly hope that you feel that it was worth the long wait.

And finally…

A huge thank you to our Power Query Academy members at Skillwave.Training who jumped on the opportunity to proof-read the book on a very tight schedule. We especially want to throw a shout out to Seth Barron, Randall McHenry, Stanton Berlinsky, John Hackwood, Mitchell Allan, Nick Osdale-Popa, Mike Kardash, and Lillian, each of whom submitted over a dozen spelling and grammar fixes through the book.

We’d also like to thank YOU. For both buying the book, putting your trust in our teaching methods, and for becoming part of the Power Query movement.

This book was written for you, in an effort to help you master your data. We truly hope it does, and that you’ll find it to be the most impactful productivity book you’ve ever purchased.

We’d like to thank YOU. For both buying the book, putting your trust in our teaching methods, and for becoming part of the Power Query movement.

Chapter 0 - The Data Revolution

The Common Scenario of the Data Analyst

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

Extracting the data from a data source,Transforming the data to our needs,Appending data sets,Merging multiple data sets together, andEnriching our data for better analysis.

We are Information Workers. And no matter what you call yourself in your formal job description, our role is to take our data, clean it up, and turn that data into information. Our job may not be glorious, but it is essential, and without our work done correctly, the end results of any analysis are suspect.

Naturally, our tool of choice for years has been Microsoft Excel. And while tools like Excel have amazing functionality 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 can often spend most of our time on; prepping the data for analysis, getting it into a nice tabular format so that it can be consumed by analytical and reporting tools.

Behind the curtains, we are all information workers trying to reach our desired goal with data

To those who have done our jobs, they’ll know that we are more than just Information Workers; we are Data Magicians. Our data seldom enters our world in a ready-to-consume format, instead it can take hours of cleaning, filtering and re-shaping 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. Conditional formatting, filters, pivot tables, charts, slicers and more, each of these tools will open up to us and let us weave the true magic to impress our audience.

Our issue comes much earlier in the process. 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. Ultimately our end goal is simple: get the data into a tabular format as quickly as possible, while ensuring it is scoped to our needs, and accurate. And with every solution needing a different combination of data coming from different sources… it takes magic.

Black Magic: What really happens to data before consumption

The Benefits and Dangers of Black Magic

And the true wizards of Excel use many different techniques to weave their magic; sometimes on their own, and sometimes in combination with other tools. 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 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) – A powerful language that can help you create powerful and dynamic transformations for your data, these techniques tend to be used by advanced users, due to the discipline required to truly master them. SQL Statements – Another powerful language for manipulating data, SQL 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, with many Excel Pros not even knowing where to get started with it. This language is often confused with being the sole domain of database professionals, although every Excel Pro should invest some time in learning it.

Each of these tools has something in common; they were essentially the only tools that we had in order to clean and transform our data into something useful.

Despite their usefulness, many of these tools also had two serious weaknesses: the time needed to build the solution and the time needed to master the techniques.

While it’s true that the truly savvy magicians could build solutions to automate and import raw data in a clean format, this took years of learning advanced languages, and then 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.

Which leads to a third danger of having a true wizard in the company; they build an incredible solution which works until long after they’ve left the company. It’s only then that the company realizes that they didn’t understand the solution, and don’t have anyone to fix it.

On the flip side, many people tasked with this data cleanup didn’t have the 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 world-wide and… do we need to go bigger? The cost of lost productivity in this area is staggering.

We need a better way. We need a product that is easy to learn, that others can pick up and understand with limited instruction. We need a product which lets us automate the import and cleanup of data, letting us focus on turning that data into information, adding true value to our company.

That product is finally here. It’s called Power Query.

The Future Transforms

Power Query is the answer to our data issues and solves the earlier issues of each of the toolsets. It is very easy to learn, having 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 can be reviewed or updated later. And everything done in Power Query can be refreshed with a couple of clicks.

From the perspective of two people who have spent years building solutions using black magic techniques, Power Query is a game-changer for many reasons. One of those is the speed with which it can be learned.

When specifically trying to import, clean and transform data to get it ready for analysis, Power Query can be learned faster than even Excel formulas, and handles complex sources much more easily than VBA:

Power Query was designed to be an easy-to-use Data Transformation and Manipulation tool

The ease of use is actually the reason we believe that this tool is the answer to the issue of the vanishing data magician that faces so many businesses. Even if that magician builds something complex in Power Query, you can have someone up to speed to be able to maintain or fix the query with minimal training, (as in 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, then build the chart or report they need. It’s for exactly this reason that Power Query’s reach will be even broader than those users who master formulas. With the menu-driven interface, a user never has to learn a single formula or line of code in many cases.

Power Query’s ease of use will impact more users than any of our classic methods

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

We also want to make it quite clear here that we are not discounting the value of formulas, VBA or SQL. In fact, they are tools that we could not live without. Formulas can be knocked out quickly outside the tranformation context to do many things that Power Query will never do. VBA has a far greater reach in sheer capability and power, allowing us 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 that created by Power Query.

In the context of simply connecting to, cleaning and importing data, however, Power Query offers more for less, allowing us to automate the job more quickly and with less investment in time. And with the constant improvements made by the Power Query team, those gaps between the SQL pro and the Power Query generated queries are shrinking.

As impactful as this is for Excel users, it’s important to recognize that Power Query is not just about Excel. In the past, if you’d built a system in Excel to transform and load data, it would need to stay in Excel or be totally re-written in a new language. But Power Query offers your data transformation process a “grow-up” story. The reason is that the same Power Query technology is in use in Excel, Power BI Desktop, Power Automate and Power BI Dataflows. So today, when you’ve built a solution using Power Query in Excel, you can simply import it into Power BI Desktop, or copy it into Power BI Dataflows.

Beyond creating portable and scalable solutions, this means that as data pros, we can learn a new portable skill and re-use it many times across various different software products. And even better? We have no reason to expect that Power Query won’t expand beyond these footprints.

And with its solid integration into other software, we get the best of both worlds. We can provide our own SQL queries to Power Query if needed, refresh it with VBA in Excel or schedule the refresh via Power BI when desired, load our Power Queries directly into data models or entities, and so much more.

Why Power Query IS Magic

The number one issue facing the data pro when building robust and stable solutions has been accessing, cleansing and transforming the data. What we’ve needed, and yet many of us have never heard of, is an ETL tool:

ETL: Extract, Transform, Load

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 functional data pros?

Extract

Extraction can be targeted against one or more data sources including the following: 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, Salesforce and other “Software As A Service” (SAAS) sources that you’d never have expected. And naturally, there are ODBC and OLEDB connectors for those databases that haven’t yet been covered by the team. No matter where your data lives today, there is a very solid chance that you can extract it and use it with Power Query.

Transform

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

1.Data Cleansing – This includes filtering out departments from a database, to removing blank or garbage rows from a text file import. Other uses include changing cases from uppercase to lower case, splitting data into multiple columns and forcing dates to import in the correct format for your country. Data cleansing is anything you need to do to your data to clean it up to be used.

2.Data Integration – If you use VLOOKUP(), INDEX()/MATCH() or the newer XLOOKUP() formulas in Excel, then you’re probably integrating multiple datasets. 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 and more.

3.Data Enrichment - These tasks include addingnew columns or doing calculations over a set of data. From performing mathematical calculations like creating Gross Sales by multiplying Sales Quantity * Sales Price, to adding new formats of dates based on your transaction date column, Power Query makes this easy. In fact, with Power Query you can even create entire tables dynamically driven based on the value in an Excel cell, SQL dataset or even a web page. Need a dynamic Calendar table that runs five years back from today’s date? Look no further than Power Query.

What is truly amazing about Power Query is how many transformations can be performed through menu commands, rather than having to write formulas or code to do it. This tool was built for end-users and requires no coding experience whatsoever in order to perform transformations that would be incredibly complex in SQL or VBA. That’s a great thing!

If you are 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” (we joke that languages A through L were taken). And for those wizards who decide to take advantage of this language, we can build even more efficient queries and do even more amazing things.

No-code, low-code or pro-code: the option is totally up to you. But no matter which way you choose to go, you’ll be floored with just how much can be done in the no-code world.

Load

As each program that supports Power Query has different uses, the locations you can load your data to will vary:

1.Excel: Load to Excel Tables, the Power Pivot Data model, or only to Connections

2.Power BI: Load to the Data Model, or only to Connections

3.Power Automate (Flow): Load to Excel workbooks (and we expect more destinations in future)

4.Dataflows: Load to Azure Data Lake Storage, Dataverse, or to Connection Only

The “Connections” might seem a bit mysterious, but it simply means that we 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 Load process in this ETL tool. It’s how it loads, or rather how to load it again.

Power Query is essentially a macro recorder, keeping track of every step you use when you work through the Extract and Transform steps. This means that you define your query once and determine where you’d like to load it. After you’ve done that you can simply refresh your query.

Define the transformation process once and consume anytime

Consider this for a moment. You import your text (.TXT) file, the one it used to take you 20 minutes to import and clean each month before you could use it. Power Query makes it easy, allowing you to accomplish the same task in 10 minutes, saving you 10 minutes the first time you use it. Then next month comes along and you get a new file…

Until now, you’d roll up your sleeves and re-live 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 and click Data → Refresh All in Excel (or Home → Refresh in Power BI). You’re finished. Seriously. And if you’ve published the file to Power BI or set it up in Power BI Dataflows, you can just schedule the refresh to avoid even that hassle!

This is the real power in Power Query. Easy to use, easy to re-use. It changes your hard work into an investment and frees up your time next cycle to do something worthwhile.

Power Query Experiences & Product Integrations

Power Query is a technology that is revolutionizing the world. It officially started in Excel back in 2013 as a COM add-on and it is now in over 8 different products ranging from Excel and Power BI Desktop, to SQL Server Integration Services, Azure Data Factory, and is probably being integrated in your favorite data-related Microsoft product as you are reading this book.

The impact that Power Query is having is phenomenal and is dramatically changing the lives of many data professionals across many different software products. The downside is that being integrated into so many products comes at a cost. The difficult situation that the Power Query team faces on a daily basis is balancing parity of functions, features and experiences throughout all these product integrations. They have to strike the sweet spot between consistency as well as features that are specific to the product that is hosting the Power Query feature set.

The Components of Power Query

You can think of Power Query as somewhat of an onion – it has layers – which are effectively the core components that make Power Query, well… Power Query.

Whenever we look at physical things, we see them at face value. As you progress through this book, you’ll learn that there is a lot that happens behind what we first see from Power Query. There is M code – which is visible to us – as well as the actual M Engine that you might never see as an end-user. Let’s take a quick look at the onion that is Power Query:

The layers behind Power Query

There are a total of three possible layers in Power Query, but some product integrations might only have the first two layers. These layers are:

M Engine - The underlying query execution engine that runs queries expressed in the Power Query formula language (“M”).

M Query - A set of commands written in the Power Query M formula language.

Power Query User Interface – Also known as the Power Query Editor, serves as a graphical user interface that helps the user with actions such as but not limited to:

Creating or modifying M queries by simply interacting with the user interfaceVisualizing queries and their resultsManaging queries by creating query groups, adding metadata, etc…

At the bare minimum, a product integration might have at least the M engine and the M query components. But as you can see from the table below, not every integration will contain all three layers of the Power Query onion:

Not all Power Query integrations contain all of its components

Experiences by Product Integration

If during the first half of 2021 you tried to compare the experiences found in Power Query for Excel against the Power Query experience found in Power BI Dataflows, you might have noticed some differences. Power BI Dataflows leverages the Power Query Online user interface, where Excel and Power BI have an experience based on the Power Query Desktop user interface. While the user interfaces do have differences, the underlying process of using them is similar.

If you try doing this again in the first quarter of the year 2024, you might notice that the gap is not as big as it was before. That’s primarily because the Power Query team is trying to work towards a single and unified core experience for the Power Query User Interface which will then be used across all user experiences and products.

Of course, there might still be some unique features per product integration in the future. Things such as grabbing data in a table directly from an active workbook might still be something unique to the Power Query experience found in Excel, but the core of the experience will be quite similar across all product integrations. Some of these differences translate into differences in the M engine, M code and the Power Query User Interface layers, while others might only impact something like the user interface. (An example might be different icons between different experiences.)

One thing which is certain is that, currently and for the past few years, Microsoft has been making a huge investment to push things to the Power Query Online experience first. Once they’ve iterated and tested enough, they then move those features into preview in the Power Query Desktop experiences, followed by eventual general release. This means that if you ever want to try out the “latest and the greatest” from Power Query, your best option is to use the Power Query Online experience through any of its product integrations such as Power BI Dataflows.

It is no secret that this tool is evolving quickly, both in features as well as user interface changes. Due to this fact, we have concluded that writing a book about Power Query with screenshots of the user interface that will remain current for the rest of our lives is simply impossible; in fact, the release of this book was delayed for two years while we waited for one user interface change to become live in Excel.

While we provide a great deal of click-steps through this book, we need you to recognize that the actual steps that you need to take when this book is in your hands may differ – both by product integration as well as by product if the user interface gets an update. But what won’t change is the goal, theory or recipes behind the examples. That is the core we are attempting to teach here; how to master your data, not necessarily the specific user interface you’ve been presented with. In this way, we hope to achieve our mission of writing a book that can be relevant for several years to come.

The Power Query Update Cycle

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 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 early 2015 they released an update that cut query load time by 30%. In July 2015 they released one that solved some very serious issues with refreshing to Power Pivot. We’ve seen Join Types, Conditional Columns and so much more released over the ensuing years. In the last three years specifically we saw the introduction of Columns from Example and Fuzzy Matching, among other new features.

So how do you get your hands on them? The answer is, it depends on the end point where you are consuming Power Query.

Power Query Online

Power Query Online refers to any instance of Power Query used in an online experience in programs such as Power Automate, Power BI Dataflows and others. These are all web-based services and there is nothing you need to update in any of them. Fixes and features are released on an ongoing basis, so you just need to review them every now and then to see what’s been added.

Microsoft 365

Our preferred way to consume Excel (or any other Office product) is via an Microsoft 365 subscription. If you are on subscription, the software will just update automatically with new bug fixes and features on a regular basis based on the “Channel” that your version of office is using.

🍌 Learn more about Channels at the following link:https://docs.microsoft.com/en-us/deployoffice/overview-of-update-channels-for-office-365-proplus

Excel 2016/2019/2021

Power Query is a product under constant development. If we look back at Excel 2016 (originally released in Sep 2015), it finally brought Power Query into Excel natively, but it also shipped with a named range bug as well as a legacy experience for combining files. In addition, the Join Types, Conditional Columns and Column From Examples feature hadn’t even been released at that point.

The good news is that even though the Excel 2016 and 2019 products were never on subscription, they did receive some Power Query updates after their initial releases. And we highly recommend that you update your software to get the new experience that we illustrate in the book.

The secret to getting these deployed on your system is to ensure that your system also gets updates for “other Microsoft products” when it downloads its Windows Updates. To check this setting in Windows 10:

Press the Windows key and type WindowsSelect the Windows Update SettingsGo to Advanced OptionsCheck the box next to “Give me updates for other Microsoft products when I update Windows”

Excel 2010 & 2013

Unlike Excel 2016 and higher, where it is built-in as part of the product, Power Query must be manually downloaded and installed from https://go.microsoft.com/fwlink/?LinkId=317450. The final update for Excel 2010 & 2013 was released in March 2019.

Power BI Desktop

Power BI Desktop has two delivery mechanisms. If you installed it via the Microsoft Store, then it will continue to update for you automatically. If, on the other hand, you downloaded and installed it via the advanced options at https://powerbi.microsoft.com/en-us/downloads/, you’ll have to manually download and install updates.

🍌 The cool thing about Power BI is that this is the first place we see new Power Query releases. They’re usually hidden in Power BI’s options window under the Preview Features tab. But if you want to see what’s coming to Excel, check there. New features are typically released to Power BI Desktop first, and then come to Excel 2-3 months after they hit General Availability (they are no longer in preview) in Power BI Desktop.

How to Use This Book

This book is intended to be your #1 resource in order to understand Power Query and the M language from a practical point of view, no matter if you are new to the tool or a seasoned ETL pro. Our goal is to address common problems that affect the everyday user and show you how we can use Power Query to solve them. We’ll also cover some more advanced scenarios as well, incorporating Power Query and M best practices throughout, to help you understand not only how to build Power Query solutions, but how to make them last.

The vast majority of the scenarios, illustrations and cases used through the book will be shown using the Microsoft 365 version of Excel. Unless otherwise stated, the illustrated scenario will work in either Excel or Power BI.

Where to Find the Power Query Commands

The key to locating the Power Query commands is simply to know where to look:

Excel 365

In versions of Excel released as part of a Microsoft 365 product, (which we refer to as Excel 365), the Power Query commands are found on the Get & Transform group of the Data tab. While there are some one-click shortcuts to common sources, you can find all the Power Query data sources available to you under the Get Data button.

Locating Power Query in Excel

Power BI Desktop

In this software, you don’t even have to leave the Home tab. Notice the huge Get Data button that is right there waiting for you to click it.

Locating Power Query in Power BI Desktop

Previous Excel Versions

While this book focuses on the Microsoft 365 version of Excel, the majority of the features are compatible with earlier versions. The thing to recognize however is that the commands may be in different areas:

Excel 2019: For the most part, Excel 2019 will look very similar to Excel 365. The one big exception at the time of printing was that the “From Sheet” button you will be directed to and see pictured in this book was called “From Table/Range”.Excel 2016: Like in Office 2019/365, the Power Query entry point is found on the Data tab, however you’ll find them under the New Query button (in the middle of the Data tab) instead of under the Get Data button you’ll find pictured throughout this book.Excel 2010/2013: You’ll find Power Query on its own tab once you have downloaded the installer. Where the steps in the book will point you to go to the Get Data button on the Data tab, you’d need to go to the Power Query tab and locate the command there.

“Get Data” Click-Steps

Power Query can connect to a wide variety of data sources, which you can explore by clicking the Get Data button found on the Data tab in Excel, or the Home tab in Power BI Desktop. While Excel’s data sources are subcategorized in menu subfolders, you’ll find that in order to see the subcategorized list in Power BI Desktop, you’ll need to click the More… button.

For the sake of consistency, we will use the following method to describe how to connect to a CSV file:

Create a new query →From File → From Text/CSV

The actual Excel click path would be:

Go to the Data tab → Get Data → From File→ From Text/CSV

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

Go to the Home tab → Get Data → More…→ File → Text/CSV

If you are still using Excel 2016 or earlier, these click-steps would read as follows:

Excel 2016: Go to the Data tab → New Query → From File → From Text/CSVExcel 2010/2013: Go to the Power Query tab → From File → From Text/CSV

Special Elements

🍌 Notes will appear in an indented paragraph with a banana icon. These delicious paragraphs point out special features, quirks, or software tricks that will help increase your productivity with Power Query.

🙈 Warnings appear in a shaded paragraph with a "see-no-evil monkey" icon. Ignore these at your peril, as they call out potential pitfalls and problems that could cause queries to break in the future.

Example Files

Before reading further, we highly recommend you download all the Workbooks used in this book so you can follow along with us. You can get them all at the following webpage:

https://skw-t.com/myd-files

It’s time to explore this amazing tool in-depth. Let’s get started.

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 OptionsUnder 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 → OptionsUnder 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 selected the connector we wish to use, we’ll be able to browse to and locate the file. In this case, we’ll connect to the following sample file:

Ch01 Examples\Basic Import.csv

Authentication

Many data sources will require authentication before you’ll be allowed to connect to them. Should that be the case, you’ll be prompted to provide authentication at this stage. Fortunately, this is a CSV file stored in your local file system and the mere fact that you have access to it implies authentication.

The Preview Window

Once you’ve selected your file, you’ll be taken to a window that looks similar to the following:

The Preview window for Power Query

The purpose of this window is to present you a view of the data as Power Query will interpret it, allowing you to make any necessary changes before Power Query starts the transformation process. Overall, we find that you rarely need to change anything here, as Power Query is fairly good about making the correct default choices in most cases. Having said that, you’ll notice that there are options at the top which will allow you to toggle the settings for:

File Origin. This would allow you to change the file encoding standard. It is unlikely that you will ever need to change this setting.Delimiter. It is also quite rare that this needs to be changed, as Power Query usually gets this right. If required, however, you could manually set this to one of a variety of options including a list of common characters, custom characters or even fixed column widths.Data Type Detection. This selection allows you to tell Power Query to detect data types based on the first 200 rows, entire data set or not at all.

Another important thing to be aware of is the message about the data preview being truncated. This is super important to be aware of as there is a limit to how much data can be shown in this window.

Choosing the Query Destination

At the end of the day, it’s unlikely you will change anything in this preview. The main purpose of the preview window is really to give you a look at the state and shape of the data and allow you to decide if this data meets one of three criteria:

1.Is it the wrong data set? If so, click the Cancel button.

2.Is it perfect? Then click Load.

3.Does it need reshaping or enriching? Then click Transform Data.

🍌 It is our belief that 80%-90% of data needs some kind of transformation before it can be used. The degree of transformation may be simple (just renaming a column), or much more complex. Regardless of what is required, however, the default option here should not be “Load”, but rather “Transform Data”.

Now that we’ve had a preview and decided that this is the data we need, we’ll click the Transform Data button to launch Power Query, which will launch you into a new window as shown here:

The Power Query Editor window in Excel

Transform

The next step of the ETL process is to Transform your data. Unlike Excel’s classic methods for importing data however, Power Query allows you to see and modify the default transformations that the system has assumed for you in the ETL process. These are managed in the Power Query Editor window, which launched upon clicking the Transform Data button.

The Query Editor Window

There are seven main areas of the Power Query Editor that we will be referring to within this book, each of which are numbered in the image shown here:

A detailed look at the Power Query Editor

The terms for each of these areas are:

1.The Ribbon: Located at the top of the screen, the Power Query ribbon has four tabs: Home, Transform, Add Column and View.

2.The Query Navigator Pane: In versions of Excel prior to 365 this pane was collapsed by default. You can always click the > just above the word Queries in order to maximize it, showing a list of all Power Queries in the project. (Note that in Excel 365 and Power BI, this window is expanded by default, and can be collapsed by clicking the < button instead.)

3.The Formula Bar: If this area is not showing, it means that you didn’t follow our advice earlier in the chapter on setting your defaults correctly. As this is an essential tool, it’s a good idea to hop over to the View tab on the Power Query Ribbon and check the “Formula Bar” checkbox now.

4.The Current View Window: This area is your working area to execute and preview data transformations. But while it can display a data preview, it may also allow viewing a schema view or diagram view.

5.The Status Bar: Located at the bottom of the screen, this provides a quick summary of the column count, row count and indicator of how many rows are being used to drive column profiling statistics, as well as an indicator on the far right as to when the preview was last updated.

6.The Properties Window: This area provides the name of the query and is inherited from the query’s data source.

7.The Applied Steps Window: This area will become very important to you in your Power Query journey, as it shows the transformations that have been applied to your data preview, and that will be applied to the entire data set upon import.

Default Transformations

Upon first extracting data from a file, it is helpful to understand what Power Query has already done for you. To do this, we’ll focus on the steps that are listed in the Applied Steps window on the right side. You’ll notice that there are currently three steps listed:

1.Source

2.Promoted Headers

3.Changed Type

The key thing to be aware of is that each of these steps is selectable, so you can see exactly what Power Query did when it imported the file. Let’s look at each one:

Source

By default, the first step of every query will be called “Source”, no matter what data source it comes from. Selecting it in the Applied Steps window will change the preview to show you Power Query’s original interpretation of the raw data it extracted:

The visual representation of the Source step