Power Query数据清洗实战

序

我是一个懒人，一个在单元格中应该输入“0.5”的地方，都能懒得只敲个“.5”的懒人。
正是因为这种“懒”，我绝无可能坐在电脑前，为寻找两个表的差异，让自己一双早已近视的小眼睛聚焦超过五分钟，因此我学会了Vlookup函数。
正是因为这种“懒”，我绝无可能坐在电脑前，为完成数据表的统计汇总，“祭”出抽屉深处的计算器，“蹂躏”其按键超过五分钟，因此我学会了数据透视表。
正是因为这种“懒”，我在单元格里“横冲直撞”这些年，竟然一不小心撞成了一个颇为资深的Excel玩家。
可是资深玩家也有为难的时候，如果面对的数据表不规范呢？数字和文本混在一个单元格里、数据表中大量使用合并单元格、数据表是一个不方便进一步统计的二维表，甚至数据表被分散到了不同的工作表或不同的工作簿……于是我自制了一个简陋的大喇叭，扯直了嗓子高声喊：“各位‘表哥’‘表姐’‘表弟’‘表妹’们，请改变你们的制表习惯，制作出规范的、有利于进一步统计汇总的数据源表吧！”
这个口号我喊了很久，而且几乎是逢人就喊，后，我被一堆“表哥”“表姐”“表弟”“表妹”们鄙视，理由无他，只因为Excel的内置功能在转换表格结构上可谓“硬伤重重”，这就意味着不能快速解决眼前的问题，反而会在一时之间增加许多工作量。所以，随着简陋的大喇叭因为日积月累的使用而越来越破旧，我逐渐萌生了将它扔进垃圾桶的念头。
这时，Power Query横空出世了。作为一个Excel玩家，我自然不会放任一个新功能天天躺在电脑里睡大觉，随之而来的各种折腾让我惊觉，原来我那个大喇叭确实可以扔了，因为Power Query正是用来进行数据查询和数据清洗的神器！前面提到的那些让Excel内置功能止步的问题，在Power Query面前简直就是小菜一碟。
于是我开始折腾Power Query了！
问题是，我除了是一个懒人以外，还是一个爱显摆的人。所以只让眼前的显示器默默地看着我的各种折腾，实在有负于我这个“爱显摆”的名声。
于是，与之相关的一些零零碎碎的博文和视频出炉了，系统讲解“异空间”的视频“Power Query数据清洗实战攻略”降世了……
于是，本书诞生了！
后我还想说一句，Excel自身的内置功能有太多强大之处，我们学习Power Query的目的是让我们在解决现实工作中的各种问题时多一种思路，让工作更为简化高效，而不是抛弃Excel本身，把一切问题都交给Power Query来处理。
祝大家做表愉快！

本书通过对多个实例的演示与讲解，详细介绍了Excel的*功能组件Power Query在数据查询与数据转换方面的主要功能。全书共分为8章，主要包括Power Query编辑器的基本功能，常见数据类型的查询与导入，对原始数据进行合并、拆分、计算、转换等数据整理操作，Power Query中M公式的编写方法等。
本书语言风趣，专注于具体的应用场景，而不拘泥于功能本身，图示清晰、可操作性强且便于查阅，能有效帮助读者提高Excel的使用水平，提升工作效率。
本书主要面向Excel的初、中级用户以及IT技术人员，对于Excel高级用户也具有一定的参考价值。

Excel Home是一个主要从事与推广Microsoft Office应用技术的非营利性网站，该网站上汇聚了众多中国大陆地区及港台地区的Office高手，他们身处各行各业，并身怀绝技！在这批高手以及众多会员的帮助下，Excel Home出版了一系列精彩图书，获得一致好评！

