Automating API link updates

[tiannie]

I've been using PowerBI to directly import a web API to get up-to-date clinical trial information for my dashboard (from Clinicaltrials.gov, example link with 2 random studies here: clinicaltrials.gov/api/v2/studies?postFilter.ids=NCT07123961%7CNCT06868654&pageSize=400).

Previously if I wanted to add a new study, I'd just concaconate it to the link with %7C (for example, to add study NCT06354998 I just do clinicaltrials.gov/api/v2/studies?postFilter.ids=NCT06354998%7CNCT07123961%7CNCT06868654&pageSize=40...). 

Now I have a growing list of studies on an excel sheet that I want to add to this API, how do I do it without manually going into the advanced editor each time to add a new NCT number to the API link?

[jaineshp · Accepted solution]

Hey @tiannie,

Based on your requirement to dynamically update the ClinicalTrials.gov API URL without manual intervention, here are the recommended approach:

Power Query List Processing

• Load your Excel NCT list into Power Query Editor
• Transform the NCT column into a list using Table.Column()
• Use Text.Combine() function to join list items with "%7C" separator
• Build the complete API URL string dynamically: "clinicaltrials.gov/api/v2/studies?postFilter.ids=" & [Combined_NCTs] & "&pageSize=400"
• This approach ensures the URL rebuilds each time data refreshes

Fixed? ✓ Mark it • Share it • Help others!

Best Regards,
Jainesh Poojara | Power BI Developer

[tiannie]

Perfect, thanks Jainesh!