Power Query Cookbook: Use effective and powerful queries in Power BI Desktop and Dataflows to prepare and transform your data

Length: 412 pages
Edition: 1
Language: English
Publisher: Packt Publishing
Publication Date: 2021-10-15
ISBN-10: 1800569483
ISBN-13: 9781800569485
Sales Rank: #158492 (See Top 100 Books)

Leverage your source data from hundreds of different connections, perform millions of different transformations, and easily manage highly complex data lifecycles with Power Query

Key Features

Collect, combine, and transform data using Power Query’s data connectivity and data preparation features
Overcome the problems faced while accessing data from multiple sources and reshape it to meet your business requirements
Explore how the M language can be used to write your own customized solutions

Book Description

Power Query is a data preparation tool that enables data engineers and business users to connect, reshape, enrich, and transform their data to facilitate relevant business insights and analysis. With Power Query’s wide range of features, you can perform no-code transformations and complex M code functions at the same time to get the most out of your data.

This Power Query book will help you to connect to data sources, achieve intuitive transformations, and get to grips with preparation practices. Starting with a general overview of Power Query and what it can do, the book advances to cover more complex topics such as M code and performance optimization. You’ll learn how to extend these capabilities by gradually stepping away from the Power Query GUI and into the M programming language. Additionally, the book also shows you how to use Power Query Online within Power BI Dataflows.

By the end of the book, you’ll be able to leverage your source data, understand your data better, and enrich it with a full stack of no-code and custom features that you’ll learn to design by yourself for your business requirements.

What you will learn

Understand how to use Power Query to connect and explore data
Explore ways to reshape and enrich data
Discover the potential of Power Query across the Microsoft platform
Build complex and custom transformations
Use M code to write new queries against data sources
Use the Power Query Online tool within Power BI Dataflows
Implement best practices such as reusing dataflows, optimizing expanding table operations, and field mapping

Who this book is for

This book is for data analysts, BI developers, data engineers, and anyone looking for a desk reference guide to learn how Power Query can be used with different Microsoft products to handle data of varying complexity. Beginner-level knowledge of Power BI and the M Language will help you to get the best out of this book.

Getting Started with Power Query
Connecting to Fetch Data
Data Exploration in Power Query
Reshaping Your Data
Combining Queries for Efficiency
Optimizing Power Query Performance
Leveraging the M Language
Adding Value to Your Data
Performance Tuning with Power BI Dataflows
Implementing Query Diagnostics

Power Query Cookbook
Contributors
About the author
About the reviewers
Preface
    Who this book is for
    What this book covers
    To get the most out of this book 
    Download the example code files
    Download the color images
    Conventions used
    Sections
        Getting ready
        How to do it…
        How it works…
        There's more…
        See also
    Get in touch
    Share Your Thoughts
Chapter 1: Getting Started with Power Query
    Technical requirements
    Installing a Power BI gateway
        Getting ready
        How to do it…
        How it works
    Authentication to data sources
        Getting ready
        How to do it…
        How it works
    Main challenges that Power Query solves
        Getting ready
        How to do it…
Chapter 2: Connecting to Fetch Data 
    Technical requirements
    Getting data and connector navigation 
        Getting ready
        How to do it...
    Creating a query from files
        Getting ready
        How to do it...
        How it works...
    Creating a query from a folder
        Getting ready
        How to do it...
        How it works...
    Creating a query from a database
        Getting ready
        How to do it...
        How it works...
    Creating a query from a website
        Getting ready
        How to do it...
        How it works...
Chapter 3: Data Exploration in Power Query 
    Technical requirements
    Exploring Power Query Editor 
        Getting ready
        How to do it…
    Managing columns
        Getting ready
        How to do it…
    Using data profiling tools
        Getting ready
        How to do it…
    Using Queries pane shortcuts
        Getting ready
        How to do it…
    Using Query Settings pane shortcuts 
        Getting ready
        How to do it…
    Using Schema view and Diagram view 
        Getting ready
        How to do it…
Chapter 4: Reshaping Your Data
    Technical requirements
    Formatting data types
        Getting ready
        How to do it
    Using first rows as headers
        Getting ready
        How to do it
    Grouping data
        Getting ready
        How to do it
    Unpivoting and pivoting columns
        Getting ready
        How to do it
    Filling empty rows
        Getting ready
        How to do it
    Splitting columns
        Getting ready
        How to do it
    Extracting data
        Getting ready
        How to do it
    Parsing JSON or XML
        Getting ready
        How to do it
    Exploring artificial intelligence insights
        Getting ready
        How to do it
Chapter 5: Combining Queries for Efficiency
    Technical requirements
    Merging queries
        Getting ready
        How to do it…
    Joining methods
        Getting ready
        How to do it…
    Appending queries
        Getting ready
        How to do it…
    Combining multiple files
        Getting ready
        How to do it…
    Using the Query Dependencies view
        Getting ready
        How to do it…
Chapter 6: Optimizing Power Query Performance
    Technical requirements
    Setting up parameters
        Getting ready
        How to do it…
    Filtering with parameters
        Getting ready
        How to do it…
    Folding queries
        Getting ready
        How to do it…
    Leveraging incremental refresh and folding
        Getting ready
        How to do it…
    Disabling query load
        Getting ready
        How to do it…
Chapter 7: Leveraging the M Language
    Technical requirements
    Using M syntax and the Advanced Editor 
        Getting ready
        How to do it…
    Using M and DAX – differences 
        Getting ready
        How to do it…
    Using M on existing queries
        Getting ready
        How to do it…
    Writing queries with M
        Getting ready
        How to do it…
    Creating tables in M
        Getting ready
        How to do it…
    Leveraging M – tips and tricks
        Getting ready
        How to do it…
Chapter 8: Adding Value to Your Data
    Technical requirements
    Adding columns from examples
        Getting ready
        How to do it…
    Adding conditional columns
        Getting ready
        How to do it…
    Adding custom columns
        Getting ready
        How to do it…
    Invoking custom functions
        Getting ready
        How to do it…
    Clustering values
        Getting ready
        How to do it…
Chapter 9: Performance Tuning with Power BI Dataflows
    Technical requirements
    Using Power BI dataflows 
        Getting ready
        How to do it...
    Centralizing ETL with dataflows
        Getting ready
        How to do it...
    Building dataflows with Power BI Premium capabilities
        Getting ready
        How to do it...
    Understanding dataflow best practices
        Getting ready
        How to do it...
Chapter 10: Implementing Query Diagnostics
    Technical requirements
    Exploring diagnostics options
        Getting ready
        How to do it…
    Managing a diagnostics session
        Getting ready
        How to do it…
    Designing a report with diagnostics results
        Getting ready
        How to do it…
        There's more…
    Using Diagnose as a Power Query step
        Getting ready
        How to do it…
    Why subscribe?
Other Books You May Enjoy
    Packt is searching for authors like you
    Share Your Thoughts

Donate to keep this site alive

To access the Link, solve the captcha.

How to download source code?

1. Go to: https://github.com/PacktPublishing

2. In the Find a repository… box, search the book title: Power Query Cookbook: Use effective and powerful queries in Power BI Desktop and Dataflows to prepare and transform your data, sometime you may not get the results, please search the main title.