Power Query Cookbook

Power Query Cookbook

by Andrea Janicijevic
Released October 2021
Publisher(s): Packt Publishing
ISBN: 9781800569485

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

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.

Table of contents

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

Product information

  • Title: Power Query Cookbook
  • Author(s): Andrea Janicijevic
  • Release date: October 2021
  • Publisher(s): Packt Publishing
  • ISBN: 9781800569485