2.3 Power Query常用操作（下）

本节概述

在之前的两篇文章(2.1 Power Query常用操作（上） 和 2.2 Power Query常用操作（中） )中已经依次介绍了“主页”选项卡中的各项操作，当中的许多功能实际上都是与其余选项卡中的功能重合的，因此本文会对菜单栏其余几个选项卡中的常用操作进行最后的讲解。为了查找起来方便，我们依然按照菜单栏布局来介绍。

“转换”选项卡

“转换”选项卡中的各项操作，都是针对所选中的列进行操作，执行结果直接作用于选中的列，过后我们会在“添加列”选项卡中见到很多同名的按钮，两者的区别就在于“添加列”中的操作相当于是复制一列并在其中执行我们的操作。对初学者来说，建议尽量多使用添加列的方式，保留原始列可以方便查错。

观察菜单中的操作项目，如上图所示，添加阴影的部分是前文中已经提到过的，在此就跳过了。画红框的是相对使用会多一些的功能，会详细举例讲解，其余会在最后统一简单解释一下。

填充(空值)

在遇到空值时，如果仅仅是想填充为指定值的话，使用替换值的方式即可完成，但有时可能需要使用其相邻行的值进行填充（例如时间序列、合并单元格拆分后出现的空行等），这时可以使用“转换”选项卡中的“填充”功能，可以选择向下或向上，即可将单元格中的值向下或向上填充至相邻的空白格中。

选择向下填充和向上填充后，会按照指定的方向用相邻的数据进行填充，同时可以看到，填充后字段名下方的色条完全变绿，数据已100%有效。

透视列与逆透视列

透视列与逆透视列是互逆的两个操作，实现行列内容的互转，因此我们放在一起来看。

逆透视

以下表为例，本表有country name,indicator name等两个维度列，另外有1960-2021的人口占比指标值，每年成一列，共计62列数据。

这种数据组织形式无疑是很不方便的，常规的做法应该是设置一个维度列来标识年份，另外有一个值列用于存放指标数值。而这么大的一个已经成形的表格，当然不可能靠人工再来调整了，此时就需要强大的“逆透视”功能了。

首先选中1960-2021的这62列，点击菜单栏中的“逆透视列”，如下图所示，之前那些数据列变成了两列，“属性”中是各列的标题、也就是我们想要的年份，“值”中则是对应当年的指标值。仅仅鼠标点击一下，就完成了整个表格的重构。

要实现上述效果，我们还可换一个做法，可以选中country name 和 indicator name两列，点击按钮旁边的下拉箭头，选择“逆透视其他列”，则程序会自动对剩余的各列进行逆透视操作。对于数据列比较多，不方便选择的场景，这样操作会更为简易。

透视

接下来，我们尝试把刚才逆透视得到的表格再进行透视操作来恢复原状。选中“属性”列，然后点击“透视列”，则弹出窗口。首先需要选择“值列”，即透视后的值由哪一列来获取。这里可以联想一下在excel中做透视表的时候，被透视的列就是我们拖放到“列”中的字段，而此处选择的“值列”就是我们要放在“值”中的字段。

高级选项中，可以选择对该列进行聚合的依据，这里跟excel中基本是一样的，数值类默认是求和，我们也可以依据统计需要选择其它函数，在本例中当然是选择求和即可得到和原始表格完全一致的结果。

文本透视

在excel透视表中，对文本内容只能计数，而power query中也可以透视和逆透视文本，在某些场景下也是相当方便的。例如对下图的值班表：

对“班次”字段进行透视，值列选择“人员”，聚合方式为“不要聚合”，就可以得到下图右侧所示的排班表：

格式

格式按钮的下拉菜单中，可以看到能对选中的列进行如下操作：

其中小写、大写、首字母大写的作用一目了然，就不多说了，谈一下其余几项：

“修整”是去除列中全部的前导和尾随的空格，相比替换值的方式，该操作可以保留文本中间的空格；

“清除”是清除列中的全部非打印字符，如回车、换行等；

添加前缀和后缀：给该列添加一个指定的前缀或后缀文本；

提取

“提取”有如下所示的七种提取方式：

长度：相当于excel的len函数，获取文本的长度

首字符、结尾字符：相当于excel中的left和right函数，从头或尾开始提取指定长度的字符

范围：相当于excel的mid函数，指定开始位置的索引和要提取的文本长度并进行提取，需要注意这里的索引也是从0开始计数的

