PowerQuery数据合并技巧详解

在Power Query出来之前，用户要想将结构相同的多张工作表（工作簿）合并成一张工作表，或者将多个工作表合成一个工作簿，常用“复制-粘贴”的办法，有时也使用函数，但效果差、效率低。也有较高端的用户用VBA合并工作簿、合并工作表，但对一般用户则很难将VBA应用到工作表中。但Power Query则很容易解决此类问题。

1.合并工作表

【例8-18】　如图8-57所示为龙脊山集团公司门店数据信息，分别存放于工作簿的Sheet 1、Sheet 2和Sheet 3中。三张门店信息表的结构相同，现在需要将三张工作表合并到一张完整的工作表中，使用Power Query可轻松完成。当然，即使是300张工作表，操作方法亦然。具体操作过程如下。

图8-57　龙脊山集团公司数据信息

Step 1：选择任意一张工作表（也可以新建工作簿）→单击功能区中的【数据】/【获取和转换】/【新建查询】/【从文件】/【从工作簿】选项→从弹出的资源浏览器中找到并选择需要合并的工作簿“第八章 Power Query合并”工作簿→单击【确定】按钮（图8-58①）。

Step 2：在弹出的导航器中有如下两种选择。

①直接选择工作簿，表示直接导入工作簿查询。

②单独选择sheet 1、sheet 2和sheet 3表，每次只能查询一张工作表。

此步骤中选择“第八章 Power Query合并”工作簿，单击【编辑】按钮，如图8-58②所示。

图8-58　选择需要合并的工作簿

Step 3：将工作表加载到【查询编辑器】→修改查询属性为【合并工作表】→单击【Data】字段右侧的展开按钮，可以展开每个工作表的详细字段列表→直接单击【确定】按钮，如图8-58③所示。

注意：如果是在当前工作簿中合并表格，则应先添加好存放结果的工作表，如sheet 4。在进行到此步时，在“Name”这个字段中需要筛选取消这个被合并的工作表sheet 4。如果是在空工作簿中查询合并其他工作簿，则不需要进行此操作。

Step 4：在合并展开后的表格中，字段标题都是不需要的，第一行的参数才是正确的字段标题，这时可以单击功能区中的【开始】→【转换】→【将第一行用作标题】/【将第一行用作标题】选项，获得新的字段标题，如图8-59所示。

Step 5：此时，合并后的表格仍然会有多余的标题行，选择一个项目少的列字段，筛选时取消项目中的标题项，这里取消“商品编码”字段里的“商品编码”选项的勾选，如图8-60所示。

图8-59选择【将第一行作标题用】

Step 6：获得的结果仍然还有其他多余的列，需要将它们删除。可单击功能区中的【开始】→【管理列】→【选择列】选项，在弹出的列表中选择需要保留的列，取消选择需要删除的列→单击【确定】按钮。如果需要删除的列数比较少，则可以直接选择字段后右击，在弹出的快捷菜单中选择【删除】选项（图8-61）。

(www.xing528.com)

图8-60　取消“商品编码”选项

图8-61　删除多余的列

Step 7：获得最终的结果后，单击功能区中的【开始】→【关闭并上载至…】选项，完成查询操作，如图8-62所示。

图8-62关闭并上载查询结果

2.合并工作簿

不同工作簿汇总数据和同个工作簿汇总数据的原理一样，只是原始数据位置不一样。在企业里面的数据库导出的数据，大部分都是分开放在不同工作簿里面分开存储，也方便管理。

【例8-19】　如图8-63所示为龙脊山集团公司门店数据信息，分别存放于合川、永川和万州三个工作簿中。三张门店信息表的结构相同，现在需要将三张工作表合并到一张完整的工作表中，使用Power Query可轻松完成。当然，即使是多个工作簿，操作方法亦然。具体操作过程如下：

图8-63　合并表与原始工作簿数据

Step 1：首先回到汇总Excel数据文件夹目录下，新建一个名为“合并表”的Excel文件与数据源呈并列关系。

Step 2：打开“合并表”Excel文件，在【数据】/【新建查询】/【从文件】下选择【从文件夹】（图8-64①）→浏览数据源所在文件夹→找到包含“合川”“万州”和“永川”的文件夹“明细表（图8-64②）”→单击【确定】（图8-64③）。这样就选定了文件夹路径，再点击【文件夹】下边的【确定】进入数据编辑状态。

图8-64 工作簿选取过程

Step 3：点击文件（图8-65①）文件列表下边的【组合】→选择【编辑并加载】（如果直接选择加载，则加载图示的文件名列表）→在【合并文件】对话框中，单击【确定】，进入【Power Query编辑器】状态。

图8-65　数据合并与加载过程

Step 4：如果此时没有将打开的工作簿导入，则可点击【组合】/【追加查询】/【追加查询】→进入【追加】对话框→选择追加的表名→单击【确定】再回到【Power Query编辑器】状态。如果需要编辑，可以同合并工作表的方法，筛选取消勾选空值和标题行，再点击【关闭并上载】即可。