精通 Power Query

肯·普尔斯（Ken Puls） 是加拿大特许专业会计师，微软 Excel MVP。他经营着 Excelguru 咨询公司，并通过 Skillwave培训平台教用户如何使用 Excel 和 Power BI。

米格尔·埃斯科瓦尔 （Miguel Escobar），在编写本书的时候是微软 Power BI MVP，并在巴拿马经营着一家名为 Powered Solutions 的咨询公司。在本书英文版即将出版时，米格尔收到一份令人兴奋的邀请函，即加入微软 Power Query 团队担任项目经理。

BI 佐罗团队，是基于对数据分析的爱好而形成的爱好者社区，活跃成员包括微软MVP、LVMH集团数据分析师、企业CFO等。2015年以来，经过多年发展，团队已经孵化出不同行业的数据咨询类企业，尤其在零售、快消、餐饮、汽车等行业聚集和沉淀了专业的研究成果，且已为50余家企业提供了数字化转型“开箱即用”的落地方案。团队通过“PowerBI战友联盟”的微信公众号、“BI佐罗”的小红书、B站、抖音等平台，持续为社区带来知识分享，也欢迎广大爱好者一起交流讨论。

本书是Power Query领域的经典之作，旨在让读者用正确而高效的方法精通Power Query。本书是两位作者多年积累的业务和IT工作经验总结，同时也是全球Power Query顶级社群的大咖经验提炼。本书从业务背景出发，在业务和IT之间做到了良好的平衡，系统化地给出了Power Query的思维框架和模式，同时按照多个企业级业务场景一一展示其实用性。

无论你是Power Query的新手还是经验丰富的ETL专家，都可以从各自的角度领略Power Query 和M语言的魅力。对于业务用户，本书给出了日常办公所涉及的大量复杂数据问题解法的直接参考，并提供了大量案例，可以直接应用。对于专业用户，本书给出了搭建大型专业数据项目的框架性流程指导及优化建议，并提供了相关案例，也可以直接复用。

【专业书评】

作为PQ、PP和Power BI的忠实用户，我由衷地感谢BI佐罗团队能翻译这本书。书中不仅涵盖了PQ的各种使用技巧，更是自始至终地贯穿着Ken和Miguel的设计思考，让读者在掌握技术的同时更能体会每个技术点背后的精妙逻辑。正是这些思考和逻辑，让PQ不仅仅是一个数据清理的工具，更是一个数据处理思维的全新起点。

——张丹，能源公司CFO

微软已经将Power Query纳入数据处理的多种平台，这项技术是经过真实用户反馈检验的，可以使业务人员不再依赖SQL等技术，掌握数据处理和分析的技能。如果你正准备整理自己的数据，Power Query是正确的选择，而学习Power Query，这本书就是正确的选择。

一旦开始阅读，你就能感受到用Power Query 零代码即可整理数据的魔力。

——Kyle，微软中国云（Azure China）技术支持经理

对于学习和运用Power Query，我仍然处于摸索的阶段。我认为可以将这本书和译者的博客，当作产品手册和词典来使用与查询：初读可对Power Query有完整的了解并学会使用，此后再遇到各类问题可据此来寻找答案，裨补阙漏，必能有所广益。

——肖伦，LVMH集团数据分析师

菲仕乐（Fissler）近几年逐步开始重视数字化管理，SAP标准化数据的整合结合Power BI的大数据管理是集团未来的转型目标。作为全资子公司的菲仕乐贸易（上海）有限公司（Fissler China Ltd.，菲仕乐中国），我们早于集团对Power BI 进行了部署，与BI佐罗老师合作，已完美实现了历史数据的清理和国内数据构架的搭建，财务数据库呈现的效果超过我们的预期，也受到管理层的一致好评。

——Cindy，菲仕乐中国财务IT 总监

