Power Query Cookbook E-Book

Andrea Janicijevic

• Herausgeber: Packt Publishing
• Kategorie: Wissenschaft und neue Technologien
• Sprache: Englisch

Seitenzahl: 212

Veröffentlichungsjahr: 2021

Beschreibung

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.

Leseprobe

Power Query Cookbook

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

Andrea Janicijevic

BIRMINGHAM—MUMBAI

Power Query Cookbook

Copyright © 2021 Packt Publishing

All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.

Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the author(s), nor Packt Publishing or its dealers and distributors, will be held liable for any damages caused or alleged to have been caused directly or indirectly by this book.

Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.

Group Product Manager: Kunal Parikh

Publishing Product Manager: Ali Abidi

Senior Editor: Roshan Kumar

Content Development Editor: Tazeen Shaikh

Technical Editor: Rahul Limbachiya

Copy Editor: Safis Editing

Project Coordinator: Aparna Ravikumar Nair

Proofreader: Safis Editing

Indexer: Subalakshmi Govindhan

Production Designer: Prashant Ghare

First published: October 2021

Production reference: 1070921

Published by Packt Publishing Ltd.

Livery Place

35 Livery Street

Birmingham

B3 2PB, UK.

ISBN 978-1-80056-948-5

www.packt.com

To my family, who have always supported me during my studies and my professional experience and have been key in me becoming the woman I am today. To my colleague, Patrik Borosch, who introduced me to this opportunity, and to my manager, Zoran Draganic, who gave me the freedom to pursue this exciting journey.

– Andrea Janicijevic

Contributors

About the author

Andrea Janicijevic is a cloud solution architect and works in the world of analytics and business intelligence, constantly expanding her knowledge in the field of data. From the outset, she has been working on analytics platforms, helping clients to better adopt cloud technology across a wide range of industries and company sizes.

She studied economics and management of innovation and technology at Bocconi University in Milan and during her studies, she started working at Microsoft in 2018. She began working with the Microsoft analytics platform, including Power BI, becoming a trusted technical advisor for business and technical users. She later started collaborating with Packt, accepting the challenge of sharing her experience with Power Query.

About the reviewers

Patrik Borosch is a cloud solution architect for data and AI at Microsoft Switzerland GmbH. He has more than 25 years of BI and analytics development, engineering, and architecture experience and is a Microsoft Certified Data Engineer and a Microsoft Certified AI Engineer. Patrik has worked on numerous significant international data warehouse, data integration, and big data projects. Through this, he has built and extended his experience in all facets, from requirements engineering to data modeling and ETL, all the way to reporting and dashboarding. At Microsoft Switzerland, he supports customers in their journey into the analytical world of the Azure Cloud.

Michiel Rozema is one of Europe's top Power BI experts, living in the Netherlands. He holds a master's degree in mathematics and has worked in the IT industry for over 25 years as a consultant and manager. Michiel was the data insight lead at Microsoft Netherlands for 8 years, during which time he launched Power BI in the country. He is the author of two Dutch books on Power Pivot and Power BI, and is the author of the Extreme DAX title with Packt Publishing. Michiel is one of the founders of the Dutch Power BI user group and the initiator of the Power BI Summer School, and has been a speaker at many conferences on Power BI. He has been awarded the Microsoft MVP award since 2019 and, together with fellow MVP Henk Vlootman, runs the consultancy firm Quanto, specializing in Power BI.

Table of Contents

Preface

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…

Other Books You May Enjoy

Chapter 1: Getting Started with Power Query

Power Query is a data preparation tool that enables data engineers and business users to connect, reshape, enrich, and transform their data. This allows them to facilitate relevant business insights analysis. Power Query is a technology that strengthens self-service business intelligence with an intuitive and consistent experience. It consists of a graphical interface that facilitates the connection to data sources and the application of different ranges of transformation.

Power Query is not a standalone tool; it can be used inside different tools in two different versions: Power Query Desktop and Power Query Online. The first version is available in Excel, Power BI, and SQL Server Analysis Services, while the second is available in the Power BI service, Power Apps, Power Automate, Azure Data Factory, Azure Synapse, and Dynamics 365 Customer Insights. Depending on where Power Query is used, users will be able to store reshaped data in different ways: publish datasets to the Power BI service, load data in Azure Data Lake with Common Data Model formatting, and load transformed data to the Dataverse.

