轻松搞定复杂表单数据，快速提升办公数字化能力

“IT有得聊”是机械工业出版社旗下IT专业资讯和服务平台，致力于帮助读者在广义的IT领域里，掌握更专业、更实用的知识与技能，快速提升职场竞争力。 点击蓝色微信名可快速关注我们。

数据处理是数据分析的奠基石，只有使用处理干净的数据，分析才会产生价值。简单而言，数据处理的终极目的是将非结构化数据转换为结构化数据。虽然数据处理自身未必直接产生数据价值，但其过程往往相当耗时，因此如果无法高效完成数据整理任务，必将影响最终的数据分析进程。通过本文内容的学习能帮助大家进一步提高数据整理的能力和效率，达到事半功倍的效果。无论您在使用Excel或Power BI，都可以用本文讲解的技巧。

处理结构化复杂表单数据

Conquer Power BI

• 如何对标题与内容同行的表进行结构化处理？
• 如果对标题与内容同单元格的表处理？

在日常业务中经常遇到类似图1这种非结构化的数据报表,经过数据处理后，非结构化表将被转换为图2的结构化数据表。本文将介绍两种常用的表单处理方法。

图1.非结构化的原始数据表

图2.经过结构化处理的数据表

01

处理标题与内容同行的数据表

以【文件夹】类型获取两个示例文件，再用Excel.Workbook函数提取表内容，为了行文方便，我们可以右击展开应用的步骤，选择【重命名】选项简化名称（此处改为GET），见图3。

图3.获取的非结构原始数据

展示一个示例以方便读者理解，单击fx图标添加一个新步骤，然后添加以下M函数公式，代码的作用是获取GET步骤中的[Data]列数据，{0}代表第一个表，{Column2}{1}对应企业名字内容所在之处，见图4。

图4.直接通过M函数获取对应的单元格信息

#table({"企业名称"},{{#"GET"[Data]{0}[Column2]{1}}})

通过以上方法，便可按规律获取其他相对应字段信息，删除GET之后的步骤，点击【自定义列】选项并添加以下完整的M代码，代码中的 [Data]是对上一步中的相关列引用，用户可在快照中观察提取的数据内容，见图5。

#table(
{"企业名称","培训项目","培训日期","联系人","培训费用","电话","邮箱"},
{{
[Data][Column2]{1},[Data][Column4]{1},[Data][Column6]{1},[Data][Column2]{2},
[Data][Column4]{2},[Data][Column6]{2},[Data][Column2]{3}
     }})

图5.添加自定义列中的M公式

生成自定义列后，我们便可以删除【Data】列，并将【自定义】列展开，见图 6。最终的结果见图 2。

图6.查看自定义列中的Table结构数据

02

处理标题与内容同单元格的数据表

另外一种典型的非结构化表结构是标题与内容处于同一单元格中。 在图7中，行2和行3为合并单元格，本例介绍将员工数据进行结构化处理，见图8。解题思路是将表头与表身进行分别的处理，然后将它们合并。

图7.非结构化的原始数据

图8.处理完成的结构化数据表

与前面示例相似，我们以【文件夹】类型获取两个示例文件，这里的唯一特殊地方是我们会保持工作簿【Name】字段作为后边的合并之用，见图9。

图9.通过文件夹形式读取数据后并提取其中的Table

此处会把原来的表改名为【Body】，并进行复制并改名为【Headers】，见图10（读者可自行改名）。

图10.复制该查询

继续处理【Body】查询，为其添加【自定义列】并添加以下M函数公式，处理结果快照见图11。

图11.添加自定义列获取Table中的结构化数据

Table.RemoveLastN(Table.PromoteHeaders(Table.Skip([Data],3) )  ,1)
/*自定义列其实是有3个M函数的嵌套组合而成，最内侧Table.Skip表示将表头进行剔除、中间层的第2个Table.PromoteHeaders表示将表头提升、最外围的Table.RemoveLastN是去除第7行的冗余信息.*/ 

将自定义列展开并且保留始终保留【Name】字段，见图12。至此，已经完成了表身的数据处理。

图12.完成表身的结构化数据处理

接下来我们要处理表头的数据，返回前面复制的【Headers】并选中该查询，为其添加【自定义列】，并添加以下M函数公式，见图13。

图13.进行表头的结构化数据处理

Table.Range([Data],1,2)
// 这段代码是提取Excel行2与行3的员工信息

提取完成后，展开【自定义列】，提取原表头的员工信息、职位、员工号码等信息，参考图 14仅保留相关列。

图14.进行拆分为行操作

将【Column1】中所有的空值进行筛选后去除，然后进行【按分隔符拆分列】操作，分隔符为【冒号】，这样便将表头信息描述和具体信息名称拆分为两列了，见图 15。

图15.进行拆分为列操作

选中【Column1.1】，并对其进行透视操作，【聚合值函数】为【不要聚合】方式，单击【确定】按钮，结果见图 16。至此，完成了对表头的数据处理。

图16.进行透视列操作

依据【Name】字段，对两个表进行合并操作，在菜单中选择【合并查询】-【将查询合并为新查询】选项，见图13

图13.将表头与表身进行合并查询操作

在【合并】对话框中分别选中【Body】和【Headers】中的【Name】字段，然后使用【左外部】联接种类，点击【确定】按钮，见图 14。

图14.对二表进行左外部合并操作

合并完后将【Headers】字段展开并选择相关的字段信息，见图15，删除【Name】字段，最终获得图8中的结果。

图15.合并完后的数据表结构