clistctrl获取选中行数据_自动化报表——课程3：数据清洗

第三节课我们学习Power Query数据清洗。

实际上，不管你是否用到Power BI，只要你和数据打交道，都应该学会Power Query。作为Excel近年来的伟大发明，Power Query被嵌入到Excel 2016及之后的版本，而2010、2013版可以通过安装Excel插件的方式使用。

为了方便学习本节课程，本节课程将在Excel中进行。当然，这和在Power BI中没有任何区别。

课件下载地址：(这个链接长期有效，随课程更新)

链接：https://pan.baidu.com/s/1geoNlJbK0zsS7mlz9t5Cyg

提取码：k48f

**课件初次上载到百度网盘时，因为文件名中有“身份证”字样，原课件地址被屏蔽掉了，不得已换了个网盘的地址，所以导致本文删除后重发，万分抱歉**

上一节课我们了解到DAX函数处理数据与Excel处理的一点不同：Excel以单元格为单位处理数据，而DAX以行列为单位处理数据。这就要求数据的格式一定符合条件。杂乱的数据源通常被称为“脏数据”，只有被清洗成“干净的数据”，才能进行分析利用。PQ的强大在于，绝大多数简单的操作都是点点鼠标就可以完成，极大降低了学习门槛。

本文分三个部分，第一部分介绍数据获取，第二部分讲数据清洗的常见操作，第三部分简单介绍M函数。

更新Excel

公司在ftp服务器上提供13和19版本正版KMS激活，这里推荐大家更新到2013版。因为根据我的测试，OA控件最多支持到2016,而2016版公司不提供正版，2019和2013是一个KMS服务器不支持同时激活，所以安装2013版Excel最为省心。

在课件里可以找到PQ的安装包，安装后，按下图进行简单设置，就可以再菜单栏找到PQ了。

如果是16或之后的版本，PQ直接整合到“数据”选项卡中

看起来是不是和PBI中的获取数据很像？没错，实际就是一个产品。

第一部分 数据获取

进入正题。

数据源种类很多，工作中经常用到的除了Excel、CSV(导出的各类报表)、文本还有各种数据库格式等等。这里我们着重了解下从文件夹获取数据。

举个例子：5月底，公司要给分公司下6月份的考核指标，这个指标下达后允许分公司根据自身情况微调。于是有了下面这个表：

很明显，每个分公司都有一张这样的表，如果手动复制粘贴很麻烦。

有可能你有一段神秘的VBA“祖传代码”可以搞定，但其实有更为简单的方式。我们一步一步来。(课件是用2019版Excel做的，这里面提到通过数据选项卡的，13版都是在Power Query选项卡里，二者使用上是完全一致的)

首先我们加载课件中的文件夹1，如下图所示：

选中文件夹后，系统弹出对话框，有组合、加载、转换数据：

如果是从单个文件，一般点加载就好了，但是我们加载的是一个文件夹，如果直接点加载，显示的是文件夹里的“文件名”汇总，而不是“数据”的汇总。通常我们都是需要点转换数据进行编辑。

这里我们从简单的开始，点击“组合”，找到“组合并转换数据”

这时，系统弹出合并文件的页面，通常我们只需要选择其中一个文件，点击要合并的sheet表名称，然后直接点击确定。

系统自动为我们转换数据，并打开Power Query的编辑器界面，我们简单的熟悉一下，具体功能在第二部分详细介绍：

点击左上角“关闭并上载”，回到Excel中，这时候表格里显示出合并后的数据。

如果有数据更新，比如这时候扎旗分公司反馈了数据过来，只需要我们把表格拖到文件夹里，然后点击一下“刷新”就可以了。

我们可以直接对这个数据使用透视表进行透视，查看各分公司调整前后的变化情况。如果文件夹里的文件有更新，在透视表里点击右键-刷新就可以了。

数据汇总时让大家头疼的一件事：不管你如何三令五申不要改变表格格式，收上来的表插入列，调整列的顺序等等神仙操作层出不穷。

如果课件中，文件夹2里有3张表在不同位置插入新列，并且列名也不一样：

