2.2 Power Query常用操作（中）

本节概述

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

由于相关功能比较繁杂，上一篇（2.1 Power Query常用操作（上））只讲述到第4部分，本文将继续介绍第五和六部分的操作。

5 数据转换相关

变更数据类型

之前我们曾提到过，在数据导入时Power Query会根据数据特征自动识别各字段的数据类型并加以应用，即步骤中的“更改的类型”，但有时程序识别的未必准确，或者有的字段类型需要结合业务来判断，譬如各类id即使识别为整数型，也没有实际意义，往往也可以置为文本。所以在创建查询后，通常第一步是先观察各字段的数据类型是否准确，并加以调整。

在数据区域中我们可以看到，每个字段名的左侧有一个小图标，即是程序判断的数据类型，点击该小图标就可以在菜单中选择要将字段调整为的类型。

在字段名称上右键-更改类型 同样可以进行类型更改，如下图所示，该方法更多是用于同时调整多列的类型（与excel中相同，按shift选择相邻列，按ctrl选择不相邻的列）。

选中数据列后，点击菜单栏中“数据类型”的下拉箭头（如下图），也可以变更数据类型，但个人认为还是上述两种右键的方法更为便捷。

标题与数据的转换

在导入数据时，通常PowerQuery会自动执行的一个步骤是“提升的标题”，即将数据表的第一行作为列标题。但这是建立在导入的数据的标题与数据存在显著的格式差异的情况下才会执行的，对于纯文本的维度表，大多数时间会被识别为一个无标题的表，如下图所示，国家表的表头被认为是一行数据，这种情况下，可以在“主页”或“转换”选项卡中找到“将第一行用作标题”按钮，点击后即可将第一行提升为标题。

反过来，如果原始数据确实是没有标题，但被程序误判了，那么也可以点击按钮旁边的下拉箭头，在菜单中选择“将标题作为第一行”，将标题降回数据。对系统默认赋的column1column2等标题，可以直接右键改名。

查找替换

选中一个或多个列后，在列标题右键菜单中可以选择“替换值”（“主页”和“转换”选项卡中也有同名操作按钮），点击后可以设置要查找和替换的内容，与excel中相同、就不赘述了。

6 多表关联

合并查询

查询操作流程

可以凭借一个或多个关联字段，将两个表横向拼合起来，类似我们在excel中使用vlookup获取其它表的字段，区别是支持多字段关联、可以一次性获取目标表中的全部字段，而且目标表中有多条记录时可以同时获取。

例如我们目前有如下两张表：

人口总数：表中有国家名称和编码，年份以及指标值。

国家表：表中有国家名称和编码，以及国家所属地区和收水平分级

很明显，这两个表中可以使用国家编码来建立连接，那么我们点击菜单中的“合并查询”并如下设置：

该窗口中，上方的表是我们进入窗口前所选中的表，通常把它称为“左表”，下方下拉框中可以选择要与之拼合的表，也就是所谓“右表”，这里我们选了“国家表”。依次点击两表中要用于关联的字段（country code），可以看到对应字段变成了深灰色，联接种类选择了“左外部”（联接种类在后文中解释），这时可以看到下方提示16276行数据均可以完全匹配到。

多字段连接的情况：假设我们的数据需要使用多个字段来进行关联匹配，那么可以在设置完第一组关联字段后，按住ctrl键，再依次点击两表中的第二组关联字段，例如coutry name 这时会看到字段名旁边会出现序号角标，以表示它们的对应关系。

当完成该操作后，回到数据预览窗口，可以看到人口总数表中新增加了一列“国家表”，内容显示为table，其标题右侧有一个双向拐弯箭头的标记。

点击该标记后，可以在展开菜单中看到国家表中包含的全部列，可以选择需要展开的列，如region和income_group：

点击确定后，如下图所示，这两列即拼合在当前数据表中了。

如果在上一步中取消“使用原始列名作为前缀”的勾选，则展开的列名称中将不会带有“国家表.”的前缀。

联接种类

在设置联接时，有如下六种联接种类。其中左外与右外、左反与右反是逻辑相同方向相反的两组操作，所以事实上只要理解一种就可以了。

左外：相当于sql中的left join，不论是否能匹配，都保留左表中的每一行，若未能在右表中找到匹配项，则在对应的拼合字段中返回空值，可以理解为使用vlookup时返回#N/A

完全外部：即sql中的full outer join，即不论能否匹配，均保留左右表中的每一行，在对应表未找到匹配的字段时返回空值。

内部：即sql中的inner join，只保留两表中能完成匹配的记录

左反：相当于使用右表对左表做过滤，只保留未完成匹配的记录

概括说来，就是对外连接的一方要保留全部行，相应的内连接的一方是过滤未匹配的行，对于无匹配的记录，在对应字段中返回空值。

追加查询

追加查询用于将结构相同的两张以上数据表纵向拼合成一张表，在该操作中要求各表具备相同的数据结构。只要各表中的字段名称相同即可被识别为一列，并不限制字段的显示顺序。而若字段名称不同，结果表会拥有全部的字段，只是在缺失部分会显示为空值。

例如以下三张表：

0-14岁：字段依次为 country name,indicator name,年份,值

15-64岁：字段依次为 indicator name,country name,年份,值

65及以上：字段依次为 country name,country code,indicator name,年份,值

可以看到，第一二张表的字段顺序不同，第三张表甚至还多了一列country code。

接下来选中表0-14后，点击菜单栏中的“追加查询”，如下图所示，在弹出窗口中选择“三个或更多表”，在可用表列表中将另外两张表添加到要追加的表中，点击“确定”

可以看到追加过后，表0-14中变成了五列，只是countrycode列显示为全空，这是因为预览只有1000行，而该列只在第三张表中有，因此预览时并未显示出来。

点击筛选下拉箭头，点击“加载更多”，即可在窗口中看到该字段其实是有值的。

同样的，点开indicator name的筛选框，也可以看到，尽管该列的位置不一致，但都准确地拼合进来了。

将查询合并 /追加为新查询

上述两项操作，都是直接在当前表上进行了表的拼合操作，在实际使用过程中，对于初学者、或是需要保留原始数据的情况，可以点击这两个按钮旁边的下拉箭头，选择“将查询合并为新查询”或“将查询追加为新查询”，则会将拼合操作生成一个全新的查询，不会影响原有的数据，不易出错。