Book Image

Power Query Cookbook

By : Andrea Janicijevic
Book Image

Power Query Cookbook

By: Andrea Janicijevic

Overview of this book

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.

Related Content you might be interested in

Current Title:

Small book image
Power Query Cookbook
Andrea Janicijevic
Oct, 2021 | 412 pages
Small book image
Learn Power Query.
Warren Sparrow | Linda Foulkes
Jul, 2020 | 428 pages
Small book image
Expert Data Modeling with Power BI, Second Edition
Soheil Bakhshi
Apr, 2023 | 698 pages
Small book image
Expert Data Modeling with Power BI
Soheil Bakhshi
Jun, 2021 | 612 pages
Table of Contents (12 chapters)
Preface
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
Get in touch
Share Your Thoughts
1
Chapter 1: Getting Started with Power Query
Chapter 1: Getting Started with Power Query
Technical requirements
Installing a Power BI gateway
Authentication to data sources
Main challenges that Power Query solves
Free Chapter
2
Chapter 2: Connecting to Fetch Data
Chapter 2: Connecting to Fetch Data
Technical requirements
Getting data and connector navigation
Creating a query from files
Creating a query from a folder
Creating a query from a database
Creating a query from a website
3
Chapter 3: Data Exploration in Power Query
Chapter 3: Data Exploration in Power Query
Technical requirements
Exploring Power Query Editor
Managing columns
Using data profiling tools
Using Queries pane shortcuts
Using Query Settings pane shortcuts
Using Schema view and Diagram view
4
Chapter 4: Reshaping Your Data
Chapter 4: Reshaping Your Data
Technical requirements
Formatting data types
Using first rows as headers
Grouping data
Unpivoting and pivoting columns
Filling empty rows
Splitting columns
Extracting data
Parsing JSON or XML
Exploring artificial intelligence insights
5
Chapter 5: Combining Queries for Efficiency
Chapter 5: Combining Queries for Efficiency
Technical requirements
Merging queries
Joining methods
Appending queries
Combining multiple files
Using the Query Dependencies view
6
Chapter 6: Optimizing Power Query Performance
Chapter 6: Optimizing Power Query Performance
Technical requirements
Setting up parameters
Filtering with parameters
Folding queries
Leveraging incremental refresh and folding
Disabling query load
7
Chapter 7: Leveraging the M Language
Chapter 7: Leveraging the M Language
Technical requirements
Using M syntax and the Advanced Editor
Using M and DAX – differences
Using M on existing queries
Writing queries with M
Creating tables in M
Leveraging M – tips and tricks
8
Chapter 8: Adding Value to Your Data
Chapter 8: Adding Value to Your Data
Technical requirements
Adding columns from examples
Adding conditional columns
Adding custom columns
Invoking custom functions
Clustering values
9
Chapter 9: Performance Tuning with Power BI Dataflows
Chapter 9: Performance Tuning with Power BI Dataflows
Technical requirements
Using Power BI dataflows
Centralizing ETL with dataflows
Building dataflows with Power BI Premium capabilities
Understanding dataflow best practices
10
Chapter 10: Implementing Query Diagnostics
Chapter 10: Implementing Query Diagnostics
Technical requirements
Exploring diagnostics options
Managing a diagnostics session
Designing a report with diagnostics results
Using Diagnose as a Power Query step
Why subscribe?
11
Other Books You May Enjoy
Other Books You May Enjoy
Packt is searching for authors like you
Share Your Thoughts

Parsing JSON or XML

We may sometimes find mixed data structures within the same query. In this recipe, we will see how to deal with mixed data structures when single columns within a table contain JSON data structure. The same reasoning would apply to XML structure too.

Getting ready

For this recipe, you need to have Power BI Desktop running on your machine. You need to download the following file in a local folder: 

  • InternetSales CSV file

In this example, we will refer to the C:\Data folder. 

How to do it

Once you open your Power BI Desktop application, you are ready to perform the following steps:

  1. Click on Get Data and select the Text/CSV connector.
  2. Browse to your local folder where you downloaded the InternetSales CSV file and open it. The following window with a preview of the data will pop up. Click on Transform Data:
    Figure 4.49 – CSV data preview

    Figure 4.49 – CSV data preview

  3. Browse to the Home tab and click on Use First Row as Headers...
Previous Section
End of Section 10
Next Section