power query 向下填充_Power Query技巧之合并字符串

横向合并

数据源：Sheet1 中的“超级表”命名为“表 1”，包含“省级”与“市级”两列数据。

目标：在不改变数据源和不添加辅助列的前提下，将两列合并成一列，如图 8-12 所示。

解决方案：利用【合并列】等功能解决。

第 1 步选取数据源表中数据区域的任意一个单元格，以【自表格/区域】的方式进入“异空间”。

第 2 步依次选取“省级”列和“市级”列后，单击【转换】选项卡下的【合并列】按钮，在【合并列】对话框中将【新列名】修改为“合并省市”后单击【确定】按钮，如图 8-13 所示。

第 3 步单击【主页】选项卡下的【关闭并上载】下拉按钮，选择下拉选项中的【关闭并上载至…】，在【导入数据】对话框中设置将查询表放置在现有工作表的C1 单元格。结果如图 8-14 所示。

整列合并

数据源：Sheet1 中的“超级表”命名为“表 1”，包含“省级”与“市级”两列数据。

目标：在不改变数据源和不添加辅助列的前提下，将“市级”列里的所有内容合并到一个单元格中，以顿号分隔，如图 8-15 所示。

解决方案：添加自定义步骤，利用M函数“Text.Combine”实现。

第 1 步选取数据源表中数据区域的任意一个单元格，以【自表格/区域】的方式进入“异空间”。

第 2 步右击“更改的类型”步骤，在快捷菜单中选择【插入步骤后】选项，添加“自定义 1”步骤。

第 3 步右击“自定义 1”步骤，在快捷菜单中选择【重命名】选项，将其改名为“合并市级列”。

上述操作步骤如图 8-16 所示。

第 4 步 将编辑栏的公式改写如下：

第 5 步单击【文本工具转换】选项卡下的【到表】按钮，将其转换成查询表。

第 6 步标题名由“Column1”修改为“所有市”。

上述操作步骤如图 8-17 所示。

第 7 步单击【主页】选项卡下的【关闭并上载】按钮，返回Excel界面。结果如图 8-18 所示。

去除重复后整列合并

数据源：Sheet1 中的“超级表”命名为“表 1”，包含“省级”与“市级”两列数据。

目标：在不改变数据源和不添加辅助列的前提下，将“省级”列里的所有内容去除重复后合并到一个单元格中，以顿号分隔，如图 8-19 所示。

解决方案：利用【删除重复项】和M函数等功能实现。

第 1 步选取数据源表中数据区域的任意一个单元格，以【自表格/区域】的方式进入“异空间”。

第 2 步选取“省级”列，单击【主页】选项卡下的【删除行】下拉按钮，在下拉选项中选择【删除重复项】，如图 8-20 所示。

第 3 步右击“删除的副本”步骤，在快捷菜单中选择【插入步骤后】选项，添加“自定义 1”步骤。

第 4 步右击“自定义 1”步骤，在快捷菜单中选择【重命名】选项，将其改名为“合并不重复省级列”。

第 5 步将编辑栏的公式改写如下：

第 6 步单击【文本工具转换】选项卡下的【到表】按钮，将其转换成查询表。

第 7 步标题名由“Column1”修改为“所有省”。

第 8 步单击【主页】选项卡下的【关闭并上载】按钮。结果如图 8-21 所示。

带条件的合并

数据源：Sheet1 中的“超级表”命名为“表 1”，包含“省级”与“市级”两列数据。

目标：在不改变数据源和不添加辅助列的前提下，“市级”列按各自所属的“省级”进行合并，合并到一个单元格里的市级以顿号分隔，如图 8-22 所示。

解决方案：利用【分组依据】等功能实现。

第 1 步选取数据源表中数据区域的任意一个单元格，以【自表格/区域】的方式进入“异空间”。

第 2 步单击【主页】选项卡下的【分组依据】按钮，在弹出的【分组依据】对话框里按“省级”分组，新列名设置为“合并市级”，需要合并的【柱】(列)是“市级”。其中最重要的【操作】，因为没有与合并相关的内容，只好先由“求和”暂代，如图 8-23 所示。

第 3 步修改M公式，将其中的“List.Sum([市级])”部分改成“Text.Combine([市级], "、")”，如图 8-24 所示。

完整的M公式如下：

第 4 步单击【主页】选项卡下的【关闭并上载】按钮完成操作。结果如图 8-25 所示。

不规则结构的合并

数据源：Sheet2 的表中包含“省级”与“市级”的数据，其中“省级”列包含合并单元格，“市级”按 6 列、不定行数排列，如图 8-26 所示。

目标：在不改变数据源和不添加辅助列的前提下，“市级”列按各自所属的“省级”进行合并，合并到一个单元格里的市级以顿号分隔。

解决方案：利用【逆透视列】等功能实现。

第 1 步选取数据源表中数据区域的任意一个非合并的单元格(如B2)，以【自表格/区域】的方式导入“异空间”。

第 2 步选取第一列后单击【转换】选项卡下的【填充】下拉按钮，并从下拉选项中选择【向下】，如图 8-27 所示。

第 3 步选取第一列，单击【转换】选项卡下的【逆透视列】下拉按钮，在下拉选项中选择【逆透视其他列】，如图 8-28 所示。

第 4 步选取“属性”列，单击【主页】选项卡下的【删除列】按钮。

第 5 步单击【转换】选项卡下的【将第一行用作标题】按钮。

上述操作步骤如图 8-29 所示。

第 6 步单击【主页】选项卡下的【分组依据】按钮，在弹出的【分组依据】对话框里，按“省级”分组，将【新列名】设置为“合并市级”，操作仍是用“求和”暂代，需要合并的【柱】(列)是“市级”。

第 7 步修改M公式。

第 8 步单击【主页】选项卡下的【关闭并上载】按钮完成操作。结果如图 8-30 所示