Learn Power Query

Learn Power Query

by Linda Foulkes, Warren Sparrow
Released July 2020
Publisher(s): Packt Publishing
ISBN: 9781839219719

Explore a preview version of Learn Power Query right now.

O’Reilly members get unlimited access to live online training experiences, plus books, videos, and digital content from 200+ publishers.

Start your free trial

Book description

Discover how you can combine data from various sources to create data models to suit your business requirements with the help of this clear and concise guide

Key Features

  • Understand how Power Query overcomes the shortcomings of Excel Power Pivot in handling complex data
  • Create customized dashboards and multi-dimensional reports using Power Query and Power BI
  • Learn the Power Query M language and write advanced queries using custom functions

Book Description

Power Query is a data connection technology that allows you to connect, combine, and refine data from multiple sources to meet your business analysis requirements. With this Power Query book, you'll be empowered to work with a variety of data sources to create interactive reports and dashboards using Excel and Power BI.

You'll start by learning how to access Power Query across different versions of Excel and install the Power BI engine. After you've explored Power Pivot, you'll see why Excel users find it challenging to clean data in Power Pivot and learn how Power Query can help to tackle the problem. The book will show you how to transform data using the Query Editor and write functions in Power Query. A dedicated section will focus on functions such as IF, Index, and Modulo, and creating parameters to alter query paths in a table. You'll also work with dashboards, get to grips with multi-dimensional reporting, and create automated reports. As you advance, you'll cover the M formula language in Power Query, delve into the basic M syntax, and write the M query language with the help of examples such as loading all library functions offline in Excel and Power BI. Finally, the book will demonstrate the difference between M and DAX and show how results are produced in M.

By the end of this book, you'll be ready to create impressive dashboards and multi-dimensional reports in Power Query and turn data into valuable insights.

What you will learn

  • Convert worksheet data into a table format ready for query output
  • Create a dynamic connection between an Access database and Excel workbook
  • Reshape tabular data by altering rows, columns, and tables using various Power Query tools
  • Create new columns automatically from filenames and sheet tabs, along with multiple Excel data files
  • Streamline and automate reports from multiple sources
  • Explore different customization options to get the most out of your dashboards
  • Understand the difference between the DAX language and Power Query's M language

Who this book is for

This Power Query book is for business analysts, data analysts, BI professionals, and Excel users looking to take their skills to the next level by learning how to collect, combine, and transform data into insights using Power Query. Working knowledge of Excel and experience in constructing and troubleshooting Excel formulas and functions is expected.

Publisher resources

Download Example Code

