I am attempting to collect href data in power query for excel for any results found on

As you can see the BP is returned just saying Open Brief Profile instead of the Href for each chemical.

Desired result for acetone in BP column:

I know this can be done using table from examples using Power BI but since I manipulate the data in excel it's more useful to pull it straight from here.

I have explored this previously with no success however https://community.powerbi.com/t5/Desktop/web-connector-and-getting-HREF-value/m-p/422068 gives me hope that it could be done? I have tried this though and run into issues.

If anyone could advise whether this could be done it would be appreciated. the final result is that column BP (not bothered about OBL) containsa href for each result in the table.

CodePudding user response：

Try this:

let
    Source = Excel.Workbook(Web.Contents("https://echa.europa.eu/search-for-chemicals?p_p_id=disssimplesearch_WAR_disssearchportlet&p_p_lifecycle=2&p_p_state=normal&p_p_mode=view&p_p_resource_id=exportResults&p_p_cacheability=cacheLevelPage&_disssimplesearch_WAR_disssearchportlet_sessionCriteriaId=dissSimpleSearchSessionParam101401654440118533&_disssimplesearch_WAR_disssearchportlet_formDate=1654440118558&_disssimplesearch_WAR_disssearchportlet_sskeywordKey=Acetone&_disssimplesearch_WAR_disssearchportlet_orderByCol=relevance&_disssimplesearch_WAR_disssearchportlet_orderByType=asc&_disssimplesearch_WAR_disssearchportlet_exportType=xls"))[Data]{0}
    
    
    
in
    Source