从数据到信息
从信息到洞察

透视与逆透视的一个隐秘问题:枚举中用于完成该操作的元素过多

作为一个不怎么用PowerQuery的人,最近在清洗数据时遇到了一个有趣的错误提示,为什么说它有趣,因为PQ(编辑查询)中大部分提示是很直白的,比如:”无法将值null转换为类型Logical”,一看就知道是列混入了多种类型的数据,无法转换为统一格式。还有一类错误提示描述的比较模糊,让人第一眼无法理解。比如下面这个:“Expression.Error: 枚举中用于完成该操作的元素过多”

要搞懂这个错误的原因,我们需要先来认识一下PQ中两个经典的数据转换操作:透视与逆透视。

了解透视与逆透视

透视和逆透视,是可以调整数据结构两种互逆操作,在很多场景下具有化腐朽为神奇的力量,不需要编写复杂操作、不需要VBA,一键就能调整表结构。

透视:将指定的某一列转为行标题

透视可以将选定的某一列提升为表格的标题,操作后表格的维度增加,所以是一种升维操作。如果你觉得这种描述过于抽象,可以把这个过程理解为在Excel中创建透视表,将要透视的列拖到列字段后的效果

透视列示例

透视的效果  图片:微软官方文档

用透视表模拟等效结果

透视列的时候需要为新增的维度选择值的聚合方式,除了透视表中使用的求和、求平均等常用方式外,PowerQuery中还提供一种不聚合的方案,以List形式返回所有行记录,后面专门介绍这种情况。需要注意的是,通常我们要求导入数据模型的表尽量保持一维表,这样便于后期的维度切片,这时候就要用到透视的逆操作。

逆透视:将选定的列标题转换为行值

逆透视可以选定一列或多列,将列标题统一转换为行值,这种操作可以让表格的维度数量减少,所以是一种降维操作。

逆透视对于刚接触数据模型的Excel用户,是非常重要一种操作,因为模型的数据结构不同于Excel二维表,二维表不符合数据库第一范式的要求,不利于数据切片,需要使用逆透视降维。

    总结:透视将列值转为行标题,是升维操作,逆透视将行标题转为列,是降维操作

Expression.Error: 枚举中用于完成该操作的元素过多

这是个在透视操作中出现的错误,想模拟这个错误,需要在透视列的高级选项中将聚合方式修改为:不要聚合。当你需要保留透视后的所有行记录时,不要聚合是最佳选择。

如果使用上面的样例数据进行透视操作,不会出现错误,这里我们修改一下数据源

把第四行的类型从D改成C,再按类型列进行透视操作,会看到透视后的结果中C列出现了这个错误。怎么理解这个“用于完成该操作的元素过多”?你可以想象一下如果不报错,C1这个单元格应该有两条记录43和46,但问题就在于通过现有的维度<年份 2018>和<类型 C>的组合没法确定唯一值。如果想避免这个问题,表格除值列(透视操作时设置的)以外的所有列的组合不能有重复值,也就是能唯一区分当前行。在简书的一篇讨论类似问题的文章中,一位用户在评论里给出了比较严谨的描述,感兴趣的读者可以参考这个链接

解决这个问题有两种思路,一是透视前保证除值列以外的其他列是不重复的,可以通过索引列来实现;另一种是对透视后的多个结果做合并处理。

合并处理用到了透视操作所使用的Table.Pivot函数的第四个可选参数,这个参数可以让你指定一个聚合函数对重复值进行操作,避免冲突。

这里使用了Text.Combine,注意所有数值都被转换成了文本格式。大海老师的公众号详细介绍过这种解法,传送门 。但是大部分情况下我们不能聚合数据,而是要需要保留所有记录,这个时候就需要建立索引。

以左边的单列表为例,如果想转换成年份/类型/值的标准结构,需要添加两个索引列,一个作为标题的索引(红框),一个作为透视之后的行索引(黄框),如果不使用第二个索引,就会出现存在重复记录报错的情况。建立的这两个索引的方式也很简单,不需要写函数,用新建列选项卡里的 索引列、取模、除和舍入 这几个功能就可以搞定。

至此,这个问题叙述完整了,如果你还有什么问题,欢迎在下面给我留言。

原创内容 转载请联系作者授权:PowerBI极客 » 透视与逆透视的一个隐秘问题:枚举中用于完成该操作的元素过多

1
说点什么

avatar
1000
 
鼓掌微笑开心憧憬爱你色并不觉得吃瓜doge二哈喵喵思考笑哭捂脸悲伤大哭抓狂汗偷笑打脸捂眼黑线问号晕拜拜闭嘴衰咒骂ok作揖
1 评论数
0 被回复的评论
1 订阅评论的人数
 
查看最近回复
查看最热评论
  订阅本文评论  
最新 最旧 得票最多
提醒
敏
游客

懂了 谢谢