Power Query应用：汇总多个不规整的Excel表（比如简历、调查问卷、评分表、财务报表等）

一、应用背景

日常常见各种类型的多维表格，比如各类简历、调查问卷、登记表、工资单、财务报表等。

   

如何将这些不规整的多维表格，汇总生成如下格式的一维表格，以便后续进行各种分析呢？

下面介绍使用Power Query的方法，只需要半小时既可快速完成任务。

二、实现方法

1、分析原始表格内容

列标题：姓名、应聘岗位、参加工作时间、上一份工作离职时间、第一学历/学位、……

列标题所在位置（行，列）：（3，1）、（3，4）、（4，1）、（4，4）、（5，1）

列值：张三、产品经理、2015/7/1、2016/8/10、本科/学士、……

列值所在位置（行，列）：（3，2）、（3，5）、（4，2）、（4，5）、（5，2）

2、单个表格的实现

在Excel中导入数据到Power Query

将数据转换成list的列表

根据原始表格分析的结果，构建列标题的list

根据原始表格分析的结果，构建列值得list

生成结果表格

M语言代码如下：

 

let
    源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    降级的标题 = Table.DemoteHeaders(源),
    转为列表 = Table.ToColumns(降级的标题),
    标题 = List.Transform(List.Zip({{0,3,0,3,0,2,4,0,2,4,0,3,0,3,0,1,2,4,5},{2,2,3,3,4,4,4,5,5,5,6,6,7,7,9,9,9,9,9}}),each 转为列表{_{0}}{_{1}}),
    数据 = List.Transform(List.Zip({{1,3,1,3,1,3,5,1,3,5,1,4,1,4,0,1,2,4,5},{2,2,3,3,4,4,4,5,5,5,6,6,7,7,10,10,10,10,10}}),each {转为列表{_{0}}{_{1}}}),
    结果 = Table.FromColumns(数据,标题)
in
    结果

3、多个表格汇总

对单个表格的实现，封装成函数

遍历文件夹下每个文件，调用封装好的函数，提取每个文件的内容并进行合并

M语言代码如下：

let
    fx=(bin)=>
	let
            表= Excel.Workbook(bin){0}[Data],
            转为列表 = Table.ToColumns(表),
            标题 = List.Transform(List.Zip({{0,3,0,3,0,2,4,0,2,4,0,3,0,3,0,1,2,4,5},{2,2,3,3,4,4,4,5,5,5,6,6,7,7,9,9,9,9,9}}),each 转为列表{_{0}}{_{1}}),
	    数据 = List.Transform(List.Zip({{1,3,1,3,1,3,5,1,3,5,1,4,1,4,0,1,2,4,5},{2,2,3,3,4,4,4,5,5,5,6,6,7,7,10,10,10,10,10}}),each {转为列表{_{0}}{_{1}}}),
            结果 = Table.FromColumns(数据,标题)
	in
            结果,
    源 = Folder.Files("C:\Users\admin\Desktop\源数据"),
    合并 = Table.Combine(List.Transform(源[Content],fx))
in
    合并

生成结果表格