Recently Leila and I published a series of two books under the name of The Definitive Guide to Power Query in Power BI and Excel. Both books are now available in ebook and paperback format on Amazon and Google Play Books. In this blog, I’ll explain some details about the book, the agenda, and who is this book for;
Video
Who this book is for?
A good analytics solution combines a good data model, good data preparation, and good analytics and calculations. Reza has written another book about the Basics of modeling in Power BI and a book on Power BI DAX Simplified. This book is covering the data preparation and transformations aspects of it.
This book series is for you if you are building a Power BI solution. Even if you are just visualizing the data, preparation and transformations are an essential part of analytics. You need to have a cleaned and prepared data ready before visualizing it.
About the books
Any data analytics solution requires data population and preparation. With the rise of data analytics solutions these years, the need for this data preparation becomes even more essential. Power BI is a helpful data analytics tool that is used worldwide by many users.
As a Power BI (or Microsoft BI) developer, it is essential to learn how to prepare the data in the right shape and format needed. You need to learn how to clean the data and build it in a structure that can be modeled easily and used high performant for visualization.
Data preparation and transformation is the backend work. If you consider building a BI system as going to a restaurant and ordering food. The visualization is the food you see on the table nicely presented. The quality, the taste, and everything else come from the hard work in the kitchen. The part that you don’t see or the backend in the world of Power BI is Power Query.
You may already be familiar with other data preparation and transformation technologies, such as T-SQL, SSIS, Azure Data Factory, Informatica, etc. Power Query is a data transformation engine capable of preparing the data in the format you need. The good news is that to learn Power Query; you don’t need to know programming. Power Query is for citizen data engineers. However, this doesn’t mean that Power Query is not capable of performing advanced transformation.
Power Query exists in many Microsoft tools and services such as Power BI, Excel, Dataflows, Power Automate, Azure Data Factory, etc. Through the years, this engine became more powerful. These days, we can say this is essential learning for anyone who wants to do data analysis with Microsoft technology to learn Power Query and master it.
Unfortunately, because Power Query and data preparation is the kitchen work of the BI system, many Power BI users skip the learning of it. They become aware of it somewhere along their BI project. Once they get familiar with it, they realize there are tons of things they could have implemented easier, faster, and in a much more maintainable way using Power Query. In other words, they learn mastering Power Query is the key skill toward mastering Power BI.
We have been working with Power Query since the very early release of that in 2013, named Data Explorer, and wrote blog articles and published videos about it. The number of articles we published under this subject easily exceeds hundreds. Through those articles, some of the fundamentals and key learnings of Power Query are explained. We thought it is good to compile some of them in a book series.
This book is compiled into a series of two books, which will be followed by a third book later;
Getting started with Power Query in Power BI and Excel
Mastering Power Query in Power BI and Excel
Power Query dataflows (will be published later)
Although this book is written for Power BI and all the examples are presented using the Power BI. However, the examples can be easily applied to Excel, Dataflows, and other tools and services using Power Query.
What is the prerequisite?
There is no prerequisite for this book series. The first book gets you to start the journey from an absolute beginner in the world of Power Query. Being familiar with Power BI Desktop is helpful in some areas of the book, but it is not mandatory. You can learn the basics of Power BI Desktop by reading a simple blog article like this.
The book series starts with the book Getting Started which focuses on beginner to intermediate level usage of Power Query, and then the Mastering Power Query takes you to the next level with some advanced concepts and practices.
What is included in the book? Table of Contents?
Book 1: Getting Started with Power Query in Power BI and Excel
- Part 1: Getting Started with Power Query
- Chapter 1: Why Data Preparation and Transformation? Why Power Query?
- Chapter 2: Data Preparation for BI Solutions
- Chapter 3: What Is Power Query? Introduction to Data Mash-Up Engine of Power BI
- Chapter 4: Get Started with Power Query: Movies Data Mash-Up
- Part 2: Getting Data
- Chapter 5: Power BI Get Data From Excel: Everything You Need to Know
- Chapter 6: Power BI Get Data: From Azure SQL Database
- Chapter 7: Import Email Attachments Directly into a Power BI Report Using Power Query
- Chapter 8: Power Query Get Data From Folder
- Chapter 9: Get Data From Multiple Excel Files with Different Sheet Names into Power BI
- Chapter 10: Combine Multiple or All Sheets from an Excel File into a Power BI Solution Using Power Query Dynamically
- Chapter 11: Change the T-SQL Script of a Power BI Table without Add/Remove from the Model
- Chapter 12: Add or Edit Columns When Importing Data from Analysis Services into Power BI
- Chapter 13: Create a Table in Power BI Using Enter Data, and How to Edit It
- Part 3: Files and Folders
- Chapter 14: Change Excel Source in Power BI to OneDrive for Business – No Gateway Needed
- Chapter 15: Power BI Get Data from Multiple Files in a Folder on OneDrive for Business, No Gateway Needed
- Chapter 16: Get The List of Folders Only in Power BI Using Power Query
- Part 4: Text and Numeric Transformations
- Chapter 17: Trim Vs. Clean in Power BI and Power Query
- Chapter 18: Add Prefix and Suffix to a Text in Power BI: Generate Country Images and Pages
- Chapter 19: Split Column by Delimiter in Power BI and Power Query
- Chapter 20: Extract Parts of a Text Value in Power BI Using a Delimiter: Power Query Transformation
- Chapter 21: Get Images from Web Page into the Power BI Report Using Power Query
- Chapter 22: Remove Digits or Keep Them in Power BI Using Power Query
- Chapter 23: Make Your Numeric Division Faultless in Power Query
- Part 5: Date and Time Transformations
- Chapter 24: Script to Create Date Dimension in Power BI Using Power Query
- Chapter 25: Script for Creating Time Table in Power BI With Hours, Minutes, and Seconds Buckets
- Chapter 26: Age Calculation in Power BI Using Power Query
- Chapter 27: Age in Years Calculation That Works for Leap Year in Power BI Using Power Query
- Chapter 28: Flawless Date Conversion in Power Query
- Chapter 29: Offset Columns for the Date Table: Flexibility in Relative Date Filtering for Power BI
- Part 6: Filtering Data
- Chapter 30: Row Filtering in Power BI and Power Query: Remove Blank Rows Vs. Remove Empty
- Chapter 31: Only Get the Last Few Periods of Data into Power BI Using Power Query Filtering
- Chapter 32: Warning! Misleading Power Query Filtering
- Chapter 33: Remove Duplicate Doesn’t Work in Power Query for Power BI? Here Is The Solution!
- Part 7: Conditional Column
- Chapter 34: Conditional Column in Power BI Using Power Query; You Can Do Anything!
- Chapter 35: Create Customized Age Bins (Or Groups) in Power BI
- Part 8: Table Transformations
- Chapter 36: Pivot and Unpivot with Power BI
- Chapter 37: Convert Flat List to Table in Power BI; Pivot Without a Set Key Column Using Power Query
Book 2: Mastering Power Query in Power BI and Excel
- Part 1: Combining Data Tables
- Chapter 1: Append Vs. Merge in Power BI And Power Query
- Chapter 2: Reference Vs. Duplicate in Power BI; Power Query Back to Basics
- Chapter 3: Choose the Right Merge Join Type in Power BI
- Chapter 4: Relationship in Power BI With Multiple Columns
- Chapter 5: Combining Dimension Tables in Power BI Using Power Query
- Chapter 6: Creating A Shared Dimension in Power BI Using Power Query
- Chapter 7: Cartesian Product in Power Query: Multiply All Sets of All Pairs in Power BI
- Chapter 8: Find Mismatch Rows with Power Query in Power BI
- Chapter 9: Be Careful When Merging on Text Fields in Power BI Using Power Query
- Chapter 10: Dates Between Merge Join in Power Query
- Chapter 11: Age Banding in Power BI Using Power Query – Merge Queries Based on Between
- Part 2: Grouping and Aggregation
- Chapter 12: Grouping in Power Query; Getting the Last Item in Each Group
- Chapter 13: Count of Unique Values (DistinctCount) in Power BI Through Power Query Group By Transformation
- Chapter 14: Create Row Number for Each Group in Power BI Using Power Query
- Part 3: Fuzzy Operations
- Chapter 15: Fuzzy Matching in Power BI And Power Query; Match Based on Similarity Threshold
- Chapter 16: Fuzzy Grouping in Power BI Using Power Query
- Chapter 17: Fuzzy Clustering in Power BI Using Power Query: Finding Similar Values
- Part 4: Parameters and Custom Functions
- Chapter 18: Power Query Parameters Vs. What If Parameters: Power BI Implementation Use Cases
- Chapter 19: Custom Functions Made Easy in Power BI Desktop
- Chapter 20: Change the Source of Power BI Datasets Dynamically Using Power Query Parameters
- Part 5: Performance Tuning
- Chapter 21: Watch Your Steps! Power Query Performance Caution for Power BI
- Chapter 22: Not Folding; The Black Hole of Power Query Performance
- Chapter 23: Performance Tip for Power BI; Enable Load Sucks Memory Up
- Chapter 24: All You Need to Know About the Incremental Refresh in Power BI: Load Changes Only
- Part 6: Error Handling, Exception Reporting, And Data Profiling
- Chapter 25: Exception Reporting in Power BI: Catch the Error Rows in Power Query
- Chapter 26: Exception Reporting in Power Query and Power BI; Part 2 Catching Error Rows for All Columns in The Table
- Chapter 27: Know Your Data Better by Column Profiling in Power BI Using Power Query
- Chapter 28: Create A Profiling Report in Power BI: Give the End User Information About the Data
- Chapter 29: Get the Error Count with The Profiling Data of Power BI Data Table Using Power Query
- Part 7: Advanced Analytics Using Power Query
- Chapter 30: Time Series Anomaly Detection in Power BI Using Cognitive Service and Power Query
- Chapter 31: Power BI And Azure ML Make Them Work with Power Query
- Chapter 32: Text Entity Extraction in Power BI, Text Analytics Service
- Part 8: Power Query Formula Language: M
- Chapter 33: Power BI Quick Tip: The Formula Bar in Power Query
- Chapter 34: Basics of M: Power Query Formula Language
- Chapter 35: M Or Dax? That Is the Question!
- Chapter 36: Basics of Value Structures in M – Power Query Formula Language
- Chapter 37: Writing Custom Functions in Power Query M
- Chapter 38: Day Number of Year, Power Query Custom Function
- Chapter 39: Power Query Function That Returns Multiple Values
- Chapter 40: Fibonacci Sequence: Understanding the Power Query Recursive Function for Power BI
- Chapter 41: Power Query Library of Functions; Shared Keyword
- Chapter 42: Get A List of Queries in Power BI
- Part 9: Table and List Functions
- Chapter 43: Pre-Concatenate List of Values in Power BI Using Power Query
- Chapter 44: Search for A Column in The Entire Database with Table.ColumnNames in Power Query and Power BI
- Chapter 45: List.Accumulate Hidden Gem of Power Query List Functions in Power BI
- Chapter 46: Remove Columns with Specific Patterns Name in Power BI Using Power Query
- Chapter 47: Export Data from Power Query to Local Machine or SQL Server Using R Scripts
- Chapter 48: Generate Random List of Numbers in Power BI Dataset Using Power Query
Some details;
- Book 1 includes 8 parts, 37 chapters, and 285 pages.
- Book 2 includes 9 parts, 48 chapters, and 417 pages
Available on Amazon in Kindle (ebook) and Paperback format
Both paperback and ebook versions are available now through Amazon;
Paperback Quality
If the paperback version looks a bit pricy on your end, it is because we used the premium all-color printing option, the best printing option available on Amazon books. All the interior images are colored so that you can understand the concepts more easily. Here is an example of the interior of our books (the picture belongs to our previous books Power BI DAX Simplified because we haven’t received our paperback version of the Power Query books yet! :))
Available on Google Play books as ebook
If some of you don’t have access to Amazon purchase in your city or country, we published the books on Google Play books too, which is available in 66 countries.
Bestseller New Release on Amazon
We were humbled and honored again to see our books listed as best-seller New Release in the Amazon Business Intelligence books category. Thanks to you all who made that happen.
John Doe
Jedi Master
“What is the point of being alive if you don’t at least try to do something remarkable?”
Check out our other books
We have written over 10 books on Power BI Subjects, and some of them are available for free. The screenshot below is only a few of those. Check out the complete list of books by Leila and me here:
Your feedback is most welcome
If you read these books and like them or don’t like them, I love to hear from you. Your feedback would make the next edition of these books and other books better. I thank you for that.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.