This is the code repository for The Definitive Guide to Power Query (M), First Edition, published by Packt.
Mastering complex data transformation with Power Query
The authors of this book are -Gregory Deckler, Rick de Groot and Melissa de Korte
Data transformation is a critical step in building data models and business intelligence reports. Power Query is an invaluable tool for anyone who wants to master data transformation, and this book will equip you with the knowledge and skills to make the most of it.
The Definitive Guide to Power Query (M) will help you build a solid foundation in the Power Query M language. As you progress through the chapters, you’ll learn how to use that knowledge to implement advanced concepts and data transformations before going on a no-compromise ‘deep dive’ into the Power Query M language.
You’ll also get to grips with optimizing performance, handling errors, and implementing efficient data processing techniques. As this is a hands-on guide, the practical examples in the chapters will help you gain the skills to apply Power Query to real-world problems and improve your data analysis capabilities.
By the end of this book, you will be able to leverage all of Power Query’s remarkable capabilities for data transformation
- Gain a strong understanding of Power query fundamentals
- Master various functions within Power Query to perform complex data operations
- Acquire knowledge about values, types, and control structures in Power Query
- Develop proficiency in error handling techniques
- Learn performance optimization strategies for Power Query
- Apply what you’ve learned to real-world scenarios, including common troublesome patterns
Mastering Data transformation is essential for enhancing their data models and business intelligence. The Definitive Guide to Power Query equips you with the knowledge and skills to master the tool while leveraging its remarkable capabilities.
- Introducing M
- Working with Power Query/M
- Accessing and Combining Data
- Understanding Values and Expressions
- Understanding Data Types
- Structured Values
- Conceptualizing M
- Working with Nested Structures
- Parameters and Custom Functions
- Dealing with Dates, Times, and Durations
- Comparers, Replacers, Combiners, and Splitters
- Handling Errors and Debugging
- Iteration and Recursion
- Troublesome Data Patterns
- Optimizing Performance
- Enabling Extensions
If you feel this book is for you, get your copy today!
With the following software and hardware list you can run all code files present in the book (Chapter number mention here).
Chapter | Software required | Link to the software | Hardware specifications | OS required |
---|---|---|---|---|
All chapters | Power BI Desktop, version: 2.126.1261.0 64-bit (february 2024) | https://www.microsoft.com/en-gb/download/details.aspx?id=58494 | Should work on any recent computer | Windows 10 or Windows Server 2016 or later |
All chapters | Visual Studio Code | https://code.visualstudio.com/download | Should work on any recent computer | Windows, MacOS, Linux (any), macOS, Windows |
All chapters | Power Query SDK | https://marketplace.visualstudio.com/items?itemName=Dakahn.PowerQuerySDK | Should work on any recent computer | Windows, MacOS, Linux (any), macOS, Windows |
You can get more engaged on the discord server for more latest updates and discussions in the community at Discord
If you have already purchased a print or Kindle version of this book, you can get a DRM-free PDF version at no cost. Simply click on the link to claim your free PDF. Free-Ebook
We also provide a PDF file that has color images of the screenshots/diagrams used in this book at GraphicBundle
Gregory Deckler is a seven-time Microsoft MVP for Data Platform and an active blogger and Power BI community member, having authored over 6,000 solutions to community questions. Greg has authored numerous books on Power BI, including the first and second editions of Learn Power BI, the second editions of Power BI Cookbook and Mastering Power BI, and DAX Cookbook. Greg has also created several external tools for Power BI and regularly posts video content to his YouTube channels – Microsoft Hates Greg and DAX For Humans.
Rick de Groot is a Power BI consultant, blogger, YouTuber, and author who hails from the Netherlands. With a background in finance, he has devoted 14+ years to building his expertise in data analysis, particularly in the Power Query M language. He is an independent consultant who also provides training on Power BI, Power Query, and DAX. He regularly publishes articles on his blogs, Power Query How and BI Gorilla, which have emerged as important sources for M articles and tutorials. He contributes extensively to his YouTube channel BI Gorilla as well. Rick’s commitment to sharing his expertise has earned him the Microsoft Data Platform MVP award for two consecutive years.
Melissa de Korte is a passionate problem-solver known for simplifying complex problems with Power Query. With an impressive track record as an enterprise DNA expert and super user, she has become an asset to the community, actively engaging, supporting, and inspiring others. Behind her professional persona lies a genuine dedication to empowering others through sharing knowledge. Her portfolio includes blogs, tutorials, courses, and webinars to make Power Query and M more accessible.
- Extending Power BI with Python and R - Second Edition
- Expert Data Modeling with Power BI - Second Edition
-
Number.NegativeInfinity: This returns a constant value that represents 1 divided by 0. should be Number.NegativeInfinity: This returns a constant value that represents -1 divided by 0. on Page 117.
-
Page 377 shows the custom function:
( table as table, newColumnName as text, lookupTable as table, lookupColumnName
as text ) as table =>
Table.AddColumn(
table,
newColumnName,
each if List.Contains(
Table.Column( lookupTable, lookupColumnName),
Record.Field( table, "Product" ) )
then true else false )A mistake is in there. The line 'Record.Field( table, "Product" ) )' returns an error.
The actual code should be like below (with a change on both the first line, and the line before the last one):
( table as table, newColumnName as text, searchColumn as text, lookupTable as table, lookupColumnName
as text) as table =>
Table.AddColumn(
table,
newColumnName,
each if List.Contains(
Table.Column( lookupTable, lookupColumnName),
Record.Field( _, searchColumn ) )
then true else false ) -
Page 378 has: On line 3 the variable is called 'List'. It should be called 'FilteredList'
-
Page 126 Topic:Accessing Files and folders. Here in the first paragraph, we intended to refer to table 3.1 not 7.1