使用CKAN通过SQL分组求和数值列时Power BI Power Query连接冲突报错

问题根源 #

报错「Conflict as result from this connection」主要来自两个问题：

• SQL字符串拼接时引号嵌套混乱，生成的SQL语法不符合CKAN API的解析要求
• SUM聚合后的字段未指定别名，CKAN返回的结果字段名不规范，导致Power Query解析出错

修正后的M代码 #

let
    // 构造符合CKAN要求的SQL语句，给SUM结果加别名，修正引号格式
    SqlQuery = "SELECT ""DscClasseConsumo"", ""CodClasseConsumo"", sum(MdaPotenciaInstaladaKW) as TotalPotenciaKW FROM ""b1bd71e7-d0ad-4214-9053-cbd58e9564a7"" GROUP BY ""DscClasseConsumo"", ""CodClasseConsumo""",
    // 规范调用CKAN API，添加异常状态处理避免连接冲突
    Fonte = Json.Document(Web.Contents("https://dadosabertos.aneel.gov.br/api/3/action/datastore_search_sql", 
        [
            Query = [sql=SqlQuery],
            ManualStatusHandling = {400, 404, 500},
            Headers = [ #"Content-Type" = "application/json" ]
        ]
    )),
    // 转换为表格并展开结果层级
    #"Convertido para Tabela" = Table.FromRecords({Fonte}),
    #"result Expandido" = Table.ExpandRecordColumn(#"Convertido para Tabela", "result", {"sql", "records", "fields"}, {"result.sql", "result.records", "result.fields"}),
    // 展开分组求和后的明细数据（按需保留）
    #"Expandido Records" = Table.ExpandListColumn(#"result Expandido", "result.records"),
    #"Expandido Campos" = Table.ExpandRecordColumn(#"Expandido Records", "result.records", {"DscClasseConsumo", "CodClasseConsumo", "TotalPotenciaKW"}, {"DscClasseConsumo", "CodClasseConsumo", "TotalPotenciaKW"})
in
    #"Expandido Campos"

关键修正说明 #

• SQL规范调整：给sum(MdaPotenciaInstaladaKW)指定别名TotalPotenciaKW，CKAN API要求聚合字段必须有明确名称，否则会导致解析异常
• 引号格式修正：用单独变量存储SQL语句，避免拼接时的引号嵌套混乱，Power Query中字符串内的双引号需用两个双引号""转义
• API调用优化：通过Query参数传递SQL（比直接拼接URL更规范），添加ManualStatusHandling处理异常状态码，避免缓存或连接策略导致的冲突
• 结果展开优化：新增步骤将records层级展开为实际的汇总表格，直接得到分组求和后的结构化数据

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