Power Query from Beginner to Mastery

Name: Power Query from Beginner to Mastery
ISBN: 9787301333044

Peter XuPeking University Press · ChinaJun 2025

3.8

Word count: 147K words
Chapters: 107
ISBN: 9787301333044

Report an error Find this book

Summary

This book is divided into 10 chapters, covering introductory knowledge of Power Query and data integration, a series of knowledge points on Power Query's M syntax rules, an explanation of how Power Query achieves data cleaning and restructuring, a focused introduction to the functions of various built-in functions currently available in Power Query, and finally how to use custom functions to implement complex data processing and restructuring processes.

Editor's review

This book covers the main stages of Power Query from beginner to advanced in ten chapters: it discusses data integration and basic operations, systematically sorts out M syntax rules, and provides methods for data cleaning and restructuring. Particularly substantial is the categorized explanation of built-in functions and how to use custom functions to handle complex data processing and restructuring tasks. The overall approach follows a progression of “rules, functions, and custom functions,” suited to readers who already work with Excel or Power BI and wish to streamline repetitive cleaning workflows. Readers should not merely flip through the function list but should follow the chapters and reproduce the examples hands-on, especially by practicing the M syntax and custom functions sections together, so as to put the tool to flexible use. For those with no experience in spreadsheet data processing, the start will be rather fast-paced.

Who it's for

A good fit for

Office workers already using Excel for data organization who want to improve cleaning efficiency
Power BI beginners who need to build up data integration and restructuring skills
Entry-level data analysts hoping to systematically master M syntax and built-in functions
Readers who need to handle repetitive data cleaning tasks and want to use custom functions for efficiency

Not a good fit for

Complete beginners with no experience operating spreadsheet software
Readers who only want a formula reference and are unwilling to practice hands-on
Readers seeking systematic tutorials in programming languages such as Python or SQL

Reading tips

Follow the chapters in order and reproduce the examples hands-on, getting data integration working before moving on to M syntax.
Use the built-in functions chapter as a reference to consult repeatedly, and pair it with small exercises from the custom functions chapter.
Choose a real cleaning task from your own work and adapt it into a reusable query as you read.
After each chapter, summarize in one sentence the data problem that chapter solves, to avoid remembering operations without the underlying approach.

About the publisher

Peking University Press China · Founded in 1979

