The Definitive Guide to Power Query (M) : Mastering Complex Data Transformation with Power Query / Gregory Deckler, Rick de Groot, Melissa de Korte.

Format:

Book

Author/Creator:

Deckler, Gregory, author.

Groot, Rick de, author.

Korte, Melissa de, author.

Series:

Expert insight.

Expert insight

Language:

English

Subjects (All):

Power Query (Computer file).

Database management--Computer programs.

Database management.

Physical Description:

1 online resource (759 pages)

Edition:

First edition.

Place of Publication:

Birmingham, England : Packt Publishing, [2024]

System Details:

Mode of access: World Wide Web.

Biography/History:

Deckler Greg: Greg Deckler is a 7-time Microsoft MVP for Data Platform and an active blogger and Power BI community member, having written over 6, 000 solutions to community questions. Greg has authored many books on Power BI, including Learn Power BI 1st and 2nd Editions, DAX Cookbook, Power BI Cookbook 2nd Edition and Mastering Power BI 2nd Edition. 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. Groot Rick de: Rick de Groot was born in the Netherlands and has been working in BI for more than 14 years. He went freelance in 2016 and now works as an independent Power BI consultant. On his mission to make Power BI more accessible, he started two blogs: BI Gorilla and PowerQuery. how, and a YouTube channel sharing Power Query and Power BI content. His commitment to offering free content through multiple platforms has led him to earning the Microsoft Data Platform MVP award for two consecutive years. Korte Melissa de: Melissa de Korte's approach to facing challenges is fueled by relentless curiosity. She is a dedicated community member and content creator. Her portfolio includes blogs, tutorials, courses, and webinars, that make Power Query M more accessible and useful for all. Behind her professional persona lies a genuine dedication to empowering others through education and knowledge sharing, and a desire to encourage professionals to embrace the potential of Power Query, M.

Summary:

Learn how to use the Power Query M formula language and its functions effectively for better data modeling and impactful business intelligence reports. Purchase of the print or Kindle book includes a free PDF eBook Key Features Get comprehensive coverage of fundamental and advanced Power Query concepts Gain hands-on experience with practical examples based on real-world problems Delve into the intricacies of Power Query M language for enhanced data manipulation Book Description 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. What you will learn 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 Who this book is for This book is for business analysts, business intelligence professionals, and power business users working with data who want to add Power Query mastery to their resume. This book will be beneficial for anyone who wants to automate their process of data cleaning and save huge amount of time. Having some basic experience in Power Query is recommended.

Contents:

Cover

Copyright

Foreword

Contributors

Table of Contents

Preface

Chapter 1: Introducing M

The history of M

Who should learn M?

Where and how is M used?

Experiences

Products and services

Why learn M?

M language basics

The let expression

The characteristics of M

Formal classification

Informal characteristics of M

Summary

Chapter 2: Working with Power Query/M

Technical requirements

Touring the Power Query Desktop experience

A brief tour

Header

Formula bar

Ribbon

Queries pane

Query Settings pane

Preview pane

Status Bar

Your first query

Options and data source settings

Options

Data source settings

Editing experience-generated code

Creating custom columns

Adding an index column

Adding columns with examples

Math operations

Adding custom m code columns

Using the Advanced Editor

Chapter 3: Accessing and Combining Data

Accessing files and folders

File.Contents

Text/CSV

Excel

Folder

PDF

XML

Xml.Tables

Xml.Document

Azure Storage

Additional file formats

Retrieving web content

Investigating binary functions

Lines functions

Accessing databases and cubes

Cube functions

Working with standard data protocols

Addressing additional connectors

Popular software systems

Identity functions

Combining and joining data

Table.Combine

Table.NestedJoin and Table.Join

Table.FuzzyNestedJoin and Table.FuzzyJoin

Chapter 4: Understanding Values and Expressions

Introducing the types of values

Binary values

Structure

Related functions

Special considerations

The Date/Time family of values

Date values

Time values

DateTime values

DateTimeZone values

Duration values

Logical values

Structure.

Related Functions

Null values

Number values

Structure and examples

Text values

List values

Record values

Table values

Function values

Type values

Operators

Expressions

Nesting let expressions

Coding best practices for expressions

Control structures

Enumerations

Chapter 5: Understanding Data Types

What are data types?

The type system

Columns with mixed types

Column data type versus value type

Importance of types

Clarity and consistency

Data validation

Other reasons

Data types available in M

Primitive types

Abstract primitive types

Nullable primitive types

Using primitive types for data filtering

Custom types

List type

Record type

Table type

Function Type

Type detection

Retrieving data types from a data source

Automatically detecting types

Data type conversion

Converting value types

Converting column types

Avoiding data loss during conversion

Effect of locale/culture

Facets