别担心，用刚才的方法，系统自动把你需要的列都汇总在一起。多出来的列删掉就可以了(如果点了刷新还会再出来。学会自己清洗数据可以避免)。

是不是很简答很方便？

适用场景

从文件夹新建查询，要求列标题一致即可，非常适合日通报的基础数据。因为这些基础数据要么通过固定的经分报表导出，要么用固定的查询语句从数据库导出，只需要简单处理就完成清洗。

还有一种场景是超过104万行简单数据分析。众所周知Excel不适合处理太过庞大的数据，借助PQ可以在Excel里简单处理。虽然远不如Access和BI好用。

比如我们新建一个查询，文件夹选择课件3(如果只有一个文件，直接从文本、CSV导入即可)，选择“合并和加载到”(如果是单个文件，直接选择加载到)，最后我们把它加载到“仅创建连接”

然后我们使用数据透视表，选择分析数据时选择“使用外部数据源”，点击选择连接按钮，在现有连接中找到刚创建的查询。

接下来，我们就可以开始透视了。把经办人编码放行上，值的计数项显示209万行

尽管Excel不适合处理大数据，但是创建百万级的外部查询还是可以凑合用用的。我在DAX圣经的网站里见到大佬创建了超过1亿行的数据查询，不知道PQ的上限是多少。

汇总合并其实并不经常在PQ中用到，上文只是简单举个例子方便大家理解。

刚才的示例中有一些小问题，比如刚才导入课件里的文件夹2，系统在自动处理数据的时候吞掉了几个列，这在有时候是很关键的。

这种情况我们可以在下发报表时通过Excel限制编辑区域和灵活运用数据验证功能来加以限制。当然，也可以通过下面的学习，通过“追加查询”的方式解决。

第二部分 数据清洗

Power Query的强大，主要体现在通过点鼠标能完成绝大多数数据清洗的操作。并且有大量操作都从Exce继承下来，非常友好。

PQ可以实现很多功能，而我的观点是，专业的工具做专业的事，这个系列课程定位是入门，所以只需要处理到可以用来分析就好了，不需要过度处理，在PQ和PP中都可以处理的操作，如果我觉得不适合在PQ里做，那我也不去讲。

本文将从“格式的处理”和“数据的处理”两方面进行讲解。

格式的处理

1、提升标题和填充

在Excel中，列标题本身占1行或者多行。

但是在PQ中，“行”里只有数据，标题在数据之上。

在PQ中，通常第一件事就是把首行转成标题，加载课件1、提升标题，选择“转换数据”，在编辑器中选择把第一行提升为标题：

有时候我们也需要把标题降下来作为第一行，通常在“转置”操作时使用。

进阶：有时候行、列标题中使用了合并单元格，在PQ中，是不允许出现合并单元格的，合并单元格里的第一个数据有效，其他均为null(空)。处理时我们可以使用“填充”功能。

加载课件《提升标题-进阶》，选择转换数据进入编辑器。

数据中出现大量null单元格，系统自动提升了标题，但是结果不是我们想要的。首先，我们选择“将标题作为第一行”，把标题降下来：

然后按住CTRL，选中前3列，在“转换”选项卡中，找到“填充”，选择“向下”，所有显示null的单元格被填充，效果如图：

然后再连续提升3次标题，得到干净的数据：

双击标题，可以为列标题改名。

2、更改数据类型

点击列标题前面的小图标，可以改变该列的类型，非常简单，如下图：

改数据类型很简单，看看就会，但是非常重要，养成良好的习惯，导入数据后首先检查下各列的数据类型是否正确，可以避免在数据建模时出现错误。

3、删除空行、错误行、重复项

加载课件3，编辑器里显示null的是空值、error的是错误值、还有一些需要剔重的数据(手机号)。在开始—删除行中可以将无效数据删除。

注意：这些操作都是针对某一列进行操作，所以在操作时要选中目标列。也可以直接在列标题点击右键操作。

4、合并列

加载课件1，按CTRL选中分公司 和 网格两列，然后在“转换”选项卡中找到“合并列”，点击进行合并操作，如图：

5、拆分列

