Transformative Magic of M Code in Power Query Excel & Power BI E-Book

MrExcel's Holy Macro! Books

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

Seitenzahl: 389

Veröffentlichungsjahr: 2024

Beschreibung

This book begins with the fundamentals, introducing key concepts like let expressions, keywords, and identifiers, before guiding you through more advanced techniques such as creating custom functions, handling diverse data types, and building reusable queries. Each chapter combines theoretical insights with practical examples, empowering you to handle complex data challenges with confidence.
As you progress, you’ll learn how to master lookup formulas, manipulate data with unpivot, append, join, and group operations, and explore advanced table transformations. A dedicated section on data connectors provides in-depth knowledge of importing data from various sources and locales, ensuring flexibility and adaptability.
The book concludes with practical data modeling projects that include handling inconsistent tables, appending multiple files, and building dynamic SharePoint connections. With a final focus on privacy levels and data security, this book equips you to use Power Query effectively and securely in real-world scenarios.

Leseprobe

A Beginner’s Guide to Mastering the Art of Data Metamorphosis to Get Just the Data Structure Needed to Create Insightful Data Analysis Solutions

by

Mike “excelisfun” Girvin

Holy Macro! Books

PO Box 541731

Merritt Island, FL 32953

The Transformative Magic of Power Query M Code in Excel and Power BI

© 2025 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.

Author: Mike Girvin

Tech Editor: Geert Delmulle

Layout: Bronkella Publishing LLC

Copyediting: Kitty Wilson

Cover Design: Shannon Travise

Indexing: Cheryl Lenser

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

Distributed by: Independent Publishers Group, Chicago, IL

First Printing: September 2024

ISBN: 978-1-61547-083-9 Print, 978-1-61547-169-0 e-Book

Dedication

Dedicated to our amazing online Excel team at YouTube, LinkedIn, and the MrExcel Message Board!

About the Author

Mike “excelisfun” Girvin has been a Microsoft Excel MVP since 2013 and a Highline College business instructor since 2002. He is the creator of the excelisfun YouTube channel and has been the internet leader in bringing free Excel and Power BI education to the world since 2008. The channel has 1 million subscribers, 3600 videos, 10,000 downloadable files, 100 playlists, and 20 different free classes focusing on Excel basics, advanced Excel, Power BI, data analysis, analytics, statistics, math, and much more. Mike Girvin has also authored several Excel books and DVDs and has won numerous awards for teaching Excel.

Before joining academia, Mike “Gel” Girvin ran the boomerang manufacturing company Gel Boomerangs in Oakland, California, from 1984 to 2002, where he won numerous boomerang design and competition awards. It was while Mike was running Gel Boomerangs in the 1990s that Steve Kavanaugh showed him Excel for the first time. From that point forward, Mike couldn’t resist the power and fun of Excel, and he went on to be an Excel teacher for the world. Currently, when Mike “Magic” Girvin is not creating Excel and Power BI solutions, you can find him each weekend out racing and parking BMX bikes with fellow rad old guys and his kid Isaac “Iceman” Girvin.

Acknowledgments

My number-one Excel guy in the world is Bill “MrExcel” Jelen, who was the first person to make Excel videos. He inspired me and many other Excel people to make and share videos. MrExcel also started the MrExcel Message Board, where I learned many of my advanced Excel skills. Plus, MrExcel has written more than 60 Excel books!

I also want to thank the more than 1 million subscribers at YouTube; in the comments below every one of the videos I post, I get to learn new things about making efficient Excel and Power BI solutions—and having fun doing it! I must also thank Geert Delmulle, who diligently did the technical edit for this book. Saving the biggest acknowledgement for last: I want to thank the world’s greatest editor: Kitty “Magician” Wilson!

Contents at a Glance

Introduction

Chapter 1: Power Query and M Code

Chapter 2: M Code Values

Chapter 3: Custom Functions

Chapter 4: M Code Lookup Formulas

Chapter 5: Unpivot, Append, Join, and Group By

Chapter 6: Data Connectors

Chapter 7: Data Modeling

Conclusion

Table of Contents

Introduction

Who This Book Is For

The Microsoft Power Query M Language Specification

Files to Download So You Can Follow Along

Chapter 1: Power Query and M Code

History of Power Query

Power Query and M Code

Comparing the Four Function-Based Languages

The User Interface and M Code

Three Locations to Edit M Code

The let Expression, Keywords, Identifiers, and Expressions

Three Different Load Data Buttons

Summary

Chapter 2: M Code Values

Type Values and Data Types

The if Expression

The Table.AddColumn Function

Date, Time, Datetime, Datetimezone, and Duration

Working with Date- and Time-Related Values

Using let Expressions to Define Variables in Formulas

Calculating Hours Elapsed from Datetime Values

Tables, Records, and Lists

Binary Values

