# The Definitive Guide to Power Query (M), First Edition
This is the code repository for [The Definitive Guide to Power Query (M), First Edition](https://www.packtpub.com/product/the-definitive-guide-to-power-query-m/9781835089729), 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]()
## About the book

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


## Key Takeaways
- 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


## Outline and Chapter Summary

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.


1. Introducing M
2. Working with Power Query/M
3. Accessing and Combining Data
4. Understanding Values and Expressions
5. Understanding Data Types
6. Structured Values
7. Conceptualizing M
8. Working with Nested Structures
9. Parameters and Custom Functions
10. Dealing with Dates, Times, and Durations
11. Comparers, Replacers, Combiners, and Splitters
12. Handling Errors and Debugging
13. Iteration and Recursion
14. Troublesome Data Patterns
15. Optimizing Performance
16. Enabling Extensions



> If you feel this book is for you, get your [copy](https://www.amazon.com/Definitive-Guide-Power-Query-Transformation/dp/1835089720/ref=sr_1_1?crid=2UM82YUS4IWFM&dib=eyJ2IjoiMSJ9.WyzGyckwFVlUY3v5UAE-E25BjnuOKq3uvaO-3bF-B9xWoIjNWOuKb1DRfUiH8R2Ao7-2vJJ2TGg2dNXCDN4SdYt1NyqFXzCdqGXVG2D7vvIWrFjoM6Z9OmJUS7iOU4JddSBCrorxRb27JHBWFuak7sbY4fDmH1CKqwcPrZ3T6oij4UnS2EP62nMerl9TUTXZo0x7g2mbtMOuSgJXsvcGQuYpg34U9djaKLkJ_gVoQjc.TFSmSRn15e2gBC78wFEg2eTC3bOj92T7hH-4brd_LFA&dib_tag=se&keywords=The+Definitive+Guide+to+Power+Query+%28M%29%2C+First+Edition&qid=1712113108&sprefix=the+definitive+guide+to+power+query+m+%2C+first+edition%2Caps%2C431&sr=8-1) today! <img alt="Coding" height="15" width="35"  src="https://media.tenor.com/ex_HDD_k5P8AAAAi/habbo-habbohotel.gif">


With the following software and hardware list you can run all code files present in the book (Chapter number mention here).

## Software and hardware list

| 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](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](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](https://marketplace.visualstudio.com/items?itemName=Dakahn.PowerQuerySDK) | Should work on any recent computer | Windows, MacOS, Linux (any), macOS, Windows |



## Know more on the Discord server <img alt="Coding" height="25" width="32"  src="https://cliply.co/wp-content/uploads/2021/08/372108630_DISCORD_LOGO_400.gif">
You can get more engaged on the discord server for more latest updates and discussions in the community at [Discord](https://discord.gg/vCSG5GBbyS)

## Download a free PDF <img alt="Coding" height="25" width="40" src="https://emergency.com.au/wp-content/uploads/2021/03/free.gif">

_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](https://packt.link/free-ebook/9781835089729) <img alt="Coding" height="15" width="35"  src="https://media.tenor.com/ex_HDD_k5P8AAAAi/habbo-habbohotel.gif">

We also provide a PDF file that has color images of the screenshots/diagrams used in this book at [GraphicBundle](https://packt.link/gbp/9781835089729) <img alt="Coding" height="15" width="35"  src="https://media.tenor.com/ex_HDD_k5P8AAAAi/habbo-habbohotel.gif">


## Get to know the Authors
_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.

## Other Related Books
- [Extending Power BI with Python and R - Second Edition](https://www.packtpub.com/product/extending-power-bi-with-python-and-r-second-edition/9781837639533)
- [Expert Data Modeling with Power BI - Second Edition](https://www.packtpub.com/product/expert-data-modeling-with-power-bi-second-edition/9781803246246)

## Errata
- 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<br>
  as text ) as table =><br>
  Table.AddColumn(<br>
  table,<br>
  newColumnName,<br>
  each if List.Contains(<br>
  Table.Column( lookupTable, lookupColumnName),<br>
  Record.Field( table, "Product" ) )<br>
  then true else false )<br>
  
  
  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<br>
  as text) as table =><br>
  Table.AddColumn(<br>
    table,<br>
    newColumnName,<br>
    each if List.Contains(<br>
    Table.Column( lookupTable, lookupColumnName),<br>
      Record.Field( _, searchColumn ) )<br>
    then true else false )<br>

- 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
