Sign In Start Free Trial
Account

Add to playlist

Create a Playlist

Modal Close icon
You need to login to use this feature.
  • Book Overview & Buying The Definitive Guide to Power Query (M)
  • Table Of Contents Toc
  • Feedback & Rating feedback
The Definitive Guide to Power Query (M)

The Definitive Guide to Power Query (M)

By : Greg Deckler, Rick de Groot, Melissa de Korte
4.8 (39)
close
close
The Definitive Guide to Power Query (M)

The Definitive Guide to Power Query (M)

4.8 (39)
By: Greg Deckler, Rick de Groot, Melissa de Korte

Overview of this 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. This will set the stage for an uncompromisingly thorough exploration of 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.
Table of Contents (19 chapters)
close
close
17
Other Books You May Enjoy
18
Index

What this book covers

Chapter 1, Introducing M, introduces M language basics such as the let expression and also covers the history of M and the formal and informal characteristics of M. It also discusses who should learn M, why you should learn M, and where and how to use M.

Chapter 2, Working with Power Query/M, introduces the Power Query Editor, the main application for coding M. In addition, it allows you to transition from only using the Power Query Editor’s GUI to create M code to writing the code yourself via custom columns and the Advanced Editor.

Chapter 3, Accessing and Combining Data, explores the multitude of different data connectors available for M, including file and folder connectors, database and cube connectors, working with binary data, and finally, how to combine and merge data between queries.

Chapter 4, Values and Expressions in M, introduces the various kinds of values in the M language, as well as expressions, operators, control structures, and enumerations.

Chapter 5, Understanding Data Types, explores the importance of data types in the M language. It teaches you about their structure and their application in real-world scenarios. The chapter offers techniques to automatically detect data types and discusses why type conversion is important. Additionally, it presents the concept of facets and type ascription, which often lead to errors in your queries.

Chapter 6, Structured Values, covers some of the most important values in the M language,such as lists, records and tables. These values can store multiple values within them. You will learn the techniques for creating them, their typical uses, and the operators that work with structured values. Furthermore, the chapter explains how to access the individual values they contain.

Chapter 7, Conceptualizing M, explores some of the more abstract concepts of M that are critical to truly understanding how the M language works. The topics covered include the global environment, creating your own global environment, sections, closure, query folding, and metadata.

Chapter 8, Working with Nested Structures, presents several techniques to transform and manipulate structured values, such as lists, records, and tables, helping you move past the limitations of the GUI.

Chapter 9, Parameters and Custom Functions, begins by discussing the role of parameters in making queries flexible and dynamic. It then delves into how to create custom functions, covering their syntax, the importance of data types, and debugging techniques. Finally, you learn that the each expression is syntax sugar for a function.

Chapter 10, Dealing with Dates, Times, and Durations, explains how temporal analysis is key to unlocking many data insights and why you must be proficient in dealing with dates, times, and durations to perform such analyses. This chapter explores these subjects and more with numerous practical examples.

Chapter 11, Comparers, Replacers, Combiners, and Splitters, explores techniques for manipulating data. It demonstrates how to customize the way values are compared, ordered, replaced, combined, or split. These methods are essential for a wide range of common data transformation tasks.

Chapter 12, Handling Errors and Debugging, focuses on what errors are and provides guidance on how to handle them in the M language and debug your code successfully, enabling you to build more robust queries. In addition, it offers techniques to report errors.

Chapter 13, Iteration and Recursion, explains recursion through the use of the @ operator. The chapter then shifts focus to iteration techniques, using List.Transform, List.Accumulate, and List.Generate. You’ll learn about memory considerations and what aspects to consider for the best performance.

Chapter 14, Troublesome Data Patterns, illustrates the versatility of the M language, covering various common text extraction techniques as well as providing a comprehensive approach to building a manageable custom solution for dealing with files in bulk.

Chapter 15, Optimizing Performance, examines factors that influence query performance. It introduces mashup containers and the importance of memory management. We’ll delve into query folding, explore the formula firewall’s mechanics, and present various methods to improve query performance.

Chapter 16, Enabling Extensions, demonstrates how to extend the M language by creating your own, reusable library of M functions, including a detailed example of creating a custom data connector.

Unlock full access

Continue reading for free

A Packt free trial gives you instant online access to our library of over 7000 practical eBooks and videos, constantly updated with the latest in tech
bookmark search playlist download font-size

Change the font size

margin-width

Change margin width

day-mode

Change background colour

Close icon Search
Country selected

Close icon Your notes and bookmarks

Delete Bookmark

Modal Close icon
Are you sure you want to delete it?
Cancel
Yes, Delete

Confirmation

Modal Close icon
claim successful

Buy this book with your credits?

Modal Close icon
Are you sure you want to buy this book with one of your credits?
Close
YES, BUY