2.1 Power Query常用操作（上）

本节概述

Power Query支持的操作非常丰富，其中最高频的操作基本都在菜单栏的“主页”选项卡，以及主界面的右键菜单中覆盖，因此本节会先按顺序介绍一下这部分功能。

如下图所示，笔者将“主页”选项卡中的操控按钮分为六组，按顺序进行讲解。其中有些项目会与别的菜单项重复，大家可以按照自己的使用习惯选择相应的入口。此外，主界面的右键菜单中的高频操作也会按相同的逻辑分类，伴随这六个部分的讲解穿插进来。

1 查询相关

该区域中的各操作均与报表的查询（数据源）相关。

• 关闭并应用：关闭Power Query窗口，并将各查询中的变更操作推送至报表内（同时触发有变更的数据源刷新，如果当前各查询没有任何修改、则直接关闭窗口）。
• 新建查询：新建源（从头创建一个新查询，与PBI中的“获取数据”按钮等效）、最近使用的源（从本机最近使用过的源列表中选择并创建查询）、输入数据（像在excel中一样直接输入一组数据，常用于一些较固定的辅助表），这几个按钮在PBI的主菜单中也有出现，不论从哪里进入效果都是一样的。
• 数据源设置：设置数据源的权限设置，通常不需要
• 管理参数：可以创建参数并将其应用于查询步骤中，较为复杂，大多数人用不到，未来如果有机会可能会单独写一篇
• 刷新预览：PowerQuery中显示的数据表是第一次导入时使用前若干行生成的预览，若数据结构发生变化，则需要刷新预览才能将变化的字段读入。刷新时可以在下拉菜单中选择只刷新当前查询或是刷新全部。
• 属性：设置是否加载或参与刷新，个人更习惯使用查询窗格的右键菜单
• 高级编辑器：打开公式编辑界面，同查询窗格的右键菜单
• 管理：删除或复制数据源，建议直接使用查询窗格中的右键菜单有更丰富的选择

2 行列调整相关

在该区域除排序外，其余四个按钮都是对行或列进行删除操作的，下图中是它们对应的下拉菜单项：

删除列

因为在Power BI中所有数据都是列式存储的，对每个字段的操作均是直接作用于整个列。所以从菜单中可以看到列操作的可选项非常少。

选择列：以列表的方式选择要保留的列字段，效果同“删除其他列”

转到列：用于列较多时，可以通过搜索帮助定位到指定列，以便进行操作。

实际上在字段较少时，直接在数据区域选择一个多个列后，在字段标题处右键，即可以选择“删除列”或“删除其他列”，比在菜单栏操作更为便捷（见下图）。

删除行

在保留或删除行时，除了可以选择行的范围外，还有几项特殊操作：

删除重复项：保留当前列（一个或多个）中的非重复值

删除空行：删除全部列均为空的行。

排序

选中某一列后，可以点击排序按钮，让整个表按该列的值升序或降序排列

筛选过滤数据

对数据进行筛选过滤，实际上也是删除了符合指定条件的行，因此合并入行列调整相关。

在字段名的右侧，可以看到一个向下的筛选箭头，点击后会显示该字段的所有值，既可以通过勾选的方式选择要保留的记录，也可以通过规则进行筛选，如下图中文本型字段中会提供“文本筛选器”，而数值型则会提供“数字筛选器”，通过设置即可完成对数据的过滤。

该过滤会直接影响最终传送到报表中的数据，因此如果有完全不需要的数据，在这里进行过滤可以减小数据量。

空值及错误监控

如下图所示，在每个字段标题下方有一个绿色的色条、且长短不一，将鼠标移动到上方，可以看到提示信息如下图，显示了有效记录以及错误、空值的数量，因此当记录质量不高，有大量空值或错误时，可以一目了然地发现，及时干预。譬如在删除行中可以选择删除空行或删除错误等。

调整列顺序

在Power Query中要调整各列所在的位置，不必像excel中一样进行剪切粘贴，只需直接点击列标题进行拖拽即可。由于数据是按列存储的，列的相对位置并无实际意义，但在有多个列要进行相同操作的场景下，先把它们拖放到相邻位置上会比较便于选择和操作。

3 拆分列

相比excel，Power Query中可以有更多的分列规则，其入口在“主页-拆分列”选项卡和“转换”选项卡中，名称相同均为“拆分列”。

按数据变化规律分列

点击下拉箭头可以看到，支持的规则除了按分隔符、字符数外，还有按位置、按大小写或数字与非数字间的转换等规律来分列。

按分隔符、按字符长度分列

这两个分列方式与excel基本一致，并且还支持选择拆分的位置和次数，如下图所示，具体效果可以自行练习尝试。

按位置分列

该功能不太常用，可以指定要拆分的节点开始位置，将一列拆分为多列。需要注意的是位置编码是从0开始计数的，例如当前指标编码列中的内容是“SP.POP.65UP.TO.ZS”，假设想按小数点位置来分隔，从第一个S开始由0计数，每个小数点的位置是：2, 6, 11, 14。

如下图（左）设置并提交后，可以看到该列被拆分成了四列（右），最前面的SP丢掉了，因为该功能中要输入的是节点的开始位置，从2开始的话，就相当于从第一个小数点处开始截取了。

因此将设置的位置前端加上0之后，就可以看到如预期般将该列拆分为了5列。

将列拆分为行

在按分隔符、按字符、按位置等三种拆分的设置窗口中，还可以展开高级选项，选择将该列的内容拆分为行。拆分的效果是，假设按照设置原本可以将该列分为a1,a2,a3三列，则会将这三列的内容在同一列中按行展示，其余各列的记录复制填充。

例如我们导入一个测试数据如下图：

然后对col2按下划线拆分到行：

如下图所示，拆分后col2中的文本按列展示，而各行中的文本和数值都保持原样复制下来。该操作虽然很有用，但使用时要慎重，避免造成数据重复。

4 数据透视

“分组依据”按钮在“转换”选项卡中甚至被放在了第一位的位置，但个人认为该功能并没有如此重要，也从来没有正式使用过。大家如果了解也可以大致看一眼。概括来说这个功能就是指定一个或若干个字段作为透视的行字段，然后添加一些聚合条件，形成一个简单的透视表，感觉甚至不如excel的透视表灵活方便。

例如对如下数据表：

选择按年份进行分组汇总，设置如下

点击“添加聚合”可以添加多个自定义字段，通过在“操作”中选择聚合方式，在“柱”中选择聚合的字段，可以生成一个聚合后的表，如下图：

按年份对数据进行了行计数、“值”字段求和以及非重复行计数，形成三个统计字段。