在合并列左边找到“拆分列”按钮，常用的拆分方式有3种，分别是分隔符、字符和位置。和Excel逻辑基本相同，不再赘述。这里我们选择分隔符，确定后有三种拆分规则，如果只拆分一次，可以选择最左或最右出现时拆分，如果是拆分多次，可以选择每一次出现时拆分。

6、提取

在PQ中我们可以轻易的提取某一列中所需的字段，常见的提取规则有7种，其实熟练掌握“范围”即可涵盖所有提取操作。这里我们只需要了解该操作的用途即可，稍后我们在第三部分M函数中详细展开。

7、删除列，删除其他列

我们获取的数据源通常有很多个字段，对于我们用不到列，可以在PQ中删除，便于我们在后续的分析中理清思路。直接在标题行点击右键，或者在主页选项卡中找到“删除列”按钮，即可完成删除列操作。

效果：删除列 是删除选择的列；删除其他列 是保留选择列，其他列全部删掉。

在PQ中，如果某一个列是引用其他列数据得出的，删除该列后，对引用列没有影响。所以对于不需要的数据列、辅助列等等，我们可以放心大胆的删。

8、逆透视(降维)

加载课件8，我们看到一张行标题是分公司、列是日期和数值的表。现在我们选中“年月”列，在转换中选择“逆透视其他列”(当然也可以按ctrl选中3个分公司，然后选择“逆透视列”)，得到一张降维的表：

9、透视(升维)

接着上一步，我们选中“年月”、“属性”量列，在逆透视列左侧找到透视列按钮，弹出对话框列值选择值，确定后，表格升维，恢复成原表。

数据的处理

10、追加查询(合并表)

讲多个表，或者一个表的多个sheet合并到一张表。

加载课件10，这次课件里有4个sheet，我们选择多选，然后勾选，点击转换数据。

此时，编辑器的查询中有4个可用查询，即我们刚刚加载的4张表。我们要合并成一张表，在主页选项卡中找到“追加查询”。

追加查询有2种，一种是直接在选中的查询(即工作表)里把数据粘贴到下面，一种是建立一个新的查询。这里我们选择“将查询追加到新查询”，弹出对话框，选择“三个表或更多”，然后把4个sheet全部选到右边，点击确定，左侧查询目录中自动弹出汇总后的新查询。

11、合并查询(相当于Excel的vlookup函数)

PQ的这2个功能的起名非常有迷惑性，追加查询的功能其实是合并，合并查询的功能其实是vlookup：通过字段的比对获取其他查询的数据。

加载课件11，我们导入2个查询：

现在我们要在sheet2中添加一列“分公司名称”

我们点击“合并查询”，在2个表中分别选中“渠道编号”列，然后点击确定。

获得一个新列，单元格显示为Table，即表格的意思。

在PQ中，单元格里可以是一个文件、表格、或者数据，前者我们可以展开，点击右上角展开按钮，选中需要展开的那一列数据，点击确定。

在查询源较多的时候，我们也可以勾选“使用原始列名作为前缀”，这样列名前会添加查询的表名，但通常不需要这样。

12、六种连接类型

在“合并查询”的课件里，细心的朋友肯定已经发现了，在连接种类里，显示的是左外部。这是什么意思呢？为了讲清楚6种连接类型，我把模型简化到不能再简化，随便看一下即可轻松搞定。

加载课件12，我们假设在随身厅app里，可以实现A B C D E 五大功能，手机营业厅app里，可以实现C D E F G 五大功能。显而易见，有的功能二者都可以办，有的功能只有其中之一可办。从逻辑上可以分6种情况(用sheet1合并sheet2)：

1. 随身厅能办的业务：ABCDE(左外)

2. 手厅能办的业务：CDEFG(右外)

3. 随身厅和手厅共同能办的业务：ABCDEFG(完全外部)

4. 二者都能办的业务：CDE(内部)

5. 只有随身厅能办，手厅不能办的业务：AB(左反)

6. 只有手厅能办，随身厅不能办的业务：FG(右反)

理清这个逻辑，肯定能用的到。

13、条件列(if函数)

添加条件列，就相当于Excel的If函数

加载课件13，在“添加列”选项卡中找到条件列，输入评分规则：超过80分优秀、超过60分良好、60分以下不及格。