目 录
第1章 “异空间”简介 1
1.1 进入“异空间” 2
1.2 “异空间”界面 5
1.3 回“现世” 11
1.4 再进“异空间” 14
1.5 导入Excel中的数据 16
1.6 数据源与查询表的关系 19
第2章 处理数据 22
2.1 导入JSON文件 23
2.2 整理标题（表头） 27
2.3 整理数据类型 28
2.4 整理列 31
2.5 整理行 35
第3章 各种拆 42
3.1 导入“文本/CSV”文件 43
3.2 修正数据字符数 44
3.3 拆分列 46
3.4 按分隔符提取列 50
3.5 拆分文本与数字 52
3.6 示例中的列 54
3.7 标题的升降 55
3.8 格式修正 57
3.9 按固定字符数处理列 59
3.10 添加“序号”列 61
第4章 M语言 63
4.1 在【自定义列】中编写M公式 65
4.2 编写M公式的“乾坤挪移”大法 69
4.3 编写M公式的“依葫芦画瓢”法 71
4.4 嵌套多个M函数的M公式 74
4.5 修改M代码 77
第5章 各种算 79
5.1 运算 81
5.2 判断 85
5.3 日期与时间 87
第6章 各种转 89
6.1 精简“臃肿”表 90
6.2 二维表转一维表 93
6.3 一维表转二维表 101
第7章 各种并 103
7.1 合并相同结构的多个规范工作表中的数据表 104
7.2 合并相似结构的多个不规范工作表中的数据表 109
7.3 合并不同结构的多个数据表 112
7.4 合并多个规范工作簿中的数据表 117
7.5 合并多个不规范工作簿中的数据表 121
7.6 合并多个工作簿里面的多个工作表 124
7.7 合并网页数据 127
第8章 各种综合 135
8.1 顺利计算带文本的数据 136
8.2 合并字符串 141
8.3 拆分与扩展 151
8.4 其他扩展 167
8.5 设置动态数据源 193
结束语 201

本书通过对多个实例的演示与讲解，详细介绍了Excel的*功能组件Power Query在数据查询与数据转换方面的主要功能。全书共分为8章，主要包括Power Query编辑器的基本功能，常见数据类型的查询与导入，对原始数据进行合并、拆分、计算、转换等数据整理操作，Power Query中M公式的编写方法等。
本书语言风趣，专注于具体的应用场景，而不拘泥于功能本身，图示清晰、可操作性强且便于查阅，能有效帮助读者提高Excel的使用水平，提升工作效率。
本书主要面向Excel的初、中级用户以及IT技术人员，对于Excel高级用户也具有一定的参考价值。

Excel Home是一个主要从事与推广Microsoft Office应用技术的非营利性网站，该网站上汇聚了众多中国大陆地区及港台地区的Office高手，他们身处各行各业，并身怀绝技！在这批高手以及众多会员的帮助下，Excel Home出版了一系列精彩图书，获得一致好评！

有个家伙，出道没几年就“吸粉”无数，它就是本书的主角：Microsoft Office 之Excel中的Power Query。而这家伙之所以能“吸粉”无数，就是因为它在改变表格结构这个领域“手段”惊艳。
比如把图1-1 中左边的“表一”转换成右边的“表二”，只需要一个命令按钮；再如把结构相同的多个工作簿里的数据，无论是2 个、20 个、200 个、2000 个还是更多，合并到一个工作表里，只需要单击几下鼠标，一分钟不到就可以完成，且数据源修改以后还可以一键刷新……其他的各种拆、各种算、各种转、各种并的手法更是层出不穷。
图1-1　Power Query 快速整理不规范数据示例
然而令人迷惑的是，在Excel任何一个版本的功能区里，并没有直接与Power Query 扯得上关系的命令按钮，那是因为这家伙其实存在于一个叫作【Power Query 编辑器】的“异空间”中……

小妖把她骨子里的幽默和“妖风妖气“带入了这本书的字里行间，让人在觉得好玩有趣的同时，不知不觉学到了知识，幽默搞怪的网络用语，浅显有趣的叙说方式，让人耳目一新，把复杂繁琐的操作讲得清晰直白，让你轻松掌握Power Queyr的高级用法。
——Office中国站长，15次当选微软MVP 王宇虹

作者方洁影，江湖人称“小妖”，她的课程诙谐幽默，将一些“妖言妖语”穿插在内容中，使得她的授课有着非常独特的个人风格，现场气氛活跃，听过的学员必定会念念不忘。本书贯彻了她一贯的特色，在嬉笑间将各种技术干货娓娓道来，不失为一本学习Power Query的佳作，你值得拥有。
——ExcelHome论坛超级版主及讲师，微软Excel方向MVP 刘晓月

