POST request with power query not returning desired result

user_13562186 0
Viewed 67

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:

  1. Search on https://echa.europa.eu/information-on-chemicals/registered-substances For Substance name: Acetone and CAS number:67-64-1
  2. 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:

enter image description here

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:

enter image description here

Which when compared to the opening the actual Xls file:

enter image description here

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

powerbi powerquery xls web-scraping excel
1 Answers

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"
user_183450370
Related