Type Claims

Available Type Claims

Converting values using type claims

Inspecting Type Claims

Ascribing types

What is ascription?

Functions that support ascribing types

Ascribing types when creating records

Ascribing types when creating tables

Ascribing types when modifying tables

Ascribing types to any value

Errors when ascribing types

The base type is incompatible with the value.

The Type Claim does not conform with the value

Ascribing incompatible types to structured values

Type equivalence, conformance, and assertion

Type equality

Type conformance

Type assertion

Chapter 6: Structured Values

Introducing structured values

Lists

Introduction to lists

List operators

Equal

Not equal

Concatenate

Coalesce

Methods to create a list

Creating lists using the list initializer

Creating lists using functions

Referencing a table column

Using the a..b form

Accessing items in a list

Accessing list values by index

Handling non-existent index positions

Common operations with lists

Assigning data types to a list

Records

Introduction to records

Record operators

Concatenation

Methods to create a record

Creating records using the record initializer

Creating records using functions

Retrieving a record by referencing a table row

Accessing fields in a record

Field selection

Record projection

Common operations with records

Structure for variables

Referencing the current row

Providing options for functions

Keeping track of intermediary results

Assigning a data type to records

Tables

Introduction to tables

Table operators

Methods to create a table

Retrieve data from a source

Manually input data into functions

Re-use existing tables/queries

Using the Enter data functionality

Accessing elements in a table

Item access

Field access

Common operations with tables

Assigning a data type to tables

Chapter 7: Conceptualizing M

Understanding scope

Examining the global environment

Studying sections

Creating your own global environment

Understanding closures.

Query folding

Managing metadata

Chapter 8: Working with Nested Structures

Transitioning to coding

Getting started

Understanding Drill Down

The trick to getting more out of the UI

Methods for multistep value transformation

Transforming values in tables

Table.AddColumn

Table.TransformColumns

Table.ReplaceValue

Working with lists

Transforming a list

List.Transform

List.Zip

Extracting an item

Resizing a list

List.Range

List.Alternate

Filtering a list

List.FindText

List.Select

To-list conversions

Column or field names

A single column

All columns

All rows

Other operations

Expanding multiple list columns simultaneously

Flattening inconsistent multi-level nested lists

Working with records

Transforming records

Extracting a field value

Resizing records

Filtering records

To-record conversions

Table row to record

Record from table

Record from list

Conditional lookup or value replacement

Working with tables

Transforming tables

Extracting a cell value

Resizing a table in length

Resizing a table in width

Filtering tables

Approximate match

To-table conversions

Record-to-table conversion

Creating tables from columns, rows, or records

Table information

Working with mixed structures

Lists of tables, lists, or records

Tables with lists, records, or tables

Mixed structures

Flatten all

Unpacking all record fields from lists

Extracting data through lookup

Chapter 9: Parameters and Custom Functions

Parameters

Understanding parameters

Creating parameters

Using parameters in your queries

Putting it all together

Parameterizing connection information

Dynamic file paths

Filtering a date range

Custom functions

What are custom functions?.

Transforming queries into a function

What is the "create function" functionality?

Simplifying troubleshooting and making changes

Invoking custom functions

Manually in the advanced editor or formula bar

Using the UI

The each expression

Common usecases

Refining function definitions

Specifying data types

Making parameters optional

Referencing column and field names

Debugging custom functions

Function scope

Top-level expression

In line within a query

Turning all columns into text

Merging tables based on date ranges

Chapter 10: Dealing with Dates, Times, and Durations

Dates

M calendar table

Other date formats

Julian days

Alternate date formats

Additional custom date functions

Working days

Moving average

Time

Creating a time table

Shift classification

Dates and times

Time zones

Correcting data refresh times

Duration

Working duration

Chapter 11: Comparers, Replacers, Combiners, and Splitters

Key concepts

Function invocation

Some common errors

Closures

Higher-order functions

Anonymous functions

Ordering values

Comparers

Comparer.Equals

Comparer.Ordinal

Comparer.OrdinalIgnoreCase

Comparer.FromCulture

Comparison criteria

Numeric value

Computing a sort key

List with key and order

Custom comparer with conditional logic

Custom comparer with Value.Compare

Equation criteria

Default comparers

Custom comparer

Key selectors

Combining key selectors and comparers

Replacers

Replacer.ReplaceText

Replacer.ReplaceValue

Custom replacers

Combiners

Combiner.CombineTextByDelimiter

Functionality

Example

Combiner.CombineTextByEachDelimiter

Example.

Combiner.CombineTextByLengths.

Notes:

Includes bibliographical references and index.

Description based on publisher supplied metadata and other sources.

Description based on print version record.

ISBN:

1-83508-616-0

OCLC:

1428526694