excel - 带有电源查询的 POST 请求未返回所需的结果

Question

按照此处成功回复的帖子的形式:Is it possible to parameterise a url such that url title display as href - PQ
我正在尝试对同一网站的不同区域执行相同操作(提供两种不同类型的有用链接:Factsheet Url 和 Substance Information Page)创建一个功能，该功能可以从网站下载 Xlxs 数据以通过电源查询搜索化学物质。
到目前为止，但是下载的 excel 文件似乎不包含正确的数据，我不确定我做错了什么。
采取的步骤:

搜索 https://echa.europa.eu/information-on-chemicals/registered-substances物质名称:丙酮和 CAS 编号:67-64-1

搜索后单击导出按钮，然后打开开发人员工具单击 XLS 按钮以从网络中识别 POST 请求和参数，这些参数可以在 M 代码中看到:

请求网址:

https://echa.europa.eu/information-on-chemicals/registered-substances?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&_dissregisteredsubstances_WAR_dissregsubsportlet_sessionCriteriaId=dissRegSubsSessionParam101401655391028695

表格数据:

_dissregisteredsubstances_WAR_dissregsubsportlet_formDate=1655391028770&disreg_substanceMasterSearchPerformed=false&substanceId=&disreg_substanceDetailsSearchPerformed=false&businessIdentifier=&disreg_ec-number=&disreg_cas-number=67-64-1&disreg_name=Acetone&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%5B%5D=useType1&_dissregisteredsubstances_WAR_dissregsubsportlet_disreg_useCategories%5B%5D=useType2&_dissregisteredsubstances_WAR_dissregsubsportlet_disreg_useCategories%5B%5D=useType3&_dissregisteredsubstances_WAR_dissregsubsportlet_disreg_useCategories%5B%5D=useType4&_dissregisteredsubstances_WAR_dissregsubsportlet_disreg_useCategories%5B%5D=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

将其放入 M 代码中时:

let
    Source = Excel.Workbook(Web.Contents("https://echa.europa.eu/information-on-chemicals/registered-substances?"&
    
    //URL Request
    "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&_dissregisteredsubstances_WAR_dissregsubsportlet_sessionCriteriaId=dissRegSubsSessionParam101401655391028695"&

    //Form Data
    "_dissregisteredsubstances_WAR_dissregsubsportlet_formDate=1655391028770&disreg_substanceMasterSearchPerformed=false&substanceId=&disreg_substanceDetailsSearchPerformed=false&businessIdentifier=&disreg_ec-number=&disreg_cas-number=67-64-1&disreg_name=Acetone&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%5B%5D=useType1&_dissregisteredsubstances_WAR_dissregsubsportlet_disreg_useCategories%5B%5D=useType2&_dissregisteredsubstances_WAR_dissregsubsportlet_disreg_useCategories%5B%5D=useType3&_dissregisteredsubstances_WAR_dissregsubsportlet_disreg_useCategories%5B%5D=useType4&_dissregisteredsubstances_WAR_dissregsubsportlet_disreg_useCategories%5B%5D=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"

))[Data]{0}
    
    
    
in
    Source

不幸的是，这只是返回:

与打开实际 Xls 文件相比:

所以我相信这是可以做到的，但显然有些东西不起作用。

Answer 1

您的代码很好，但肯定有问题。它要么是 PQ 错误，要么是服务器以某种格式错误的响应进行响应。如果我将下面 Web.Content 中的文本复制并粘贴到浏览器窗口中，它会为我打开一个 CSV。但是，在 PQ 中，由于某种原因，只读取了前几行，并且文件被截断。我很想看看其他人以前是否遇到过这种情况。

let
    Source =   Web.Contents("https://echa.europa.eu/information-on-chemicals/registered-substances?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&_dissregisteredsubstances_WAR_dissregsubsportlet_sessionCriteriaId=dissRegSubsSessionParam101401655398538618&POSThttps://echa.europa.eu/information-on-chemicals/registered-substances?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&_dissregisteredsubstances_WAR_dissregsubsportlet_sessionCriteriaId=dissRegSubsSessionParam101401655398538618[HTTP/1.1%20200%20200%20568ms]1_dissregisteredsubstances_WAR_dissregsubsportlet_formDate=1655398538719&disreg_substanceMasterSearchPerformed=false&substanceId=&disreg_substanceDetailsSearchPerformed=false&businessIdentifier=&disreg_ec-number=&disreg_cas-number=&disreg_name=Acetone&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%5B%5D=useType1&_dissregisteredsubstances_WAR_dissregsubsportlet_disreg_useCategories%5B%5D=useType2&_dissregisteredsubstances_WAR_dissregsubsportlet_disreg_useCategories%5B%5D=useType3&_dissregisteredsubstances_WAR_dissregsubsportlet_disreg_useCategories%5B%5D=useType4&_dissregisteredsubstances_WAR_dissregsubsportlet_disreg_useCategories%5B%5D=useType5&disreg_highlightedname=&disreg_highlightedecnumber=&disreg_highlightedcasnumber=&disreg_orderByCol=relevance&disreg_orderByType=asc&_dissregisteredsubstances_WAR_dissregsubsportlet_disreg_exportType=csv&_dissregisteredsubstances_WAR_dissregsubsportlet_disreg_exportTab=ALL+SUBSTANCES"),

    #"Imported Text" = Table.FromColumns({Lines.FromBinary(Source)})
in
    #"Imported Text"