Forum Discussion
Help with Power Query from an online database
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: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
I took a closer look at the file StressCortisol.xlsx. The numbers in the tables are a little off.... For instance, the actual numbers extracted manually using the same hyperlinks are as listed in C10..C13 (see below). I wonder where the numbers in the table came from. Will try to go through the query details & see if I can catch them.
Hi YYHoe
Re. The numbers in the tables are a little off....
IMHO you should look at the query that's actually executed from the web page. For example - still with filter Clinical trial - if you only provide the term stress in the Search box, result = 35644, the actual query is:
("stress"[All Fields] OR "stressed"[All Fields] OR "stresses"[All Fields] OR "stressful"[All Fields] OR "stressfulness"[All Fields] OR "stressing"[All Fields]) AND (clinicaltrial[Filter])After reading a bit the E-utilities doc. if I translate the above query as follow and pass it to PQ, result = 35617 (pretty close):
https://eutils.ncbi.nlm.nih.gov/entrez/eutils/esearch.fcgi?db=pubmed&term=%28stress+OR+stressed+OR+stresses+OR+stressful+OR+stressfulness+OR+stressing%29+AND+%28clinical%20trial[Filter]%29
Similarly, with your example (stress AND human AND plant) OR (stress AND human AND herb), result = 919, actual query is:
((("stress"[All Fields] OR "stressed"[All Fields] OR "stresses"[All Fields] OR "stressful"[All Fields] OR "stressfulness"[All Fields] OR "stressing"[All Fields]) AND ("human s"[All Fields] OR "humans"[MeSH Terms] OR "humans"[All Fields] OR "human"[All Fields]) AND ("plant s"[All Fields] OR "planted"[All Fields] OR "planting"[All Fields] OR "plantings"[All Fields] OR "plants"[MeSH Terms] OR "plants"[All Fields] OR "plant"[All Fields])) OR (("stress"[All Fields] OR "stressed"[All Fields] OR "stresses"[All Fields] OR "stressful"[All Fields] OR "stressfulness"[All Fields] OR "stressing"[All Fields]) AND ("human s"[All Fields] OR "humans"[MeSH Terms] OR "humans"[All Fields] OR "human"[All Fields]) AND "herb"[All Fields])) AND (clinicaltrial[Filter])translated as follow and passed to PQ, result = 918 (almost perfect):
https://eutils.ncbi.nlm.nih.gov/entrez/eutils/esearch.fcgi?db=pubmed&term=%28%28%28stress+OR+stressed+OR+stresses+OR+stressful+OR+stressfulness+OR+stressing%29+AND+%28human s+OR+humans[MeSH Terms]+OR+humans+OR+human%29+AND+%28plant%20s+OR+planted+OR+planting+OR+plantings+OR+plants[MeSH Terms]+OR+plants+OR+plant%29%29+OR+%28%28stress+OR+stressed+OR+stresses+OR+stressful+OR+stressfulness+OR+stressing%29+AND+%28human%20s+OR+humans[MeSH Terms]+OR+humans+OR+human%29+AND+herb%29%29+AND+%28clinical%20trial[Filter]%29
Hope this helps