The following recipes will be covered in this chapter:

Installing a Power BI gatewayAuthentication to data sourcesMain challenges that Power Query solves

Technical requirements

In this chapter, you will be using the following:

Power BI Desktop: https://www.microsoft.com/en-us/download/details.aspx?id=58494Power BI Pro License: https://powerbi.microsoft.com/en-us/power-bi-pro/Power BI gateway: https://powerbi.microsoft.com/en-us/gateway/

The minimum requirements for installation are as follows:

.NET Framework 4.6 (Gateway release August 2019 and earlier).NET Framework 4.7.2 (Gateway release September 2019 and later)A 64-bit version of Windows 8 or a 64-bit version of Windows Server 2012 R2 with current TLS 1.2 and cipher suites4 GB of disk space for performance monitoring logs

You can find the data resources referred to in this chapter at https://github.com/PacktPublishing/Power-Query-Cookbook/tree/main/Chapter01.

Installing a Power BI gateway

Power BI users often need to work with data from on-premises sources, such as filesystems, local files available on a PC, and databases not running on the cloud. In order to make this data securely available once the report is published to the web, a Power BI gateway needs to be installed. Microsoft offers two different types of gateway for different scenarios, and their setup can be customized according to specific enterprise configuration requirements, such as proxy, service account, communication, and high availability settings. Users can choose one of the following two types:

Standard (or enterprise) mode: This mode can be used to connect data sources to Power Platform services, Logic Apps, and Analysis Services by multiple users. It has to be run by users with admin rights and is meant for enterprise scenarios. Personal mode: This mode can be used by single users without the possibility of sharing the files. This version is available for Power BI only. If you want to quickly connect to an Excel file on your local machine and run tests without needing admin rights, this mode is for you. It is meant for testing purposes.

Customers need monitoring options and analysis to decide whether to scale up or scale down the gateway server to improve data movement performance. This recipe aims to help users to decide which type of gateway to install, and to assist with the configuration and monitoring options.

Getting ready

You can refer to this link to download a Power BI gateway: https://powerbi.microsoft.com/en-us/gateway/.

In this recipe, we are going to install the standard (enterprise) mode gateway on a local machine. It is recommended, though, to install the gateway on a server, especially in enterprise scenarios.

In this chapter, Power BI Desktop needs to be installed on a machine that has access to the data sources. Access to the Power BI service is also needed.

Download the data files on your local machine.

How to do it…

Once you have downloaded the Power BI gateway, you are ready to start the setup:

Define the default path for your gateway resources, accept the terms, and run the installation. Revise the minimum requirements for the machine where the setup is going to be done:

Figure 1.1 – On-premises data gateway installation

Enter your work or school Microsoft Office 365 account. This account has to be in the Azure Active Directory tenant, the one shared with Power BI. By entering your organizational account, you will be able to manage gateways and add multiple data sources using the Power BI service portal:

Figure 1.2 – On-premises gateway email admin

Click on Register a new gateway on this computer:

Figure 1.3 – Register a new gateway

Give a name to the gateway and create a Recovery key. This key is needed if you want to create a gateway cluster (a group of gateways), to migrate your existing gateway, or to take over the gateway's ownership. Once you set the key, click on Configure:

Figure 1.4 – Gateway recovery key definition

At this step, you can decide to change the default region to connect the gateway to cloud services. The default region is the one where your Power BI or Microsoft O365 tenant is located. If you want to change it, you'll select an Azure region, but make sure that the region is close to you.

Once the configuration is completed, you should end up with the following view:

Figure 1.5 – Running the gateway application interface

This will be the default view when you open the gateway application. You can monitor the health of the gateway and see what services can use the same gateway. This application allows the gateway admin to customize the configuration.

Managing the data gateway on the Power BI portal

Once you have installed the gateway and it is running on the machine, you can access https://powerbi.com and log in to the Power BI portal with the credentials you use to sign in to the gateway application. When you are logged in, complete the following steps to see how to manage the data gateway:

Go to Manage gateways and access the section where you can find the running gateway you set up before:

Figure 1.6 – Power BI service: Settings section

You will now be able to see gateways settings and administrators:

a) Administrators: If you installed the gateway, you will be an admin by default. You can use these sections to add other administrators:

Figure 1.7 – Managing gateways: adding administrators

b) Gateway Cluster Settings: On the left side, you can see