使用PowerQuery整理SPSS中错录入的多选题选项

这个案例的场景是：我进行了一个问卷调研，其中题目Q2是一个多选题，一共有10选项，分别是Q2.1、Q2.2，…，Q2.10，由于录入人员在SPSS中录入时，选项被错误的录入，如下第1张图所示。录入人员依次从左至右，录入的是选项的序号值，而不是对应标题，用1表示选中，用0表示未选中，最终正确的录入结果应该是如下第2张图所示（这在SPSS中可以建立基于二分类的多重响应集，以便对多选题进行分析）。

重要提醒：如需获取源文件，请扫码每张图片右下角的二维码加入QQ群。

由于样本行数很多，重新一行一行修改很不现实。现需要使用PQ将原始多选项的数据转换成上图的结果

操作实现：

1. 首先我们选中原始数据的问卷号列，然后进行“逆透视其他列”






2. 接下来再按问卷号进行分组，分组不要聚合，得到所有行。


3. 然后我们需要修改上一步分组的代码，修改了子表列的生成方式，代码如下所示。

= Table.Group(逆透视其他列, {"问卷号"}, {{"子表", (t)=> 
    List.Generate(
        ()=>[ID=1,KEY="Q2."&Text.From(t{0}[值]),VALUE=1],
        each [ID]<=Table.RowCount(t),
        each [ID=[ID]+1,KEY="Q2."&Text.From(t{[ID]}[值]),VALUE=1],
        each _
    )
}})

其中主要使用到了List.Generate，该函数我在另一篇博文按总期数为每期生成独立行数据(金融应用)中已经详细说明过，这里就不再多做说明，只是解释一下整个修改的部分的含义。

我使用List.Generate来生成一个每个元素都是Record的列表，每个Record都有三个键值对，ID,KEY,VALUE。ID是一个数字递增序号，KEY将会根据原来表中的值重新确定其对应的题号，例如假设值为4，那么它表示Q2.4这个选项被选中了，KEY就是Q2.4这个题号，而VALUE则是表示是否选中，1是选中，0是没选中，我们这里直接等于，因为在没选中情况下，原来的数据里面是空的，而PQ进行逆透视时，已经自动过滤掉了这些空值，所以一旦在子表列里的行，其中的值列必定不会为空。

此时我们会得到如下所示的结果，子表列中，每个元素都是一个List，List中则包含的是Record，可能会有多个Record也可能只有一个Record




4. 上一步的结果图中，我们只能看到List中包含了Record，但是具体Record有些什么内容我们不知道，所以我们需要展开子表列，来查看






5. 接下来我们还需要继续将Record也进行展开，点击子表列右侧按钮，取消ID列的勾选，然后展开即可





6. 接下来我们选中问卷号列，进行透视列操作





7. 我们发现，透视完毕后，列的顺序是混乱的，因此我们需要将上一步透视列操作中，公式的第二个参数替换成我们需要顺序的列表，以下分别是修改前和修改后的代码

= Table.Pivot(展开记录, List.Distinct(展开记录[KEY]), "KEY", "VALUE")

= Table.Pivot(展开记录, {"Q2.1", "Q2.2", "Q2.3", "Q2.4", "Q2.5", "Q2.6", "Q2.7", "Q2.8", "Q2.9", "Q2.10"}, "KEY", "VALUE")

8. 最后我们需要将结果中null值全部替换为0以表示未选择(SPSS分析需要)，选中Q2.1到Q2.10列，然后依次按图中序号操作替换