Summary

Chapter 3: Custom Functions

Creating Two Reusable Function Queries

Creating a Custom Function in a Function Argument

Using each and an Underscore to Create a Custom Function

Creating a Custom Function Query Step in a let Expression

Recursion with Custom Functions

Using List.Accumulate to Simulate Recursion

Summary

Chapter 4: M Code Lookup Formulas

Exact Match Lookups

Approximate Match Lookups

Summary

Chapter 5: Unpivot, Append, Join, and Group By

Unpivot and the Table.UnpivotOtherColumns Function

Appending: Table.Combine vs. Table.ExpandTableColumn

Join Operations Used by the Merge Feature

The Table.Group Function and the Group By Feature

Other List Functions and Table.Sort, Too

Three Table.Group Tricks

Summary

Chapter 6: Data Connectors

CSV Files vs. Text Files

On-Premises File and Folder Paths

Using the Locale Feature to Import Data from Different Locales

ISO Dates

Online Data Sources

Summary

Chapter 7: Data Modeling

Project 1: Using From Folder and Combine Files to Combine Multiple Excel Files, Each with a Single Object

Project 2: Using a Custom Column to Combine Multiple Excel Files, Each with a Single Object

Project 3: Appending Multiple Text Files with Table Structure Problems

Project 4: Appending Tables with Inconsistent Column Names

Project 5: Appending JSON Tables with Filename Attributes

Project 6: Importing Multiple Excel Files, Each with Multiple Objects

Project 7: Combining Two Fact Tables into One Fact Table

Project 8: Converting a Single Column of Badly Structured Records into a Proper Table

Bonus Topic: Privacy Levels and Data Security

Bonus Example: Dynamically Connecting to SharePoint Server Files from Within an Excel File

Conclusion

Index

Introduction

This book will teach you the fundamentals of how to use and write Power Query M code in Excel, Power BI Desktop, and Dataflow in the online Power BI service. The M code can be slightly different in each tool, but all the fundamentals of M code are the same in each tool.

Power Query’s M code is a case-sensitive, function-based data-shaping computer language. There are more than 800 M code functions in Excel and more than 1000 of them in Power BI Desktop and Dataflow. You will learn about many of the amazing functions that accomplish data tasks that no other language can do quite as easily. However, you cannot easily understand most of the functions until you learn about M code fundamentals such as the let expression, M code values, data types, custom functions, and M code lookups.

To prepare you to use M code, this book covers the following topics:

The user interfaceWhere you can edit M codeIdentifiers, keywords, and expressionsThe let expressionM code valuesData typesCustom functionsM code lookupsDrill-down and primary keysHow to unpivot, append, join/merge, and group byData connectorsData modeling

When you have assimilated the content of this book, you will be able to understand some of the wild M code that the user interface generates, build custom functions to make the many unique data transformations that are not possible with the user interface, and author custom let expression queries to accomplish any data scenario, including the seemingly impossible.

Who This Book Is For

This book is for you if you have learned to use Power Query through the user interface, are familiar with navigating through Power Query, and want to take your Power Query abilities to the next level by learning the function-based language behind every query: M. It is also for you if you are an analyst using Excel or Power BI who wants to learn and understand the syntax and structure of the M language so that you have full access to the power of Power Query to get, clean, and transform data. This book will give you a solid understanding of the M language so you can edit and create efficient queries for all your projects that involve data. Some say that you don’t really know Power Query until you know M—and I agree. So, if you want to learn M to gain data-shaping skills in Excel and Power BI, this is the book for you!

The Microsoft Power Query M Language Specification

The Microsoft Power Query M Language Specification is a guide worth checking out because it speaks to every minute detail about M code. You can find it at https://learn.microsoft.com/en-us/powerquery-m/power-query-m-language-specification.

Files to Download So You Can Follow Along

Following along and trying examples as you read about them is a great way to learn. I’ve therefore provided all the Excel and Power BI files that you see in this book as a zipped file that you can download from https://excelisfun.net/files/MCodeExcelisfunBook.zip.

Here are a few points to keep in mind about the files for this book:

Almost all of the queries in the Excel files use Excel tables from the current workbook file as the source data to avoid on-premises file path errors. In addition, almost all of the queries in the Power BI Desktop files use data that was pasted in using the Enter Data feature, which uses a combination of the M code functions Table.FromRows, Json.Document, Binary.Decompress, and Binary.FromText. Pasting the data avoids on-premises file path errors.In some cases, in both Excel and Power BI, this book also uses intrinsic tables as the source data to avoid data source errors.Almost every query in the files is loaded as a connection only and is not loaded to a final location in order to make the file size much smaller and more manageable.Many query names end with the letter Q to differentiate them from the data source Excel table names.

If you are using Dataflow (in the Power BI online service), you will have to import the tables and queries from the Excel files.