第三部分  M函数入门

本文第二部分熟练掌握，基本上可以应付大多数数据清洗工作了。随着学习的深入，我们要接触到PQ的高级玩法：M语言。就像学好Excel必须会用公式，会用VBA一样，PQ学习过程中不可避免的会用到M函数。

随便打开一个我们刚才练习的查询，在左边查询目录里，右键点击查询名称，然后选择“高级编辑器”，系统弹出一个对话框，这就是M函数。看，不知不觉间，我们已经写了好多行代码。

编辑器右边的应用步骤里，每一步操作都对应着一行代码

学习M函数，使得数据处理更加灵活、高效。作为入门教程，在这里我只介绍一些简单的函数，比如聚合函数和条件函数。

M函数的书写规范：

M函数和之前介绍的函数最大不同是，它对大小写敏感。也就是说，函数语句一定要分清大小写字母，否则不识别。

上面的课程里，我们提到表是Table。

每行的内容是Record，用大括号{ }表示。

每列的内容是List，用中括号[ ]表示。

取第一行“分公司”列的内容表示为：

=表{0}[分公司]

聚合函数：

求和：

List.Sum()

求最小值：

List.Min()

求最大值：

List.Max()

求平均值：

List.Average()

聚合函数好理解，相当于Excel的sum、min、max、average函数，其他聚合函数的表述也都差不多

文本函数：

求文本长度：

Text.Length()

相当于Excel的len函数

条件函数：

if    "判断条件" then  "结果为真时返回值"  else  "结果为假时返回值"

相当于Excel的if函数

提取数据函数：

从Excel表中提取数据：

Excel.Workbook()

从Csv/Txt中提取数据：

Csv.Document()

提取函数是M函数的特有表达

M语言很强大，现阶段我们只要知道以上几个就可以了，剩下的都交给鼠标搞定。

微软官方PQ M语言学习文档：

https://docs.microsoft.com/zh-cn/powerquery-m/

这一段比较枯燥，先简单了解一下。

下面我们进入练习，通过M函数整理数据。

加载课件练习1：身份证处理。因为课件里有2张表，所以选择从文件夹导入。

**课件中的身份证号都是通过rand函数随机生成，并非真实身份证号码，仅用于举例，如有雷同纯属巧合**

这一次我们直接点“编辑”(不同版本里也叫“转换数据”)，从最开始的数据源开始一步一步手动处理。

我们看到最左边有一个查询，即这个文件夹。最右边应用步骤里显示源，预览中展示的不是数据，每一行代表一个文件。

我们需要把这个文件中的数据提取出来，用到上面提到提取函数。在“添加列”选项卡中找到“自定义列”，输入：

=Excel.Workbook([Content])

这里注意区分大小写。Excel.Workbook数据有3个参数，第一参数是需要提取的文件，后2个参数可以省略。

找到添加完的自定义列，单元格里显示的是Table，即“表”。说明我们已经把表提取出来了。其他列都没有用，我们选中刚建的自定义列，选择删除其他列进行删除。

点击自定义列标题栏右侧“开展”符号，继续把表展开。

展开后我们看到6行数据。分别代表6个sheet。点到[Data]列可以预览数据，可以看到sheet1里的数据，是有用的数据，其他sheet表的数据是无用数据。

然后我们筛选Name列，只选中sheet1；

选中Date列，删除其他列；

最后展开Date列。

现在我们想要的数据已经展示出来了，先将首行提升为标题，因为导入了2个表的sheet1中都有标题，所以还需要筛选掉“身份证号”。

数据导入工作结束，足足有10步操作，但是一点都不麻烦，全程只敲了一行代码。

我讲的有些啰嗦，熟练掌握以后，半分钟内就可以完成。

绝大多数文件夹的数据源都可以通过以上步骤导入。文件夹的数据源非常好用，以后数据更新了，把文件直接拖进来就好了，不用每次都更改数据源。

接下来我们看看今天要处理需求：

1. 出生年月日

2. 年龄

3. 性别

4. 星座

5. 户籍所在地