分隔符之前(后/间)的文本：可以指定分隔符，获取其前/后或其间的文本，在高级选项中可以选择扫描的方向以及是否要跳过若干个分隔符。如下图，我们对指标编码列，设置分隔符为小数点，取分隔符之前文本，跳过3个分隔符，可以看到从第4个小数点处进行了切分，返回了前段的文本。

日期时间

power query提供对日期型、时间型和持续时间（即一个时间范围）三种类型的数据分别提供了一系列的格式转换功能，当选中对应格式的数据列时，才能激活对应操作菜单。

日期转换

例如下图是选中某日期列之后，“日期”按钮被激活，点击下拉菜单可以看到有若干组的操作可选。最常使用的是中间的“年”到“天”之间这部分，它们分别都带有下级菜单，可以选择提取对应单位区间的起、止日期、转换为名称等。最下方的“最早和最新”是统计类功能，可获取该列的最小和最大值，不过通常不建议在power query中做这些数据观察类的操作。

时间转换

时间菜单如下图所示，跟日期差不多，就不细说了。

合并日期和时间

在日期和时间的菜单中，可以看到“合并日期和时间”项都是灰色的，当我们同时选中一个日期列和时间列时，该选项就会变为可选，执行后会将两列拼合在一起。

持续时间

持续时间的菜单项中，第一部“天”到“秒”部分，是对时间段中该单位对应数值的提取，第二部分“总年数”到“总秒数”是将该时间段折算为该单位得到的值。如下图所示，第二列是只取小时部分的效果，第三列总小时数则是直接折算成了小时数。

其它不常用功能

转置：将表格行列互转，由于分析中常用的都是数据表，基本不会使用该功能

反转行：将表格的行逆序排序，也是基本不会用到

对行进行计数：返回数据表的行数，仅返回一个统计值用于数据观察，基本不会用到

检测数据类型：数据在读入时已经自动检测过了，基本不会使用到

重命名：在字段名上右键更改会更方便，基本不会用

移动：除了列的数量特别多时可以选择移到开头或末尾比较方便外，其余场景下都不如直接拖拽

转换为列表：将选中的列转为列表，相当于删除其余列，所以不常用

分析：可以解析XML或JSON

统计信息：进行行计数、最大最小值、均值、标准差等常规统计，直接返回一个统计值，只能用于临时的数据观察，所以基本不会用到

“添加列”选项卡

到了添加列选项卡，可以看到只剩下极少数之前没有提过的操作了。当然，还是要再强调一下，阴影部分这些操作基本都在“转换”选项卡中有同名按钮，但此处的操作都是相当于先复制一列，再进行对应操作，即原始数据列不会受到影响。

条件列

可以通过指定不同条件下的返回值，来形成一个新列，相当于if函数，或是一系列if的嵌套。既可作用于文本类也可以用于数值类。

单条件：

首先看单条件的情况，例如有如下表格，可以看表两个类别字段都不完整，假设我们希望在“类别”列不为空时取自身，为空时取“类别2”。

那么就可以点击“条件列”，按下图设置，给新列起一个名字，在if条件区选择要比较的字段为“类别”，运算符为“等于”，值为null。当符合该条件时，输出处选择列“类别2”，在else的输出中选择类“类别”即可。

注意绿色框的三处，均可以下拉选择是手动输入一个值还是选择某个列的值。

“运算符”的下拉选项中还有开头、结尾、包含等适用于文本筛选的选项，常用于添加辅助的分组字段。

点击确定后，得到拼合后的新列如下

多条件

在条件设置窗中，我们可以看到有“添加子句”字样，通过添加多个子句，可以形成多重嵌套的效果。例如在上表中，选中“值”列，点击添加条件列，按如下设置，注意每个条件在被鼠标选中时，右侧都会出现三个点，点击可以在菜单中选择调整条件的排序。

设置完成后，提交，即可看到按照值列的数值，完成了数据分层。

示例中的列

用于在不知道该如何操作时，通过给出一个示例文本，由程序判断其中的规则并进行填充，如下图，在列1中填写了“编码：ABW”,程序识别到ABW来自国家编码列，于是判断给该列添加前缀“编码：”

自定义列

输入公式生成一个新列，不过由于菜单操作已经基本能满足大部分操作需求了，通常不需要专门再去学习相关公式了。

索引列

给表格增加一个索引，可选择从0或1开始

重复列

生成选中列的副本

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！