Power Query构造POST请求下载ECHA网站XLS数据返回结果异常

核心错误点 #

• 请求方法完全错误：你抓取到的导出接口是POST请求，但现有代码把所有表单参数直接拼接在URL末尾，实际发起的是GET请求，服务端无法从POST正文读取查询条件，只会返回默认的非目标数据集。
• 硬编码动态失效参数：代码中写死的sessionCriteriaId和formDate是服务端按访问会话临时生成的校验参数，你复制的历史值早已过期，会直接触发服务端返回异常内容。
• 参数拼接格式错误：URL查询段和你拼接的表单内容之间没有加&分隔符，会直接导致服务端解析URL参数失败。

修复后可直接运行的M代码 #

let
    // 可自行修改的查询条件
    casNumber = "67-64-1",
    substanceName = "Acetone",
    baseUrl = "https://echa.europa.eu/information-on-chemicals/registered-substances",
    
    // 接口固定URL参数
    urlQuery = [
        p_p_id = "dissregisteredsubstances_WAR_dissregsubsportlet",
        p_p_lifecycle = "2",
        p_p_state = "normal",
        p_p_mode = "view",
        p_p_resource_id = "exportResults",
        p_p_cacheability = "cacheLevelPage"
    ],

    // 导出接口固定POST表单参数
    formData = [
        disreg_substanceMasterSearchPerformed = "false",
        substanceId = "",
        disreg_substanceDetailsSearchPerformed = "false",
        businessIdentifier = "",
        #"disreg_ec-number" = "",
        #"disreg_cas-number" = casNumber,
        disreg_name = substanceName,
        disreg_registrationNumber = "",
        disreg_registrant = "",
        disreg_registrantAddress = "",
        disreg_totalTonnageBandMin = "null",
        disreg_totalTonnageBandMax = "1000000000",
        disreg_lastUpdateMinDate = "",
        disreg_lastUpdateMaxDate = "",
        disreg_registrantLastUpdateYear = "null",
        disreg_country = "",
        disreg_pbtAssessmentOutcome = "null",
        disreg_csaPerformed = "",
        disreg_registrationType = "",
        disreg_registrationStatus = "",
        disreg_submissionType = "",
        disreg_firstPublishedMinDate = "",
        disreg_firstPublishedMaxDate = "",
        disreg_tonnageBandType = "ALL",
        disreg_usesOperator = "AND",
        #"_dissregisteredsubstances_WAR_dissregsubsportlet_disreg_useCategories[]" = {"useType1","useType2","useType3","useType4","useType5"},
        disreg_highlightedname = "",
        disreg_highlightedecnumber = "",
        disreg_highlightedcasnumber = "",
        disreg_orderByCol = "relevance",
        disreg_orderByType = "asc",
        #"_dissregisteredsubstances_WAR_dissregsubsportlet_disreg_exportType" = "xls",
        #"_dissregisteredsubstances_WAR_dissregsubsportlet_disreg_exportTab" = "ALL SUBSTANCES"
    ],

    // 发起标准POST请求，移除硬编码的临时会话参数，服务端会自动为新请求分配有效会话
    Source = Excel.Workbook(
        Web.Contents(
            baseUrl,
            [
                Query = urlQuery,
                Content = Text.ToBinary(Uri.BuildQueryString(formData)),
                Headers = [
                    #"Content-Type" = "application/x-www-form-urlencoded"
                ]
            ]
        )
    )[Data]{0}
in
    Source

运行说明：首次执行时需要在Power Query隐私权限弹窗中允许对该站点的访问，后续刷新如果出现会话失效，直接重新执行查询即可，不需要手动修改参数。如果需要查询其他化学品，只需要修改代码开头的casNumber和substanceName变量值即可。

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