简单分析下如何得出这些数据。身份证号中间8位是出生年月日，通过这个日期还可以计算年龄和星座；身份证前6位是地址信息；身份证倒数第二位的奇偶可判断性别。

需要提取的关键信息分别是：

1. 前6位

2. 中间8位

3. 倒数第二位

在“添加列”选项卡中找到提取-首字符，然后输入6，前6位就提取出来了。

继续点提取-范围，起始索引输入6，字符数输入8，中间年月日的区域就提取出来了。

在PQ中，第一行、第一字符等，都是0，这一点要注意一下。

同样的，选择提取-范围，起始索引输入16，字符数输入1，把倒数第二位提取出来。

基础数据提取完，我们来逐一实现需求。

首先，把年月日列名修改为出生日期，格式调整成日期

计算年龄就是用当前年份减出生年，新建自定义列，列名输入年龄，公式输入

= 2020 - Date.Year([出生日期])

如果还是觉得M函数的公式太麻烦，还有更简单的方法：选中出生日期列，然后再“添加列”选项卡中找到“日期-年-年”，PQ自动就把年份提取出来了，然后再自定义列，输入 “2020 – ” ，然后双击右侧年那一列，确定。搞定了，全程一个代码没敲。

现在我们来判断性别，刚刚我们提取的倒数第二位，默认是文本格式，要先改成整数。然后再“转换”选项卡中，找到“信息”，里面有判断奇数偶数的选项，比如我们选择偶数，这列所有偶数全显示为TRUE，奇数则是FALSE。再次修改格式为整数，偶数为1，奇数为0。

然后再“添加列”选项卡中找到条件列，列名输入性别，如果该列数字=1，则输出“女”，否则为“男”，性别列就创建好了。

然后再看星座。我刚补了下星座知识，星座是根据月份和日期来判断的。我们先用上面提取年的方式，把月和日提取出来。

6月是双子座和巨蟹座，时间是：

双子座：5月22日～6月21日

巨蟹座：6月22日～7月22日

这里有个小问题，刚才添加条件列，判断条件只有一个列，而这次需要判断2个列。

这里就需要用到if函数，先理清关系：月份列=5，并且天列大于等于22 或者月份列=6，天列小于等于21的，就是双子座；月份列=6，并且天列大于等于22 或者月份列=7，天列小于等于22的，就是巨蟹座；否则就是其他星座。

然后新建自定义列，输入下面公式：

if ([月份]=5 and [天]>=22) or ([月份]=6 and [天]<=21)then "双子座"else if([月份]=6 and [天]>=22) or ([月份]=7 and [天]<=22)then "巨蟹座"else "其他星座"

点击确定，星座列出来了。

在PQ中，if and or 这些函数的应用和Excel不一样，在表达上，and 和or可以直接连接条件，if后面跟的参数是条件，then后面跟满足条件时的返回值，else跟不满足条件时的返回值。其实跟Excel的基本逻辑是一样的，只是语法略有不同，实际上这种语法更为简洁，易读性更强。

户籍所在地，同样可以通过if函数来完成，也可以通过条件列。

最后把没用的列删掉，5个需求全部完成。

总结起来，最开始从文件夹提取文件的时候需要敲一行Excel.Workbook函数，多条件判断的时候需要自己写if函数，其他都是点点鼠标就搞定。

新的需求又来了！

现在需要统计各地区男女星座情况。

别担心，这次任务也超级简单。

在“转换”选项卡中找到“分组依据”，选择高级，分组依据分别选择星座和户籍所在地列，操作选对行计数，然后点击确定。

我们编辑的所有操作都显示在右侧应用的步骤里面，PQ中没有ctrl+z的撤销快捷键，如果哪一步有问题，我们可以在这里直接删掉。各步骤的顺序也可以任意调整。为了保持更好的可读性，还可以重新为这些操作步骤起名。养成良好的习惯，尽可能的简化处理步骤。

- End -

以上就是本节课的全部内容。

M语言其实非常强大，比如创建星座这列，M语言甚至直接有一个星座的函数，比我的这个土方法强多了。但其实也没有深入学习的必要，重点还是要放到DAX上面。

下一节课我们继续学习DAX函数的基础篇。

欢迎留言