Forum Discussion
Automating API link updates
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=400).
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?
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
2 Replies
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 DeveloperPerfect, thanks Jainesh!
