Pages
368
Published
2018
A hands-on guide to data transformation workflows in Power BI and Power Query for Excel
Master the full data-preparation pipeline — from raw source to clean, combined dataset — so your DAX models are built on solid ground.
Before a single DAX measure runs, your data has to be collected, shaped, and combined. Gil Raviv's hands-on guide walks through Power Query in both Power BI and Excel, covering every stage of the transformation pipeline: connecting to sources, cleaning messy data, restructuring tables, and merging disparate datasets into a single, reliable model ready for analysis.
Every Power BI report and every DAX calculation depends on one thing: clean, well-structured data. Most tutorials skip past that dependency. This book does not.
Gil Raviv — a longtime Microsoft MVP and practitioner — built this guide around the reality that data preparation is where projects succeed or fail. Power Query is the tool, and this book is the complete practical reference for it, covering both Power BI Desktop and the Power Query add-in for Excel so you can apply the skills wherever your team works.
You will start by understanding how Power Query fits into the modern self-service analytics stack. From there, each chapter adds a concrete skill: connecting to flat files, databases, and web sources; applying transformations that survive refresh; reshaping wide tables and unpivoting survey-style data; writing M expressions to handle edge cases your UI clicks cannot reach; and combining queries through append and merge operations that mirror real business data structures.
The book does not treat data preparation as a chore to endure before the real analysis begins. It treats it as a discipline with its own logic — one that, done well, makes every downstream DAX model faster to build, easier to audit, and more reliable in production.
If you have been writing DAX measures on top of data you are not fully confident in, this book is the place to fix that foundation. 368 pages, published April 2018 by Pearson Professional.
Understand where Power Query fits within Power BI and Excel and why data preparation is a distinct discipline from data modeling. You will set up your environment and run your first query.
Connect to the most common source types — CSV and Excel files, SQL databases, web pages, and folders of files — and learn how Power Query manages connection credentials and refresh settings.
Apply the core row and column operations: filtering, sorting, renaming, changing data types, splitting columns, and replacing values. You will build transformation sequences that stay reliable across refreshes.
Turn wide pivot-style tables into normalized structures by unpivoting columns, transposing tables, and promoting headers. These techniques handle the survey and report exports that most analysts dread.
Move beyond what the graphical editor generates and write M code directly to handle conditional logic, custom column formulas, and edge cases the UI cannot reach.
Join two queries together using inner, left-outer, and anti-join merge types to replicate lookup logic and enrich one table with columns from another.
Stack multiple tables from different time periods, regions, or files into a single consolidated query, including techniques for dynamically pulling all files from a folder.
Organise queries into groups, control which queries load to the data model, and identify slow steps so your solution performs well as data volumes scale.
Bring every skill together to build a full, refreshable data model in both Power BI Desktop and Excel, ready to support DAX measures and dashboard reports.
Familiarity with Excel is assumed. You do not need prior Power Query experience, but some exposure to Power BI Desktop will help you follow the Power BI-specific examples more quickly.
Both. Gil Raviv covers Power Query as it appears in Power BI Desktop and in the Excel add-in side by side, so the skills transfer regardless of which tool your team uses.
It is focused entirely on data collection, transformation, and combination — the stage before DAX. It pairs well with a dedicated DAX resource but does not teach DAX itself.
The core Power Query concepts and M language covered here remain stable. Some UI details and connector options have evolved, but the transformation patterns and methodology hold up well for current versions of Power BI and Excel.
No programming background is required. The book introduces M formula syntax gradually and in the context of real scenarios, so you learn it as a natural extension of the visual editor rather than as a separate coding skill.
New
Build interactive dashboards and reports with Power BI Desktop, the Power BI service, and DAX
by Bradley Schacht, Devin Knight, Erin Ostrowsky, Mitchell Pearson
New
A practical guide to Power BI data modeling, DAX, and enterprise-grade report design
by Brett Powell, Greg Deckler, Leon Gordon
New
Reusable DAX solutions for common Power BI and Analysis Services business intelligence scenarios
by Alberto Ferrari, Marco Russo
New