在这个数据时代，我们或多或少需要跟数据打交道。掌握数据分析工具的使用方法，才能更好地深入研究业务。而Power Query，可以说是最有性价比的工具之一，不需要你有技术背景也能轻松入门。而这本书，就是一本从入门到精通的落地指南，其系统化的思维框架和实操案例，为工作中的各种实际难题提供高效的解决方案。如果你希望从每天繁杂的数据中解脱出来，提高分析效率，开拓分析思路，那这本书是一个优秀的选择。

——张航，第5 届Power BI 可视化大赛一等奖获得者

无论是Excel还是Power BI，做出一份完美的报表或报告，少不了数据的清理与准备，这看不见的工作量很可能占了总工作量的80%。这项工作可以请IT人员或者顾问支持解决，也可以由业务人员自学SQL和Python搞定。但现实是，你可能没有预算请别人做，也可能觉得自学新的语言太难。这时Power Query就为非技术出身的业务人员提供了一个“当家作主”的好机会——做数据的主人。为此，你需要的只是几杯咖啡的预算，来拥有这本书。

——王诗琛，第5 届Power BI 可视化大赛“最具推广价值奖”获得者

《精通Power Query》是目前少有的可以体系化学习Power Query的佳作，书中案例覆盖了多数的数据整理常见场景，对实际操作极具指导意义，适合放在手边时常查阅。对于经常和数据打交道的各行各业人士来说，这是本值得一读再读的好书。

——陆俊峰，第5 届Power BI 可视化大赛“最佳展现创意奖”获得者

自从Power Query面世以来，Excel数据处理能力发生了指数级变革。使用Power Query可以让你“弯道超车”，轻松地完成原本需要高难度的Excel公式甚至编写VBA代码或其他程序语言才能完成的数据整理工作。这本书是一本难得的Power Query“驾车指南”，内容翔实，通俗易懂，能让你快速掌握Power Query 这一利器。

——张震，《智能管理会计：从Excel到Power BI 的业务与财务分析》作者

谁都无法否认，Excel是非常重要且好用的数据分析工具。但是有很多业务一线的小伙伴告诉过我，大量的重复的数据清理工作占去了他们40%到80%的工作时间，这极大地影响了他们的工作效率。这说明他们缺少了Power Query 的学习。在Power Query 学习领域，“猴子书”久负盛名，但一直没有被引进国内。在BI佐罗团队的努力下，中文版终于面世了。如果你和我的小伙伴们一样，被数据清理工作所困扰，那么这本书你一定要细细研读，这能让你的工作效率提升好几倍！

——孙光，小米数据分析师

这本书由浅入深、由基础到进阶地介绍了Power Query 和M函数，适用于各个阶段的读者学习：为初入门的小白构建了一套完整的Power Query知识体系，为已入门的专业人士提供了一系列优化查询的最佳实践，为业务伙伴指导了针对实际业务问题的解决方案。两位作者作为该领域的领跑者，从实际工作经验出发，将业务与IT完美平衡，使得这本书不仅仅停留在技术理论的“传道”层面，更是为实际业务提供了优秀参考，让读者能够即学即用，学以致用。强烈推荐这本书！

——徐露，碧迪医疗数据分析师

这本书是学习Power Query的经典之作的第二版，两位作者融合了高超的技术能力和推荐词丰富的业务应用场景，帮助读者能够循序渐进地入门并精通这套Power BI 与Excel中的数据处理利器。同时这本书也是BI佐罗团队在DAX的精进和实践外，潜心付出、翻译后，带给广大Power BI用户的又一硕果。这本书配合BI佐罗团队的视频课程一同学习，将让你的数字化能力成倍提升。

——陆文捷，物流供应链BI分析师，《DAX设计模式》译者之一

熟悉这本书前半部分后，你就可以通过Power Query直观的界面，完成数据准备的大部分工作。如果你还想解决别人没办法处理的难题，则可以选择继续深入到M语言、优化查询与自动化等进阶内容。这本书帮助我用更聪明的方法，高效进行数据提取、转换和加载，进而让我得以全力聚焦于更重要的一环：挖掘出在数据中潜藏的风险、机遇与更多的价值。如果有时间的话，现在就开始吧，相信你也一定能从中受益。

