“The Ultimate Power Query Cookbook for Power BI and Excel” serves up easy-to-follow recipes that transform data into meaningful insights. You will learn to clean messy files, combine datasets, and even use AI magic to Power BI and Excel. This book will walk you through the basics of getting connected to data with Power Query. You will understand how to ingest data from files, folders, databases, websites, APIs, and other third party sources. Once connected, you will learn how to transform the data so it is ready for your use. We will clean up columns, filter, replace, extract, and classify data in Power Query to meet your needs. The book offers over 100 practical recipes, ensuring you understand each step with clear explanations and examples. Lastly, we will go over advanced techniques to help optimize and simplify your transformations allowing fast refreshes all while helping you manage them in the future. This book will help you know how to apply these techniques and recipes to your data all while understanding the implications of making certain decisions. This will enable you to have better conversations with other data professionals who are providing data for your use.

• Cover
• Title Page
• Copyright Page
• Dedication Page
• About the Author
• About the Reviewer
• Acknowledgement
• Preface
• Table of Contents
• 1. Introduction to Power Query
  • Introduction
  • Structure
  • Objectives
  • Why use Power Query?
  • What is Power Query?
  • Leveraging Power Query with Excel
  • Leveraging Power Query with Power BI Desktop
  • Overview of the Power Query graphical interface
  • Overview of advanced editor
  • Maximizing Power Query
  • Conclusion
• 2. Connect to File-Based Data Sources
  • Introduction
  • Structure
  • Objectives
  • Understanding file-based connections
  • Connecting to tabular files
  • Connecting to structured files
  • Connecting to folders
  • Refreshing file-based connections
  • Conclusion
• 3. Connect to Web-Based Data Sources
  • Introduction
  • Structure
  • Objectives
  • Connecting to SharePoint lists
  • Connecting to website data
  • Connecting to web APIs
  • Connecting to files in SharePoint
  • Connecting to folders in SharePoint
  • Connecting to OData sources
  • Connecting to Dynamics 365
  • Conclusion
• 4. Connect to Database Sources
  • Introduction
  • Structure
  • Objectives
  • Query folding
  • Data connectivity modes
  • Connecting to Microsoft SQL server
  • Connecting to Azure Synapse Analytics
  • Connecting to Azure Cosmos DB
  • Connecting to SQL Server Analysis Services
  • Connecting to Snowflake
  • Connecting to Google BigQuery
  • Connecting to Amazon Redshift
  • Connecting to ODBC
  • Connecting to Dataflows in Power BI
  • Connecting to Azure Tables
  • Connecting to Azure Blob
  • Connecting to Dremio
  • Conclusion
• 5. Connect to Third-Party Data Sources
  • Introduction
  • Structure
  • Objectives
  • Understanding third-party connectors
  • Connecting to Salesforce
  • Connecting to Zendesk
  • Connecting to Azure DevOps
  • Connecting to Google Analytics
  • Connecting to Smartsheet
  • Connecting to LinkedIn Sales Navigator
  • Considerations for third party connectors
  • What if there is no connector?
  • Conclusion
• 6. Managing Data Types
  • Introduction
  • Structure
  • Objectives
  • Understanding data types in Power BI
  • Detecting data types
  • Assigning text data types
  • Assigning numeric data types
  • Assigning date/time data types
  • Assigning logical data types
  • Updating data types in bulk
  • Combining data type transformations
  • Understanding efficiencies with data types
  • Optimizing data types upstream
  • Conclusion
• 7. Transforming Columns
  • Introduction
  • Structure
  • Objectives
  • Why transformations in Power Query?
  • Promoting first row as headers
  • Renaming columns
  • Formatting text in columns
  • Extracting values from columns
  • Merging columns
  • Applying mathematical functions
  • Date/Time transformations
  • Moving columns in a query
  • Pivot/unpivot columns
  • Maintaining query folding in transformations
  • Conclusion
• 8. Cleansing Columns
  • Introduction
  • Structure
  • Objectives
  • Column profile tools
  • Filtering data
  • Replace values in column
  • Replace errors in a column
  • Using fill up/down
  • Remove duplicate values
  • Remove extra columns
  • Remove top/bottom rows
  • Considerations with cleansing columns
  • Conclusion
• 9. Creating New Columns
  • Introduction
  • Structure
  • Objectives
  • When to create columns
  • Creating columns in Power Query
  • Transformations as new columns
  • Splitting columns
  • Duplicating columns
  • Creating conditional columns
  • Creating custom columns
  • Creating columns from example
  • Introduction to advanced columns
  • Changing data types
  • Cautions with creating columns
  • Conclusion
• 10. Combining and Manipulating Queries
  • Introduction
  • Structure
  • Objectives
  • Why aggregate data in Power Query?
  • Simplify with group by and aggregation
  • Use cases for aggregating data
  • Understanding join types
  • Merging queries
  • Understanding Fuzzy matching
  • Use cases for merging queries
  • Preparing queries for appending
  • Appending queries
  • Use cases for appending
  • Suppressing queries
  • Conclusion
• 11. Using Python, R, and AI
  • Introduction
  • Structure
  • Objectives
  • Use cases for Python, R, and AI
  • What is Python?
  • Transforming a column using Python
  • What is R?
  • Transforming a dataset using R
  • What is AI?
  • Leveraging text analytics functions
  • Leveraging vision functions
  • Leveraging Azure machine learning functions
  • Considerations with Python, R, and AI
  • Conclusion
• 12. Indexing
  • Introduction
  • Structure
  • Objectives
  • What is an index column?
  • Understanding star schemas
  • Understanding keys
  • Creating an index
  • Creating a dimension from an index
  • Optimizing queries to reduce bloat
  • Conclusion
• 13. Parameters
  • Introduction
  • Structure
  • Objectives
  • Understanding parameters in Power Query
  • Creating parameters in Power Query
  • Creating parameter values from existing queries
  • Parameters and incremental refresh
  • Updating parameters
  • Use cases for parameters
  • Conclusion
• 14. Functions
  • Introduction
  • Structure
  • Objectives
  • Understanding functions in Power Query
  • Creating a function from a query
  • Adding parameters to a function
  • Creating a custom column from a function
  • Convert time zones with a function
  • Perform lookups with a function
  • Create a date table with a function
  • Use cases for functions
  • Conclusion
• 15. Advanced Web Connections
  • Introduction
  • Structure
  • Objectives
  • Review of web APIs
  • Connect to web APIs using POST method
  • Connect to web APIs using OAuth2
  • Manage paging with a skip parameter
  • Manage paging with a skip token
  • Considerations with advanced connections
  • Conclusion
• 16. Manipulating Supporting Queries
  • Introduction
  • Structure
  • Objectives
  • Review unifying files in a single query
  • Understanding the transform file query group
  • Updating your sample file
  • Managing transformations
  • Adding new columns to queries
  • Common challenges with supporting queries
  • Conclusion
• Index