Table of contents

  1. Learn Power Query
  2. Why subscribe?
  3. Contributors
  4. About the authors
  5. Acknowledgements
  6. About the reviewer
  7. Packt is searching for authors like you
  8. 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. Code in Action
    6. Download the color images
    7. Conventions used
    8. Get in touch
    9. Reviews
  9. Section 1: Overview of Power Pivot and Power Query
  10. Chapter 1: Installation and Setup
    1. Technical requirements
    2. Introducing Power Pivot
      1. Power Pivot Office versions and differences
    3. Introduction to Power BI
      1. Power BI versions and differences
      2. Mac/Apple
    4. Introduction to Power Query
      1. Features of Power Query
      2. Power Query Office versions and differences
      3. Installing Power Query in Office 2013 and 2010
      4. Activating my Power Query/Pivot again
      5. Launching Power Query, Power Pivot, and Power BI
      6. Launching Power Query within Excel
      7. Accessing Power Query from Power BI
    5. Summary
  11. Chapter 2: Power Pivot Basics, Inadequacies, and Data Management
    1. Technical requirements
    2. Creating a pivot table
      1. Creating a Power Pivot
      2. Creating a table in Excel
      3. Adding tables to the data model
      4. Creating relationships between tables
    3. Power Query to the rescue
      1. Creating a calculated column
      2. Creating a calculated field
      3. Creating a Power Pivot table
    4. Shortcomings of Power Pivot
      1. Problem 1 – selecting multiple items
      2. Problem 2 – Power Pivot preview
      3. Problem 3 – calculated fields
      4. Problem 4 – Microsoft Office versions
    5. Summary
  12. Chapter 3: Introduction to the Power Query Interface
    1. Technical requirements
    2. The Power Query window and its elements
      1. The main ribbon and tabs
      2. The navigation pane or the Queries list
      3. Data table preview
      4. The Query Settings pane
      5. Working with APPLIED STEPS
      6. Investigating the View settings
      7. Using Advanced Editor
    3. Creating a basic power query
    4. Discovering the Load To… options
      1. Changing the default custom load settings
      2. Loading queries to the worksheet manually
    5. Data profiling tips
      1. Column profile
      2. Column quality
      3. Column distribution
    6. Summary
  13. Chapter 4: Connecting to Various Data Sources Using Get & Transform
    1. Technical requirements
    2. A brief introduction to databases
    3. Connecting from a table or range
    4. Connecting data to the web
    5. Connecting from a relational database
      1. Connecting through Excel's Get & Transform tool
      2. Connecting through Power BI
    6. Understanding custom connections
      1. Connecting from Workbook
      2. Connecting from a folder
    7. Exploring data source settings
      1. From Excel
    8. Summary
  14. Section 2: Power Query Data Transformations
  15. Chapter 5: Transforming Power Query Data
    1. Technical requirements
    2. Turning data with the unpivot and pivot tools
      1. Refreshing data
    3. Basic column and row tools
      1. Removing columns
      2. Removing top or bottom rows
      3. Using the index column
      4. Creating a conditional column with the if…then…else statement
      5. Filtering data using the And/Or conditions
      6. Creating single-criteria filters
      7. Creating dynamic multiple-criterion filters
      8. Removing duplicate rows
      9. Replacing null values
      10. Working with the header row
      11. Splitting columns
    4. Merging and appending tools
      1. Merging columns using combine
      2. Merging text and values into one column
      3. Appending (combining) tables
    5. Grouping data
    6. Working with extraction tools
      1. Extracting an age from a date
      2. Extracting columns
      3. Using the extract column features
    7. Summary
  16. Chapter 6: Advanced Power Queries and Functions
    1. Technical requirements
    2. Writing an IF function in Power Query
    3. Creating a parameter table for queries
      1. Changing the monthly data source
    4. Understanding the Index and Modulo functions
      1. Beginning with the modulo function
      2. Understanding index functions
    5. Appending multiple files
    6. Appending multiple tabs
    7. Summary
  17. Chapter 7: Automating Reports in Power Query
    1. Technical requirements
    2. Understanding the storage modes and dataset types
      1. Viewing the Power BI Desktop Storage mode setting
    3. Choosing the Import storage mode setting
      1. Looking at where Power BI stores data
      2. Investigating whether Microsoft SQL Server Analysis Services is running
    4. Understanding the Power BI refresh types
      1. Learning how to refresh a OneDrive connection
      2. Viewing and performing a OneDrive refresh
      3. Setting a scheduled refresh
      4. Incremental refresh
      5. Automatic page refresh
      6. Dataflow refresh
    5. Summary
  18. Chapter 8: Creating Dashboards with Power Query
    1. Technical requirements
    2. Creating a basic power pivot and PivotChart
    3. Using Power BI to collect and connect data
      1. Combining files
    4. Using Power BI to add data to a data model
    5. Selecting data visualization, a dataset, and an appropriate chart
    6. Saving, publishing, and sharing a dashboard
    7. Sharing a dashboard
      1. Best practices
    8. Summary
  19. Section 3: Learning M
  20. Chapter 9: Working with M
    1. Technical requirements
    2. The beginnings of M
      1. Understanding the M syntax and learning how to write M
    3. Using #shared to return library functions
      1. Text data types
      2. Number data types
      3. Lists
      4. Records
      5. Table data types
      6. Searching for relevant data
    4. Importing a CSV file using M
    5. Summary
  21. Chapter 10: Examples of M Usage
    1. Technical requirements
    2. Merging using the concatenate formula
    3. Data type conversions
    4. Setting up a SQL server
    5. Installing SQL Server Management Studio
    6. Using parameters
      1. Parameterizing a data source
      2. Using parameters in the Data view
    7. Summary
  22. Chapter 11: Creating a Basic Custom Function
    1. Technical requirements
    2. Creating a function manually using M
      1. Changing the file path of the query to a local path
      2. Creating the function manually
      3. Testing the parameter function
      4. Creating a date/time column using three M functions
    3. Summary
  23. Chapter 12: Differences Between DAX and M
    1. Technical requirements
    2. Learning about the DAX and M functionality
    3. Constructing DAX syntax
      1. Constructing DAX formulas in Excel
      2. Using IntelliSense
      3. Creating a DAX formula
    4. Understanding the DAX formula and storage engine
    5. Creating a calculated column
    6. Creating calculated measures
      1. Using quick measures
      2. Formulating a DAX measure from scratch
      3. Organizing measures
    7. Summary
  24. Other Books You May Enjoy
    1. Leave a review - let other readers know what you think

Product information

  • Title: Learn Power Query
  • Author(s): Linda Foulkes, Warren Sparrow
  • Release date: July 2020
  • Publisher(s): Packt Publishing
  • ISBN: 9781839219719