透视列
在 Power Query 中,可以创建一个表,其中包含列中每个唯一值的聚合值。 Power Query 对每个唯一值进行分组,为每个值执行聚合计算,并将列透视到新表中。
显示左侧包含空白列和行的表的关系图。 ”属性“列包含 A1、A2 和 A3 重复三次的 9 行。 ”值“列从上到下包含值 V1 到 V9。 透视列后,右侧的表包含空白列和行,属性值 A1、A2 和 A3 作为列标题,A1 列包含值 V1、V4 和 V7,A2 列包含值 V2、V5 和 V8,A3 列包含数值 V3、V6 和 V9。
想象一下下图中所示的表。
该表包含设置为文本数据类型的国家/地区列、设置为数据类型的日期列和设置为整数数据类型的值列。 “国家/地区”列的前三行包含美国,后三行包含加拿大,最后三行包含巴拿马。 “日期”列在第一行、第四行和第七行中包含一个日期,在第二行、第五行和第八行中包含第二个日期,以及在第三行、第六行和第九行包含第三个日期。
该表是按国家和日期分列的简单表。 在此示例中,你要将此表转换为日期列被透视的表,如下图所示。
表包含设置为文本数据类型的国家/地区列,以及设置为整数数据类型的第一、第二和第三个日期列。 国家/地区列第 1 行包含加拿大,第 2 行包含巴拿马,第 3 行包含美国。
备注
在透视列操作期间,Power Query 会根据表左侧第一列上找到的值按升序对表进行排序。
若要透视列
选择想要透视的列。 在此示例中,选择“日期”列。
在“任意列”组的“转换”选项卡上,选择“透视列”。
在“透视列”对话框中的“值列”列表中,选择“值”。
默认情况下,Power Query 会尝试将求和作为聚合,但可以选择“高级”选项来查看其他可用的聚合。
可用选项包括:
- 不聚合
- 计数(全部)
- 计数(非空白)
- 最低
- 最大值
- 中值
- Sum
- 平均值
在处理无法聚合的列或不需要聚合的列时,可以在不进行聚合的情况下透视列。 例如,想象如下图所示的表,其中包含“国家/地区”、“位置”和“产品”字段。
该表“国家/地区”列的前三行包含美国,后三行包含加拿大,最后三行包含巴拿马。 ”位置“列包含第一、第四和第七行的第一名,第二、第五和第八行的第二名,以及第三、第六和第九行的第三名。
假设要透视此表中的“位置”列,以便可以将其值作为新列进行透视。 对于这些新列的值,使用“产品”列中的值。 选择“位置”列,然后选择“透视列”以透视该列。
在“透视列”对话框中,选择“产品”列作为值列。 在“透视列”对话框中选择“高级”选项按钮,然后选择“不聚合”。
此操作会产生下图所示的结果。
表包含“国家/地区”、“第一名”、“第二名”和“第三名”列,其中“国家/地区”列第 1 行包含加拿大,第 2 行包含巴拿马,第 3 行包含美国。
“不聚合”选项的工作方式是,为透视操作抓取单个值,作为列和行对的交叉值。 例如,假设你有一个类似于下图中的表。
表中包含国家/地区、日期和值列。 “国家/地区”列的前三行包含美国,后三行包含加拿大,最后三行包含巴拿马。 “日期”列在所有行中都包含一个日期。 值列包含 20 和 785 之间的各种整数。
你想要通过使用”日期“列来透视该表,并且想要使用“值”列中的值。 由于此数据透视表的行中只有“国家/地区”值,列中只有“日期”,因此每个单元格值都会出错,因为“国家/地区”和“日期”的每种组合都有多行。 此“透视列”操作的结果会产生下图所示的结果。
请注意错误消息“Expression.Error:枚举中元素太多,无法完成操作”。之所以发生此错误,是因为“不聚合”操作仅需要国家/地区和日期组合的单个值。