Forum Discussion
Solved
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.go...
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
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
Perfect, thanks Jainesh!
