Search icon CANCEL
Subscription
0
Cart icon
Close icon
You have no products in your basket yet
Save more on your purchases!
Savings automatically calculated. No voucher code required
Arrow left icon
All Products
Best Sellers
New Releases
Books
Videos
Audiobooks
Learning Hub
Newsletters
Free Learning
Arrow right icon
$9.99 | ALL EBOOKS & VIDEOS
Save more on purchases! Buy 2 and save 10%, Buy 3 and save 15%, Buy 5 and save 20%
Power Query Cookbook
Power Query Cookbook

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

By Andrea Janicijevic
$39.99 $9.99
Book Oct 2021 412 pages 1st Edition
eBook
$39.99 $9.99
Print
$48.99 $33.99
Subscription
$15.99 Monthly
eBook
$39.99 $9.99
Print
$48.99 $33.99
Subscription
$15.99 Monthly

What do you get with eBook?

Product feature icon Instant access to your Digital eBook purchase
Product feature icon Download this book in EPUB and PDF formats
Product feature icon Access this title in our online reader with advanced features
Product feature icon DRM FREE - Read whenever, wherever and however you want
Buy Now
Table of content icon View table of contents Preview book icon Preview Book

Power Query Cookbook

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...

Technical requirements

In this chapter, you will be using the following:

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 suites
  • 4 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...

Authentication to data sources

Power Query provides different connector types to a wide range of data sources. These will be widely explained in the following chapter, but in this recipe, we will concentrate on how authentication works for data sources.

Each connector provides different kinds of authentication. There are six main types of authentication:

  • Anonymous
  • Windows
  • Basic
  • Organizational account
  • Microsoft account
  • Database

Depending on the type of connector and the Power Query version used, different combinations of these options will be available for the user.

Once you connect to your data source and you perform Power Query transformations, you will be able to change the type of authentication and the data source without losing the work you have done.

Getting ready

In this recipe, in order to test different types of connections, you need to have the following data sources to which you can connect:

  • An Azure SQL Database with...

Main challenges that Power Query solves

By going through the previous recipe, you had the chance to see the variety of transformations and preparation options that Power Query provides.

Power Query aims to solve some of the traditional challenges linked to data analysis:

  • Responding to the need for a low code tool for business analysts who need to make corrections quickly without doing things from scratch
  • Managing different data types and volumes with quick transformations
  • Having a consistent experience across platforms and enabling different users to collaborate even if they are using different tools

Getting ready

For this recipe, you need to open the PBIX file provided within the materials. Moreover, you need a Power BI Premium Capacity or a Power BI Embedded capacity allocated and linked to your Power BI tenant.

How to do it…

Starting from the first challenge, when Power Query was introduced in Excel, it changed the way business users used...

Left arrow icon Right arrow icon
Download code icon Download Code

Key benefits

  • 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

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

Product Details

Country selected

Publication date : Oct 15, 2021
Length 412 pages
Edition : 1st Edition
Language : English
ISBN-13 : 9781800569485
Vendor :
Microsoft
Category :
Concepts :

What do you get with eBook?

Product feature icon Instant access to your Digital eBook purchase
Product feature icon Download this book in EPUB and PDF formats
Product feature icon Access this title in our online reader with advanced features
Product feature icon DRM FREE - Read whenever, wherever and however you want
Buy Now

Product Details


Publication date : Oct 15, 2021
Length 412 pages
Edition : 1st Edition
Language : English
ISBN-13 : 9781800569485
Vendor :
Microsoft
Category :
Concepts :

Table of Contents

12 Chapters
Preface Chevron down icon Chevron up icon
1. Chapter 1: Getting Started with Power Query Chevron down icon Chevron up icon
2. Chapter 2: Connecting to Fetch Data Chevron down icon Chevron up icon
3. Chapter 3: Data Exploration in Power Query Chevron down icon Chevron up icon
4. Chapter 4: Reshaping Your Data Chevron down icon Chevron up icon
5. Chapter 5: Combining Queries for Efficiency Chevron down icon Chevron up icon
6. Chapter 6: Optimizing Power Query Performance Chevron down icon Chevron up icon
7. Chapter 7: Leveraging the M Language Chevron down icon Chevron up icon
8. Chapter 8: Adding Value to Your Data Chevron down icon Chevron up icon
9. Chapter 9: Performance Tuning with Power BI Dataflows Chevron down icon Chevron up icon
10. Chapter 10: Implementing Query Diagnostics Chevron down icon Chevron up icon
11. Other Books You May Enjoy Chevron down icon Chevron up icon

