0
Home / Catalog / The Ultimate Power Query Cookbook for Power BI and Excel
  
The Ultimate Power Query Cookbook for Power BI and Excel  
Leveraging Power Query for collecting, combining and transforming your data (English Edition)
Author(s): Dominick Raimato
Published by BPB Publications
ISBN: 9789355517395
Pages: 398
https://doi.org/9789355517395

EBOOK (EPUB)

ISBN: 9789355517395   Price: INR 699.00
  
 

Description

Table of contents

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