Power BI中Azure预留实例成本对比的最优数据处理方案问询

核心需求回顾 #

• 目标：判断Azure Reserved Instances (RI) 是否适配自身业务
• 现状：成本数据按消费型、1年预留、3年预留分多行存储，已基于productName、skuName、location建立唯一主键
• 期望输出：将ProductName、effectivePrice（消费型单价）、1Year_unitPrice、3Years_unitPrice合并到同一行

Power Query 中的最优处理方案 #

方案1：透视列（推荐，高效直接） #

这是最贴合需求的原生方案，步骤清晰且性能稳定：

• 在Power Query编辑器中，选中主键列（productName、skuName、location）
• 切换到转换选项卡 → 点击透视列
• 透视配置：
  • 值列选择effectivePrice
  • 高级选项选择不要聚合（主键已确保分组唯一，无需聚合）
  • 将行中的计费类型标识（如包含"1 Year Reservation"的字段值）映射为1Year_unitPrice，同理处理3年预留和消费型
  • 重命名透视后的列，匹配期望的字段名

方案2：保留多行的规则提取（适合需保留原始数据场景） #

如果需要保留原始多行数据同时提取对应单价，可通过添加自定义列实现：

• 按主键列对数据分组：切换到转换选项卡 → 分组依据，选择主键列作为分组键，操作选所有行，命名分组结果为GroupedRows
• 添加自定义列，用M语言提取对应类型的单价（需替换ReservationTerm为你数据中标识计费类型的实际字段名）：
  • 提取1年预留单价：

    Table.SelectRows([GroupedRows], each Text.Contains([ReservationTerm], "1 Year")){0}[effectivePrice]?
    

  • 提取3年预留单价：

    Table.SelectRows([GroupedRows], each Text.Contains([ReservationTerm], "3 Years")){0}[effectivePrice]?
    

  • 提取消费型单价：

    Table.SelectRows([GroupedRows], each [ReservationTerm] = "Pay-as-you-go"){0}[effectivePrice]?
    

  （?用于处理无对应数据时返回空值，避免报错）
• 展开自定义列后，即可在每行原始数据旁看到对应主键下的所有类型单价

性能优化建议 #

• 数据源预处理：如果使用Azure SQL Database，可先在库中通过PIVOT语句预处理数据，减少Power Query计算压力：

  SELECT 
    productName,
    skuName,
    location,
    MAX(CASE WHEN term = 'Pay-as-you-go' THEN effectivePrice END) AS effectivePrice,
    MAX(CASE WHEN term = '1 Year Reservation' THEN effectivePrice END) AS 1Year_unitPrice,
    MAX(CASE WHEN term = '3 Years Reservation' THEN effectivePrice END) AS 3Years_unitPrice
  FROM your_price_table
  GROUP BY productName, skuName, location
  

• Power Query优化：关闭自动检测数据类型（数据加载阶段），手动设置字段类型；先过滤无关数据再执行分组/透视操作

内容的提问来源于stack exchange，提问作者Francesco Mantovani