Customer reviews

Top Reviews
Rating distribution
Empty star icon Empty star icon Empty star icon Empty star icon Empty star icon 0
(0 Ratings)
5 star 0%
4 star 0%
3 star 0%
2 star 0%
1 star 0%
Top Reviews
No reviews found
Get free access to Packt library with over 7500+ books and video courses for 7 days!
Start Free Trial

FAQs

How do I buy and download an eBook? Chevron down icon Chevron up icon

Where there is an eBook version of a title available, you can buy it from the book details for that title. Add either the standalone eBook or the eBook and print book bundle to your shopping cart. Your eBook will show in your cart as a product on its own. After completing checkout and payment in the normal way, you will receive your receipt on the screen containing a link to a personalised PDF download file. This link will remain active for 30 days. You can download backup copies of the file by logging in to your account at any time.

If you already have Adobe reader installed, then clicking on the link will download and open the PDF file directly. If you don't, then save the PDF file on your machine and download the Reader to view it.

Please Note: Packt eBooks are non-returnable and non-refundable.

Packt eBook and Licensing When you buy an eBook from Packt Publishing, completing your purchase means you accept the terms of our licence agreement. Please read the full text of the agreement. In it we have tried to balance the need for the ebook to be usable for you the reader with our needs to protect the rights of us as Publishers and of our authors. In summary, the agreement says:

  • You may make copies of your eBook for your own use onto any machine
  • You may not pass copies of the eBook on to anyone else
How can I make a purchase on your website? Chevron down icon Chevron up icon

If you want to purchase a video course, eBook or Bundle (Print+eBook) please follow below steps:

  1. Register on our website using your email address and the password.
  2. Search for the title by name or ISBN using the search option.
  3. Select the title you want to purchase.
  4. Choose the format you wish to purchase the title in; if you order the Print Book, you get a free eBook copy of the same title. 
  5. Proceed with the checkout process (payment to be made using Credit Card, Debit Cart, or PayPal)
Where can I access support around an eBook? Chevron down icon Chevron up icon
  • If you experience a problem with using or installing Adobe Reader, the contact Adobe directly.
  • To view the errata for the book, see www.packtpub.com/support and view the pages for the title you have.
  • To view your account details or to download a new copy of the book go to www.packtpub.com/account
  • To contact us directly if a problem is not resolved, use www.packtpub.com/contact-us
What eBook formats do Packt support? Chevron down icon Chevron up icon

Our eBooks are currently available in a variety of formats such as PDF and ePubs. In the future, this may well change with trends and development in technology, but please note that our PDFs are not Adobe eBook Reader format, which has greater restrictions on security.

You will need to use Adobe Reader v9 or later in order to read Packt's PDF eBooks.

What are the benefits of eBooks? Chevron down icon Chevron up icon
  • You can get the information you need immediately
  • You can easily take them with you on a laptop
  • You can download them an unlimited number of times
  • You can print them out
  • They are copy-paste enabled
  • They are searchable
  • There is no password protection
  • They are lower price than print
  • They save resources and space
What is an eBook? Chevron down icon Chevron up icon

Packt eBooks are a complete electronic version of the print edition, available in PDF and ePub formats. Every piece of content down to the page numbering is the same. Because we save the costs of printing and shipping the book to you, we are able to offer eBooks at a lower cost than print editions.

When you have purchased an eBook, simply login to your account and click on the link in Your Download Area. We recommend you saving the file to your hard drive before opening it.

For optimal viewing of our eBooks, we recommend you download and install the free Adobe Reader version 9.