如何使用Power Query将多工作表不规范数据转换为标准可用数据表

前置准备 #

• 打开目标Excel文件，点击「数据」选项卡-「获取数据」-「自文件」-「自工作簿」，选择当前文件路径后确认，在导航器中同时勾选STORE1、STORE2、STORE3三个销售工作表，以及已有的产品-类型映射表，点击「转换数据」进入Power Query编辑器。

单门店表结构标准化 #

三个门店表结构完全一致，按以下步骤逐个处理即可：

• 修正表头：若导入后表的第一行是原表的字段名（产品ID、产品名、1月、2月…月度字段），点击「开始」选项卡的「将第一行用作标题」，自动识别字段名。
• 新增门店编号字段：点击「添加列」-「自定义列」，列名设置为门店编号，自定义公式输入="STORE1"后确认。处理STORE2、STORE3表时，公式内的门店编号对应替换为STORE2、STORE3即可。
• 逆透视生成统一月份列：按住Ctrl选中「产品ID」「产品名称」「门店编号」三个固定维度列，右键点击选中的列头，选择「逆透视其他列」；此时自动生成两列，将属性列重命名为月份，值列重命名为月度销售额；筛选「月度销售额」列，剔除值为null的无效空行。

三店数据批量合并 #

• 在Power Query编辑器左侧查询面板右键，选择「新建查询」-「将查询追加为新查询」，弹窗中选择「三个或更多表」，把结构标准化后的三个门店查询添加到右侧待追加列表，确认后自动生成三店全量销售明细查询。

关联映射表补充产品类型 #

• 选中刚生成的三店合并明细查询，点击「开始」-「合并查询」，在合并配置弹窗中：第一步选中明细表的「产品ID」列，第二步下拉选择产品-类型映射表，选中映射表的「产品ID」列，联接种类选择「左外部（保留第一个表的所有行，匹配第二个表的对应行）」，确认生成合并列。
• 点击合并列头右上角的展开箭头，仅勾选「产品类型」字段，取消「使用原始列名作为前缀」的勾选，确认后自动匹配填充所有行的产品类型信息。

结果校验：最终输出表应包含门店编号、产品ID、产品名称、产品类型、月份、月度销售额共6个字段，抽样核对门店归属、月份销售额、产品类型匹配无错误后，点击「关闭并上载」即可导出规范的明细数据到Excel工作表。

内容的提问来源于stack exchange，提问作者Smorg