——蔡至洁，BI分析师

感谢BI佐罗老师的课程能够让我感悟到正确和智慧的数据分析思维。我通过两年时间的努力做到了别人四五年才能达到的工作状态，现在继续用这套思维体系服务于世界五百强企业。如果你也像曾经的我陷于纠结之中，那我推荐你通过这本书学习这套技术。

——Nancy，商业数据分析师

第0 章　导言：一场新的革命 1
0.1　数据分析师的常见场景 1
0.2　“魔法”的好处和危险 2
0.3　未来的改变 3
0.4　 为什么说Power Query 有“魔力” 5
0.4.1　提取 5
0.4.2　转换 5
0.4.3 加载 6
0.5　 Power Query 和产品体验的整合 7
0.5.1　Power Query 的组件 7
0.5.2　产品体验的整合 8
0.6　Power Query 的更新周期 8
0.6.1　Power Query 在线版 9
0.6.2　Microsoft 365 9
0.6.3　Excel 2016/2019/2021 9
0.6.4　Excel 2010 & 2013 9
0.6.5　Power BI 桌面版 10
0.7　如何使用本书 10
0.7.1　Microsoft 365 Excel 10
0.7.2　Power BI 桌面版 11
0.7.3　以前的Excel版本 11
0.7.4　单击“获取数据”按钮 11
0.7.5　特殊元素 12
第1 章　基础知识 13
1.1　开始之前 13
1.1.1　调整Excel默认设置 13
1.1.2　 调整Power BI 桌面版默认设置 14
1.2　提取 14
1.2.1　选择数据 14
1.2.2　身份验证 15
1.2.3　预览窗口 15
1.2.4　查询处理 16
1.3　转换 17
1.3.1　编辑器 17
1.3.2　默认转换 18
1.3.3　源 18
1.3.4　将第一行用作标题 19
1.3.5　更改的类型 19
1.3.6　调整和修改转换 20
1.4　加载 22
1.4.1　设置数据类型 22
1.4.2　重命名查询 23
1.4.3　在Excel中加载查询 24
1.4.4　在Power BI 中加载查询 24
1.5　刷新查询 25
1.6　编辑查询 26
1.6.1　在Power BI 中启动查询编辑器 26
1.6.2　在Excel中启动查询编辑器 27
1.6.3　检查步骤 27
1.6.4　重新配置步骤 27
1.7　Power Query 的价值 29
第2 章　查询结构设计 31
2.1　使用多查询体系结构 31
2.1.1　对ETL进行分层 31
2.1.2　单个查询的好处 31
2.1.3　拆分查询的好处 32
2.1.4　关于“暂存”查询的　　　性能 32
2.2　查询的引用 34
2.2.1　创建基础查询 34
2.2.2　创建查询的引用 34
2.2.3　查询依赖关系树的可视化 37
2.2.4　使用Monkey工具查看依赖关系 38
2.3　选择查询加载目的地 38
2.3.1　在Power BI 中选择加载目的地 38
2.3.2　在Excel中选择加载目的地 39
2.3.3　更改加载目的地 41
2.4　保持查询的条理性 43
2.4.1　查询文件夹 43
2.4.2　将查询分配到文件夹 44
2.4.3　排列查询和文件夹 44
2.4.4　查询子文件夹 45
2.5　拆分现有查询 45
2.6　关于查询体系结构的最后思考 47
第3 章　数据类型与错误 49
3.1　数据类型与格式 49
3.1.1　数据格式 49
3.1.2　数据类型 49
3.1.3　如何设置数据格式 52
3.1.4　设置数据类型的顺序 53
3.1.5　数据类型的重要性 54
3.2　常见的错误类型 55
3.3　步骤级错误 56
3.3.1　数据源错误 56
3.3.2　没有找到某列 58
3.4　值错误 59
3.4.1　发现错误 60
3.4.2　无效的数据类型转换 61
3.4.3　不兼容的数据类型 63
3.5　检查查询错误 64
3.5.1　发现错误的来源 64
3.5.2　修复最初查询 65
3.5.3　删除错误查询 66
3.6　关于数据类型与错误的最后思考 66
第4 章　在Excel和Power BI间迁移查询 67
4.1　在工具之间迁移查询 67
4.1.1　Excel到Excel 68
4.1.2　Excel到Power BI 70
4.1.3　Power BI 到Excel 71
4.1.4　Power BI 到Power BI 71
4.2　导入查询 72
4.2.1　仅外部数据源 72
4.2.2　数据模型的导入 74
4.2.3　导入时复制数据 75
4.2.4　导入时保持连接 80
4.3　在工具之间迁移查询的思考 82
第5 章　从平面文件导入数据 84
5.1　了解系统如何导入数据 84
5.1.1　设置系统默认值 84
5.1.2　程序如何解析平面数据 85
5.2　导入带分隔符的文件 87
5.2.1　源数据文件 87
5.2.2　提取数据 88
5.2.3　错误的解析 88
5.2.4　使用区域设置 89
5.3　导入无分隔符的文本文件 92
5.3.1　连接到文件 92
5.3.2　清理无分隔符文件 93
5.3.3　按位置拆分列 94
5.3.4　利用查询中的错误 96
5.3.5　删除“垃圾列” 98
5.3.6　合并列 99
5.3.7　通过分隔符拆分列 99
5.3.8　修剪重复的空格 100
5.3.9　Power Query 的闪耀时刻 101
第6 章　从Excel导入数据 104
6.1　来自当前工作簿的数据 104
6.1.1　连接到表 105
6.1.2　连接到区域 106
6.1.3　连接到命名区域 108
6.1.4　连接到动态区域 110
6.1.5　连接到工作表 112
6.2　来自其他工作簿的数据 112
6.2.1　连接到文件 113
6.2.2　连接到表 114
6.2.3　连接到命名区域 115
6.2.4　连接到工作表 115
6.3　关于连接到Excel数据的最后思考 118
第7 章　常用数据转换 121
7.1　逆透视 121
7.1.1　准备数据 122
7.1.2　逆透视其他列 123
7.1.3　重新透视 124
7.1.4　应对变化 125
7.1.5　逆透视之间的区别 125
7.2　数据透视 126
7.3　拆分列 128
7.3.1　将列拆分为多列 129
7.3.2　将列拆分为多行 130
7.3.3　拆分后逆透视与拆分到行 131
7.4　筛选和排序 132
7.4.1　按特定值筛选 133
7.4.2　按上下文筛选 135
7.4.3　数据排序 137
7.5　数据分组 138
第8 章　纵向追加数据 141
8.1　基本追加 141
8.1.1　追加两个表 142
8.1.2　追加额外的表 145
8.2　追加列标题不同的数据 147
8.3　在当前文件中追加表和区域 148
8.3.1　合并表 149
8.3.2　合并区域或工作表 153
8.3.3　Excel.Current Workbook 155
8.4　关于追加查询的最后思考 155
第9 章　批量合并文件 156
9.1　示例文件背景介绍 156
9.2　过程概述 157
9.2.1　合并文件的标准流程 157
9.2.2　合并文件的通用架构 157
9.3　步骤0：连接到文件夹 159
9.3.1　连接到本地/ 网络文件夹 159
9.3.2　连接到SharePoint文件夹 160
9.3.3　连接到OneDrive for Business 162
9.3.4　连接到其他文件系统 162
9.4　步骤1：筛选文件 162
9.4.1　标准步骤 163
9.4.2　应用于示例场景 163
9.5　步骤2：合并文件 165
9.5.1　标准步骤 165
9.5.2　应用于示例场景 165
9.6　步骤3：转换示例文件 167
9.6.1　使用转换示例文件的原因 168
9.6.2　使用转换示例文件的方法 168
9.7　步骤4：通过主查询进行数据清理 171
9.7.1　修复主查询中的错误 171
9.7.2　保存文件属性 172
9.7.3　添加更多的步骤 173
9.8　更新解决方案 174
9.8.1　使用数据 175
9.8.2　添加新文件 175
9.8.3　只用最后几个文件以提升速度 176
第10 章　横向合并数据 179
10.1　合并基础知识 179
10.1.1　创建“暂存”查询 179
10.1.2　执行合并 180
10.2　连接类型 182
10.2.1　左外部连接 184
10.2.2　右外部连接 186
10.2.3　完全外部连接 187
10.2.4　内部连接 188
10.2.5　左反连接 189
10.2.6　右反连接 190
10.2.7　完全反连接 190
10.3　笛卡儿积（交叉连接） 191
10.3.1　方法 191
10.3.2　示例 192
10.3.3　意外问题 194
10.4　近似匹配 195
10.4.1　方法 195
10.4.2　示例 196
10.5　模糊匹配 198
10.5.1　基本模糊匹配 199
10.5.2　转换表 200
10.5.3　减小相似性阈值 201
10.5.4　保持模糊匹配的策略 203
第11 章　基于Web的数据源 204
11.1　连接到Web 数据文件 204
11.2　连接到HTML网页 205
11.2.1　连接到网页 206
11.2.2　自然表和建议表 206
11.2.3　使用示例添加表 207
11.3　连接到没有表的页面 209
11.4　从Web 获取数据的注意事项 212
11.4.1　收集数据的经验 213
11.4.2　数据完整性 213
11.4.3　解决方案稳定性 213
第12 章　关系数据源 214
12.1　连接到数据库 214
12.1.1　连接到数据库 214
12.1.2　管理凭据 216
12.1.3　无法连接 217
12.1.4　使用导航器 217
12.1.5　探索数据 218
12.2　查询折叠 221
12.2.1　理解查询折叠 221
12.2.2　支持查询折叠的技术 223
12.2.3　常见问题 224
12.3　数据隐私级别 225
12.3.1　声明数据隐私级别 226
12.3.2　管理数据隐私级别 227
12.3.3　隐私与性能 227
12.3.4　禁用隐私引擎 228
12.4　优化 230
第13 章　转换表格数据 233
13.1　透视 233
13.1.1　单列多行 233
13.1.2　多层行标题 237
13.1.3　多层列标题 239
13.2　逆透视 242
13.2.1　多层行标题 242
13.2.2　性能优化 248
13.2.3　重构 249
13.2.4　保留“null”值 250
13.3　分组 253
13.3.1　占总计的百分比 253
13.3.2　数据排序 254
13.3.3　分组编号 257
第14 章　条件逻辑 261
14.1　基础条件逻辑 261
14.1.1　数据集背景 261
14.1.2　连接到数据 262
14.1.3　通过用户界面创建条件逻辑 262
14.2　手动创建IF 判断 265
14.3　IFERROR函数 268
14.4　多条件判断 270
14.5　与上下行进行比较 273
14.6　示例中的列 276
第15 章　值系统 282
15.1　值类型 282
15.2　表 283
15.3　列表 284
15.3.1　语法 284
15.3.2　从头开始创建列表 284
15.3.3　将列表转换为表 286
15.3.4　从表列创建列表 287
15.3.5　创建列表的列表 288
15.4　记录 290
15.4.1　语法 290
15.4.2　从头开始创建记录 291
15.4.3　将记录转换为表 291
15.4.4　从头开始创建多个记录 292
15.4.5　将多个记录转换为表 293
15.4.6　按索引访问表记录 294
15.4.7　按条件访问表记录 295
15.4.8　从每个表行创建记录 298
15.5　值 300
15.6　二进制文件 300
15.7　错误 301
15.7.1　行级错误 301
15.7.2　步骤级错误 301
15.8　函数 302
15.9　关键词 304
15.9.1　二进制（#binary） 305
15.9.2　日期时间（ #datetime） 306
15.9.3　时间（#time） 307
15.9.4　持续时间（ #duration） 307
15.9.5　类型（type） 308
15.9.6　表（#table） 310
第16 章　理解M语言 314
16.1　M查询结构 314
16.1.1　查询结构 315
16.1.2　查询定义与标识符 316
16.1.3　关于通用标识符 318
16.1.4　代码注释 319
16.1.5　整体效果 320
16.2　理解查询计算 320
16.2.1　什么是延迟计算 321
16.2.2　查询计划 322
16.3　迭代器（逐行计算） 324
16.3.1　循环函数 324
16.3.2　关键词each和_ 324
16.4　其他技术 328
16.4.1　获取第一个值 328
16.4.2　错误保护 330
16.4.3　固定类型动态列表 331
16.4.4　自适应类型动态列表 334
第17 章　参数和自定义函数 338
17.1　重新创建合并文件 338
17.1.1　创建示例文件 339
17.1.2　创建示例文件参数 340
17.1.3　创建转换示例 341
17.1.4　创建转换函数 342
17.1.5　调用转换函数 342
17.1.6　更新转换函数 342
17.1.7　观察到的规律 343
17.2　使用参数构建自定义函数 344
17.2.1　创建文件路径参数 345
17.2.2　创建Timesheet转换 346
17.2.3　创建Timesheet函数 347
17.2.4　更新Timesheet查询 347
17.3　手动构建自定义函数 349
17.3.1　构建一个单一使用场景 350
17.3.2　将查询转换为函数 350
17.3.3　从另一个查询调用函数 352
17.3.4　调试自定义函数 353
17.3.5　恢复函数功能 355
17.4　动态参数表 355
17.4.1　动态文件路径问题 356
17.4.2　实现动态参数表 357
17.4.3　创建参数表 357
17.4.4　实现fnGetParameter函数功能 358
17.4.5　调用函数 359
17.5　参数表的意义 361
第18 章　处理日期时间 362
18.1　边界日期 362
18.1.1　计算边界日期 363
18.1.2　处理财政年度日期 364
18.1.3　处理364 日型 365
18.2　日期表 367
18.2.1　原子日期表 367
18.2.2　增强日期表 368
18.2.3　财政日期列 369
18.2.4　全局日期列 369
18.2.5　自定义日期表 371
18.2.6　示例说明 373
18.3　日期时间填充 374
18.3.1　日期级别填充 374
18.3.2　小时级别填充 376
18.3.3　带间隔的填充 377
18.4　按日期分摊 378
18.4.1　起止日内按日分摊 379
18.4.2　起止日内按月分摊 381
18.4.3　在开始日期后按月分摊 384
18.4.4　关于分摊 386
第19 章 　查询优化 388
19.1　优化设置 388
19.1.1　全局−数据加载 388
19.1.2　全局−Power Query编辑器 388
19.1.3　全局−安全性 389
19.1.4　全局−隐私 389
19.1.5　当前工作簿−数据加载 389
19.1.6　当前工作簿−其他选项 390
19.2　使用缓存 391
19.2.1　强制计算 391
19.2.2　缓存结果 393
19.3　处理响应滞后 396
19.3.1　优化策略 397
19.3.2　体验响应滞后 397
19.3.3　重构解决方案 399
19.3.4　调整预览数据 401
19.4　处理公式防火墙 401
19.4.1　隐私级别不兼容 402
19.4.2　数据源访问 402
19.4.3　重建数据组合 402
19.4.4　连接式重构 404
19.4.5　展开式重构 406
19.4.6　传值重构 408
19.4.7　关于公式防火墙 410
第20 章　自动刷新 412
20.1　Excel自动刷新选项 412
20.2　Excel计划刷新 412
20.2.1　后台刷新 412
20.2.2　每x分钟刷新一次 413
20.2.3　打开文件时刷新数据 413
20.2.4　启用快速数据加载 413
20.3　用宏实现自动刷新 414
20.3.1　刷新单个连接 414
20.3.2　按特定顺序刷新 416
20.3.3　刷新所有查询 418
20.3.4　同步刷新的问题 418
20.4　Power BI 中的计划刷新 418
· · · · · ·