阅读此书，恰似一堂情景教学课，在小妖的带领下，由一个一个实际应用中的问题入手，让读者循序渐进地掌握Power Query。小妖的理念是对的，“学习Power Query的目的，是让我们在解决现实问题时更高效，而不是抛弃Excel本身”。正因如此，本书才更贴合实践，随手一翻就能找到“自己”的案例，深入阅读又能领略未知。
——微软Office方向MVP  法立明

在面对日益复杂的数据问题时，Power Query显得格外游刃有余，它比VBA和函数更加简洁且强大。但对于初次接触它的人来说，全新的处理方式、抽象的概念都让Power Query显得不那么友好。本书通过形象的类比，让抽象的概念更容易被理解、由浅入深的内容安排，也能让读者更容易理清Power Query的框架体系，轻松掌握这一数据处理的利器。
——资深Office培训师 王佩丰

微软Excel分析数据本身就很牛了，再加上Power BI系列组件，便更加博大精深！数据规范清洗是数据分析处理的基础，小妖老师的这本书，讲解幽默风趣，案例精彩实用，章节逻辑清晰，建议想深入学习Excel或Power BI的朋友们尽快入手。
——微软Power BI方向MVP，Excel Home云课堂讲师 赵保恒

作为一个经常跟数据打交道的人来说，面向数据的准备工作曾经十分繁重，消耗了我很多的时间和精力，现在则可以交付给Power Query技术来实现，而且可以跨越数百种我们熟悉的各种数据源，通过简单的拖拽和公式的组合，来实现曾经用代码才能达到的效果。小妖用她那幽默的语言和生动的教学，把这个强大的工具带来了！这本书打破了技术隔膜，让初学者也能快速驾驭这样一个厉害的工具，提升数据处理的效率。
——Teams Power Platform中文社区创始人，ExcelHome 论坛版主 刘钰

方洁影老师是ExcelHome云课堂的资深讲师，有着丰富的授课经验。如今，方老师在在线课程的基础上进一步提炼和优化，编撰出一本适合初学者入门的Power Query数据清洗实战宝典。学一技之长，为自己充电，方老师的这本书将成为你打开Power Query大门的钥匙。 
——Excel Home学员 吴松江

在便利性、学习门槛等方面，Power Query不失为数据处理新手的好工具。以前需要熟知分列、合并、转置，甚至编写VBA代码，才能完成数据的清洗整理，现在利用Power Query，只需要点几下鼠标就解决了。本书深入浅出，相信会给你的职业技带来快速提升，让你能轻松解决工作中的各种数据难题。
——Office中国论坛超级管理员，资深数据处理专家 RoyCh

本书架构清晰，结合大量企业案例，能够帮助大家更深入地了解使用Power Query清洗数据的精妙之处，强烈推荐财务、人力资源、电商等需要处理大量数据的朋友学习。希望通过对本书的学习，人人都成为称职的数据分析师。
AvePoint首席服务工程师，微软MVP刘俊哲

在这个人人都在说大数据的时代，有很多人在处理数据时不免心浮气躁。但有一群人始终默默地坚守初心，拒绝浮躁，坚持用合适的工具解决问题。小妖就是他们中出色的一员，一名将Excel工具用到出神入化的数据处理者，一名在社区和朋友圈子中热心帮助他人的授业者，一名现实生活中具有超级动手能力的姑娘。Power Query和小妖一样，看上去朴实无华却又强大无比，在PQ的世界中对数据的处理可以说是较好，所以不妨放下浮躁，跟随小妖一起进入PQ的”异度空间“吧！
——微软AI方向MVP 王豫翔

如果你是女生，不想学习太多函数，这本书很适合你，因为Power Query大部分功能都是基于鼠标操作；如果你是男生，想在女生面前秀技术，这本书很适合你，因为Power Query可以轻松地合并成百上千的工作表数据；如果你是在校大学生，想从零基础进行提升，这本书很适合你，因为是从基础开始引导读者进入PQ的技术海洋；如果你是职场人士，想要提升工作效率，这本书很适合你，因为PQ的技术将助你轻松应对各种数据处理的疑难杂症。
——微软Office方向MVP 邓华