问题的形成
作为输入数据，我们有一个 Excel 文件，其中一张表包含多个表格，其中包含以下形式的销售数据：
注意：
- 不同大小的表格，在行和列中具有不同的产品和区域集，无需任何排序。
- 表格之间可以插入空行。
- 表的数量可以是任意的。
两个重要的假设。 假设：
- 在每张表格上方的第一列中，有该表格说明其销售额的经理的姓名（Ivanov、Petrov、Sidorov 等）。
- 所有表格中的商品名称和地区名称都以相同的方式书写——精确到大小写。
最终目的是将所有表的数据收集到一个扁平的规范化表中，方便后续分析和构建汇总，即在这个：
步骤 1. 连接到文件
让我们创建一个新的空 Excel 文件并在选项卡上选择它 时间 命令 获取数据 - 从文件 - 从书 （数据 - 来自文件 - 来自工作簿）. 使用销售数据指定源文件的位置，然后在导航器窗口中选择我们需要的工作表并单击按钮 转换数据 （转换数据）:
因此，其中的所有数据都应加载到 Power Query 编辑器中：
步骤 2. 清理垃圾
删除自动生成的步骤 修改型 （更改类型） и 高架标头 （提升的标题） 并使用过滤器删除空行和总计行 空 и TOTAL 由第一列。 结果，我们得到以下图片：
步骤 3. 添加管理员
后面为了了解谁的销售额在哪里，有必要在我们的表中增加一列，每一行都会有一个对应的姓氏。 为了这：
1. 让我们使用命令添加一个带有行号的辅助列 添加列 – 索引列 – 从 0 （添加列 — 索引列 — 从 0 开始）.
2. 使用命令添加带有公式的列 添加列 - 自定义列 （添加列 - 自定义列） 并在那里引入以下结构：
这个公式的逻辑很简单——如果第一列下一个单元格的值是“产品”，那么这意味着我们偶然发现了一个新表的开头，所以我们将前一个单元格的值显示为经理的姓名。 否则，我们不显示任何内容，即 null。
要获取具有姓氏的父单元格，我们首先参考上一步中的表格 #“添加索引”，然后指定我们需要的列名 [专栏1] 方括号中的单元格编号和大括号中该列中的单元格编号。 单元格数将比我们从列中获取的当前单元格数少一 Index。
3. 剩下的就是用 空 使用命令来自更高单元格的名称 变换 - 填充 - 向下 （变换——填充——向下） 并删除不再需要的具有索引的列和第一列中具有姓氏的行。 结果，我们得到：
步骤 4. 由经理分组到单独的表中
下一步是将每个经理的行分组到单独的表中。 为此，在 Transformation 选项卡上，使用 Group by 命令（Transform – Group By）并在打开的窗口中选择 Manager 列和操作 All rows（所有行）来简单地收集数据而不应用任何聚合函数它们（总和、平均值等）。 P.）：
结果，我们为每个经理获得了单独的表格：
第 5 步：转换嵌套表
现在我们给出位于结果列的每个单元格中的表格 所有数据 体面的形状。
首先，删除每个表中不再需要的列 经理. 我们再次使用 自定义列 标签 转型 （变换——自定义列） 和以下公式：
然后，使用另一个计算列，我们将每个表中的第一行提升到标题：
最后，我们执行主要的转换——使用 M 函数展开每个表 表.UnpivotOtherColumns:
标题中的区域名称将进入一个新列，我们将得到一个更窄但同时更长的规范化表。 空单元格 空 被忽略了。
摆脱不必要的中间列，我们有：
步骤 6 展开嵌套表
使用列标题中带有双箭头的按钮将所有规范化的嵌套表扩展为一个列表：
......我们终于得到了我们想要的：
您可以使用以下命令将结果表导出回 Excel 主页 — 关闭并加载 — 关闭并加载... （首页 — 关闭并加载 — 关闭并加载到…）.
- 从多本书中构建具有不同标题的表格
- 从给定文件夹中的所有文件中收集数据
- 将书中所有工作表中的数据收集到一个表中