带有电源查询的发布请求未返回所需结果

发布于 2025-02-07 20:01:22 字数 5581 浏览 2 评论 0原文

按照表格在此处成功回答的帖子：可以参数化URL，以使URL标题显示为HREF-PQ

我正在尝试使用同一网站的其他区域进行相同的操作（提供两种不同类型的有用链接：FACTSHEET URL和物质信息页）创建一个函数，可以通过电源查询从网站上下载XLXS数据以获取搜索化学品。

但是，到目前为止，下载的Excel文件似乎并不包含正确的数据，我不确定我在做什么错。

Steps takemn:

Search on https://echa.europa.eu/information-on - 化学/注册 - 掩体用于实质名称：丙酮和CAS号：67-64-1
单击导出按钮，然后搜索出口按钮，然后使用开发人员工具打开开机单击XLS按钮以从网络中识别post reuqest and post reuqest和可以在m代码中看到的参数：

请求URL：

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文件相比，

所以我有信心这可以做到这一点，但显然有些事情不起作用。

原文

Following on form a successfully answered post here: Is it possible to parameterise a url such that url title display as href - PQ

I am trying to do the same with a different area of the same website (provides two different types of useful links: Factsheet Url and Substance information Page) create a function that can Download the Xlxs data from the website for searched chemicals through power query.

So far however the excel file downloaded doesnt appear to contain the right data and im not sure what im doing wrong.

Steps takemn:

Search on https://echa.europa.eu/information-on-chemicals/registered-substances For Substance name: Acetone and CAS number:67-64-1
Click on export button once searched for and then with developer tools open clicked on the XLS button to identify from network the POST reuqest and parameters which can be seen in the M Code:

Request URL:

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

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

When throwing this into M Code:

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

Unfortunately this just returns:

Which when compared to the opening the actual Xls file:

So im confident this can be done but clearly something isn't working.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

地狱即天堂 2025-02-14 20:01:23

您的代码很好，但肯定有问题。它是PQ错误，或者服务器正在以某种形式的畸形响应进行响应。如果我将文本从Web.concontent.concontent中复制并粘贴到浏览器窗口中，它将为我打开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"

Your code is fine but there is definitely something wrong. It is either a PQ bug or the server is responding with a malformed response of some kind. If I copy and paste the text from Web.Content below into a browser window, it opens a CSV for me. However, in PQ, only the first few lines are read for some reason and the file is truncated. I'll be interested to see if anyone else has encountered this before.

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"

回复收藏 0 原文

~没有更多了~