说明
这个例子应用面非常窄：只适合我们公司奇葩系统导出的问卷答题汇总结果，如下图：
问卷结果汇总
因此，要使用此模板，必须满足两个条件：
1.表头由人员信息、题目及选项组成，其中选项单独占一行。
2.每个单元格只能有一个选项。
贴出来一是作为自己学习的记录，二是也提供一种处理这类问题的思路参考。
思路
1.依旧要借用参数表格，自动获取文件路径，这样就可以直接将原始文件和统计模板放到同一个文件夹，拷贝给别人后只需要替换原始文件，然后打开模板文件刷新即可。
2.表格形式其实是一个透视表格式，所以需要逆透视表格，将表格转化为标准的记录格式（或者叫数据库格式？）。
3.题目数量和选项都是动态的，但人员信息是不会变化的，因此，用“逆透视其他列”。
4.题目和人员信息其实是两类不同信息，所以需要分表，然后用关系将两者关联起来。
5.由于可能存在某个选项没有一个人选择的情况，当制作数据透视表时，这个没人选的选项很可能被隐藏，因此要设置数据透视表，选择“显示列中的空数据项”。如下图：
显示列中的空数据项.png
代码
参数表格的代码
参数表格用法详见《PowerQuery的参数表格用法》。
参数表格中，除了文件路径这个参数外，我还新增了一个“人员信息列”的参数，这个参数的目的在于找到原始表格中人员信息与题目信息的分界线。这需要使用者观察原始数据，然后手动填写。本例中人员信息占了7列，所以填写了7。
let
源 = (ParameterName as text) =>
let
ParamSource = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content],
ParamRow = Table.SelectRows(ParamSource, each ([Parameter] =ParameterName)),
Value=
if Table.IsEmpty(ParamRow)=true
then null
else Record.Field(ParamRow{0},"Value")
in
Value
in
源
答题结果表格处理代码
let
源 = Excel.Workbook(File.Contents(fnGetParameter("原始文件")), null, true),
数据表 = 源{[Item="Sheet1",Kind="Sheet"]}[Data],
删除的顶端行 = Table.Skip(数据表,2),
逆透视的列 = Table.UnpivotOtherColumns(删除的顶端行, List.FirstN(Table.ColumnNames(删除的顶端行),fnGetParameter("人员信息列")), "属性", "值"),
重命名的列 = Table.RenameColumns(逆透视的列,List.Zip({List.FirstN(Table.ColumnNames(删除的顶端行),fnGetParameter("人员信息列")), Record.FieldValues(Record.SelectFields(源{[Item="Sheet1",Kind="Sheet"]}[Data]{0},List.FirstN(Table.ColumnNames(删除的顶端行),fnGetParameter("人员信息列"))))}))
in
重命名的列
【注意】，“删除的顶端行”这一步，我选择了删除前面的两行，这是由原始数据文件的表头决定的——因为原始数据表头有合并单元格，导入PowerQuery后合并单元格被拆散，表头变成了两列。如果现在不删除，后面会有一个额外动作需要剔除表头。
表头删除后，在“重命名的列”这一步，我又把删除的表头从“源”里找回来了。
题目表格
let
源 = Excel.Workbook(File.Contents(fnGetParameter("原始文件")), null, true),
数据表 = 源{[Item="Sheet1",Kind="Sheet"]}[Data],
保留的第一行 = Table.FirstN(数据表,2),
删除的列 = Table.RemoveColumns(保留的第一行,List.FirstN(Table.ColumnNames(数据表),fnGetParameter("人员信息列"))),
自定义1 = Table.DemoteHeaders(删除的列),
转置表 = Table.Transpose(自定义1),
向下填充 = Table.FillDown(转置表,{"Column2"}),
重命名的列 = Table.RenameColumns(向下填充,{{"Column1", "题目序号"}, {"Column2", "题目"}, {"Column3", "选项"}})
in
重命名的列
“重命名的列”这一步不是必须，只是为了便于理解。注意fnGetParameter("人员信息列")引用的是参数表格中第二行的值。
最后结果
统计结果
注意选项D，由于没有任何人选择，所以如果不设置“显示列中的空数据项”，D选项就不会出现，这样就失真了——会让人以为题目没有设置D选项。
网友评论