Search icon CANCEL
Arrow left icon
Explore Products
Best Sellers
New Releases
Books
Videos
Audiobooks
Learning Hub
Conferences
Free Learning
Arrow right icon
Arrow up icon
GO TO TOP
Power Query Cookbook

You're reading from   Power Query Cookbook Use effective and powerful queries in Power BI Desktop and Dataflows to prepare and transform your data

Arrow left icon
Product type Paperback
Published in Oct 2021
Publisher Packt
ISBN-13 9781800569485
Length 412 pages
Edition 1st Edition
Languages
Arrow right icon
Author (1):
Arrow left icon
Andrea Janicijevic Andrea Janicijevic
Author Profile Icon Andrea Janicijevic
Andrea Janicijevic
Arrow right icon
View More author details
Toc

Table of Contents (12) Chapters Close

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

What this book covers

Chapter 1, Getting Started with Power Query, focuses on what Power Query is, how the tool has evolved, and where you can find/use it across Microsoft platforms. Then, we share when to use Power Query within each Microsoft service (Power BI, Excel, Analysis Services, Power Apps, and Azure Data Factory), giving you an idea of how different types of users can leverage the same tool for different purposes.

Chapter 2, Connecting to Fetch Data, shows an overview of connectors. Some best practices will be shared on how to connect to some of the most common connector types. The main ones identified are connections to files, folders, databases, and websites.

Chapter 3, Data Exploration in Power Query, focuses on data exploration features in Power Query. You will learn how to choose a subset of data and explore data profiling tools and query dependencies in order to see at a glance what data you will be dealing with. You will see how to smartly use query and step panes with some shortcuts and examples. Moreover, the schema and diagram views will be explained.

Chapter 4, Reshaping Your Data, focuses on how users can reshape their data. Most common transformation tasks will be shown, sharing best practices that you can apply to a wide range of dataset types. Other than data manipulation and wrangling, some artificial intelligence features such as Cognitive Services will be shown.

Chapter 5, Combining Queries for Efficiency, describes how users can combine different queries. Merge and append possibilities will be explained. Best practices for multiple file combinations are also shown.

Chapter 6, Optimizing Power Query Performance, aims to clarify what features you can leverage to optimize Power Query queries. The setup of parameters and their use will be explained and we will take a deep dive into how to best approach query folding. It is important for you to understand how query folding works and how to apply it in an incremental refresh scenario.

Chapter 7, Leveraging the M Language, gives an outline of M coding. The differences from the DAX language will be clarified and knowledge about how to deal with existing and new queries using M code will be shared. The chapter will focus both on simpler and more advanced scenarios involving M code.

Chapter 8, Adding Value to Your Data, aims to teach you how you can enrich your data by using add column features that range from simpler ones such as columns from examples to more advanced ones such as custom columns. Custom functions will be explored and examples will be shared. The cluster values feature, one of the most recent, will be given as an example.

Chapter 9, Performance Tuning with Power BI Dataflows, explains Power BI dataflows. It will focus on how users can leverage the Power Query engine to create dataflows, how to schedule a refresh, and how to allow other users to build data models by using dataflows as central data sources. The aim is to clarify what the best practices are, such as to prefer dataflows over Power Query in other Microsoft tools, and which are the most common scenarios for their use.

Chapter 10, Implementing Query Diagnostics, focuses on Power Query diagnostics. There is a specific tool for that and it is useful to describe how to use it and interpret its output.

lock icon The rest of the chapter is locked
Register for a free Packt account to unlock a world of extra content!
A free Packt account unlocks extra newsletters, articles, discounted offers, and much more. Start advancing your knowledge today.
Unlock this book and the full library FREE for 7 days
Get unlimited access to 7000+ expert-authored eBooks and videos courses covering every tech area you can think of
Renews at €18.99/month. Cancel anytime