دانلود کتاب یادگیری پاور کوئری (Power Query) -ساده‌سازی پاکسازی و تحلیل داده‌ها با قدرتمندترین ابزار اکسل

Power Query has become one of the most important tools in Excel for anyone working with data. As organizations collect and process more information than ever before, the ability to transform data directly within Excel is no longer optional. Power Query offers both simplicity through its interface and power through the M language, making it a critical skill for modern data professionals. This book is organized to take readers from foundational transformations all the way to advanced scenarios. Early chapters introduce essential commands and logic, while later sections explore parameterization, error handling, performance, recursion, and custom functions. The material is structured to build confidence step by step, with each topic preparing the ground for the next. Throughout the book, examples are grounded in practical use cases rather than abstract theory. Readers will see how Power Query can be applied to real business problems, and how writing M code directly can unlock flexibility and efficiency beyond what the interface offers. The goal is not just to follow steps, but to understand the why behind the transformations. This book is written for Excel users who want to move beyond formulas and for analysts who need better ways to prepare and automate data transformation. It also supports trainers and professionals looking to learn more about Power Query and gain clarity in explaining its logic to others. By the final chapter, readers will be able to design streamlined, automated query workflows with confidence. They will not only know how to use the GUI, but how to write and refactor M code, manage performance, and build transformations that scale. General introduction to the book followed by, Chapter 1: Getting Started with Power Query – Introduces Power Query as a core tool for importing, transforming, and automating data work in Excel. Covers how to access the interface, connect to sources like text files or websites, and apply basic transformations. Emphasizes the importance of proper data structure and shows how to begin building repeatable, efficient workflows. Chapter 2: Advanced Data Connections and Imports – Shows how to connect Power Query to structured and semistructured sources, including tables, named ranges, folders, and Access databases. Demonstrates techniques for handling inconsistent headers, filtering files and hidden elements, and dynamically importing content. Ends with practical guidance on setting up automatic refresh using both Power Query settings and VBA. Chapter 3: Combining Data Queries – Covers the core techniques for combining datasets using Power Query. Readers learn the difference between appending rows and merging columns, along with various join types such as inner, outer, anti, and fuzzy merges. Practical use cases include importing multi-year sales data, building price lookups, aggregating invoice details, and matching fuzzy city names. The chapter also explores advanced operations like self-joins, list comparisons, and transformation tables. Chapter 4: Grouping Data – Organize and summarize information using various grouping techniques in Power Query. From simple aggregations to dynamic product lists, rankings with ties, and local grouping, this chapter covers the full range of Group By operations. Explore how to apply functions like List.Modes and Table.Group with GroupKind.Local, while ensuring correct data types for reliable and efficient analysis. Chapter 5: Pivot and Unpivot – Reshape complex, reportstyle tables into structured datasets ready for analysis. Follow practical examples that guide you through unpivoting irregular headers, turning repeated rows into columns, and adjusting fields to support consistent and automated transformations. Chapter 6: Adding Columns – Perform transformations that enrich your data, such as calculating and rounding discounts, splitting fields by custom logic, and converting durations into time-based outputs. Use Column From Examples to define complex logic through patterns, creating structured and readable transformations grounded in real-world needs. Chapter 7: Logical Operations and Conditional Columns – Explores the power of conditional logic in Power Query through real-world applications like calculating overtime, assigning bonuses, and grading student scores. Demonstrates how to use logical operators, nested ifs, and M functions to automate decisions and handle exceptions. Covers practical methods for comparing rows, managing errors, and analyzing absence periods using both interface tools and advanced M code. Chapter 8: Parameters and Query Parameterization – Covers how to create and use parameters in Power Query to build dynamic and reusable queries. Step-by-step examples show how to extract values from cells, use drill down for precise data targeting, and replace hardcoded file paths. Practical guidance is given for managing parameters from lists, queries, or worksheet cells, and using M code for greater flexibility. Readers also explore how to avoid common issues with non-unique keys and privacy settings. Chapter 9: Creating Custom Functions – Discover how to encapsulate and reuse transformations across multiple datasets using custom functions in Power Query. From understanding how Power Query generates functions during folder imports to building reusable logic from parameterized queries, this chapter provides hands-on techniques for modular design. It also demonstrates how to construct custom M functions from scratch, handle optional arguments, and standardize data formats effectively— laying the groundwork for scalable, automated workflows. Chapter 10: Examples Using M Language – Presents advanced data transformation techniques that go beyond the graphical interface by leveraging the M language. Covers a wide range of real-life scenarios such as running totals, custom sorting, dynamic row and column removal, and pair generation. Demonstrates how recursion works through a factorial function and offers scripting and structural best practices to improve query flexibility and performance. Chapter 11: Optimization and Extensions – Focuses on making Power Query workflows more efficient, readable, and maintainable. Introduces built-in view and statistics tools that help detect data quality issues and evaluate transformations. Highlights concrete strategies for improving query performance by removing unnecessary data early, reducing applied steps, using correct data types, and avoiding computationally expensive operations. Discusses techniques to measure execution time using Power Query, VBA, and Power BI diagnostics. Also explores Visual Studio Code as an alternative editor for better M code formatting and development.