Help with Power Query from an online database

Hi all, I am trying to set up a dynamic Excel sheet that allows me to input certain keywords into a cell (or two) to search for matching scientific publications from Pubmed. I managed to set up ...

excel

office 365

Lorenzo
Jun 26, 2022
Hi YYHoe

I think I undestand what you ultimately expect to achieve. Hopefully the following will help

Part 1 to 2
Your first query that pulls numbers from i.e. https://pubmed.ncbi.nlm.nih.gov/?term=stress&filter=pubt.clinicaltrial&sort=pubdate returns only 2 fields: [Year] & [# Results] ==> No way to pull the corresponding list of PMIDs

At looked at the construction of the above link and the link to pull PMIDs but couldn't find something consistent enough to auto. construct the second link from the first

Part 2 & 3 - Get keywords for PMIDs
With the URLs you provided at looked at getting the PMIDs and their respective Keyworks from the nested Tables - see attached file

One thing to note regarding the URL for PMIDs. The one you provided is:
https://eutils.ncbi.nlm.nih.gov/entrez/eutils/esearch.fcgi?db=pubmed&term=stress[tiab]+clinical%20trial[pt]+human[tiab]+extract[tiab]
I also tested:
https://eutils.ncbi.nlm.nih.gov/entrez/eutils/esearch.fcgi?db=pubmed&term=stress[tiab]+clinical%20trial[tiab]+human[tiab]+extract[tiab]
So, same search criteria but [pt] after clinical%20trial in case 1, [tiab] after clinical%20trial in case 2. Results are different:

You'll find the correspoding queries in the attached. Hope this helps a bit
PMID_Keywords.xlsx38 KB

Lorenzo

Silver Contributor

Jun 24, 2022

Hi YYHoe

Part1 - Stress & Cortisol query
See attached file where I created a table in sheet PARAM with the appropriate links. Up to you to change the Labels

My [Number of Results] for Cortisol are accurate, you were summing the [Year] column
The Source step of queries Stress & Cortisol is:

= #sections[Section1][StressCortisolLinks]

This is a "trick" to prevent a Power Query Firewall error

Part2 - PMIDs

In principal a similar approach can be taken. However, no idea at all what info. you want to extract for each ID nor in which Table that information is strored. Querying i.e. https://eutils.ncbi.nlm.nih.gov/entrez/eutils/efetch.fcgi?db=pubmed&id=35708557&retmode=xml returns a Table with a bunch of nested Tables, some of them having nested Tables as well

==> If you can't make it share a query that returns what you expect for one PMID

Part3 - extract keywords by PMID

In which nested Table are the Keywords stored assuming they exist (i.e. no keyword for 35708557 & 35707866)?

StressCortisol.xlsx43 KB