Peking University Press is a comprehensive university press whose publishing areas cover classical literature, popular science, social culture, world history, Western philosophy, and culture. Its catalogue contains 6,031 titles, with representative books including A Dream of Red Mansions, Relativity: The Special and General Theory (15th Edition) (with Einstein's classic papers on unified field theory and other topics), Elements, Conics, The Aristocratic Families of the Eastern Jin, The Philosophy of Mythology (Volume I), The Philosophy of Mythology (Volume II), and The Origin of Continents and Oceans. Representative authors include Cao Xueqin, Zhou Ruchang, Albert Einstein, Euclid, Apollonius, Tian Yuqing, Schelling, and Xian Gang. The press has developed distinctive strengths in classical Chinese literature and in the translation of Western philosophical classics, while also covering scientific classics and social and cultural studies. As public information is limited, this description is based only on the books in the catalogue and offers no further extrapolation.

版权信息
内容提要
前言
第1章走入Power Query的世界
1.1 Excel处理数据的缺点
1.2 Excel的Power数据处理组件
1.3 Power Query组件
1.4 Power Query编辑器
1.5 Power Query的底层语言——M语言
1.6 本章总结
第2章 Power Query的数据集成
2.1 Power Query的CSV/TXT 数据集成
2.2 Power Query的Excel数据集成
2.3 Excel 导入当前表格中的数据
2.4 XML格式数据集成
2.5 JSON格式数据集成
2.6 文本格式数据集成
2.7 Power Query的SQL Server数据集成
2.8 Power Query导入Web数据
2.9 Power Query 导入Web API数据
2.10 Power Query导入MySQL数据
2.11 Power Query导入文件夹数据
2.12 Power BI中的数据流服务
2.13 数据源访问的权限管理
2.14 本章总结
第3章 Power Query和M语言
3.1 什么是M语言
3.2 M语言支持的基本数据类型
3.3 M语言支持的组合数据类型
3.4 Power Query的M语言结构
3.5 Power Query中M语言的智能提示
3.6 Power Query的M语言变量
3.7 Power Query的M语言参数
3.8 Power Query的M语言流程处理
3.9 Power Query的M语言的错误处理
3.10 Power Query的M语言嵌套
3.11 Power Query的M语言操作符
3.12 M语言的注释
3.13 本章总结
第4章 Power Query实现数据的清洗和重构
4.1 数据清洗遵从的原则
4.2 数据清洗后的操作
4.3 数据清洗和重构具体操作
4.4 Power Query实现数据类型的转换
4.5 Power Query实现列的删除
4.6 Power Query 实现行的删除
4.7 Power Query保留行操作
4.8 Power Query的数据筛选
4.9 Power Query添加数据列
4.10 Power Query按列分列
4.11 Power Query按列分行
4.12 Power Query的数据转置
4.13 Power Query数据替换
4.14 Power Query数据列交换
4.15 Power Query的数据排序
4.16 Power Query时间表的构建
4.17 本章总结
第5章 Power Query实现数据合并操作
5.1 Power Query数据多重合并操作
5.2 Power Query数据追加合并操作
5.3 Power Query数据横向合并
5.4 数据合并连接关系说明
5.5 Power Query处理缺失值
5.6 Power Query实现数据的分组
5.7 Power Query实现数据的透视
5.8 Power Query实现数据的逆透视
5.9 本章总结
第6章 Power Query查询连接的分享与刷新
6.1 Power Query数据连接的分享与重用
6.2 Excel中的Power Query连接与复制
6.3 Power Query连接的导出与导入
6.4 Power Query 的数据刷新
6.5 本章总结
第7章 Power Query的函数
7.1 Power Query函数的获取与使用
7.2 Power Query文件系列访问函数
7.3 数据库访问函数
7.4 Web访问函数
7.5 Power Query文本处理函数
7.6 Power Query列表处理函数
7.7 Power Query记录处理函数
7.8 Power Query表处理函数
7.9 Power Query URL处理函数
7.10 Power Query数据合并函数
7.11 Power Query数据分割函数
7.12 Power Query日期时间函数
7.13 本章总结
第8章 Power Query的自定义函数
8.1 从零开始构建自定义函数
8.2 基于数据结果建立函数
8.3 执行并获取自定义函数结果
8.4 自定义函数提取文件夹内所有请假数据
8.5 自定义函数获取基金即时净值
8.6 本章总结
第9章 Power Query与Python
9.1 Power Query调用Python的前置条件
9.2 Python环境的安装与部署
9.3 Power BI 启用Python 支持
9.4 本章总结
第10章 Power Query数据综合应用案例
10.1 身份证信息的初步导入
10.2 身份证号码数据初步清洗
10.3 身份证数据二次清洗
10.4 非中文姓名数据清洗
10.5 本章总结
附录 Power Query 简单案例处理
资源下载码

FAQ

Can complete beginners study this book directly?

Not really recommended. The book progresses from the basics to M syntax, built-in functions, and custom functions at a fairly quick pace. If you have no experience operating spreadsheet software at all, it is advisable to first become familiar with basic Excel data operations before entering this book; otherwise you may get stuck in the syntax sections.

Is the M syntax section difficult?

M syntax is one of the book's difficult points, but the author explains it step by step through a series of rules and pairs it with data cleaning and restructuring scenarios. As long as you type through the examples hands-on, understanding will come much faster; reading without practicing will easily feel abstract.

What practical work can I handle after finishing it?

It covers data integration, cleaning, restructuring, and completing relatively complex data processing workflows with built-in and custom functions. It is suited to streamlining repetitive spreadsheet organization work, but the actual results depend on whether you practice hands-on and transfer the methods to your own data tasks.

Comments

Find this book

This site is an index: you start “Search all sources” in your browser, and the entries it lists all point to third-party sites; please judge their availability yourself.

About the author

Peter Xu Author · Chinese · 1985

Xu Peng, who writes under the pen name Yi Shuihan, was born in June 1985 in Gaotang, Shandong. He is a member of the China Writers Association, a member of the Chongqing Municipal Committee of the Chinese People's Political Consultative Conference, and a second-class literary creator. His work spans a wide range: modern Chongqing fiction such as Taipingmen, Kuixinglou and Zengjiayan; historical and literary writings such as Changle: The Scholar's Success and Weiyang: Sunset over Shu Roads; and books including Once You Decide, Go All Out and Hey, Youngster. He has also published practical computer books, including Power Query from Beginner to Master and The Complete Guide to Windows PowerShell Automation, and contributed to reports such as The 2018 Report on Frontier Science and Technology Development in China's Cyberspace Security. He won second prize in the National New Concept Writing Competition, received a perfect score in the 2003 college entrance exam essay, and is also a professional racing driver. Public information is limited, but his writing crosses both literary and technical fields and covers diverse subjects.