How to reference an index from another table in a function

The elements that need to make it into the final table are in the API call:

https://classic.clinicaltrials.gov/api/query/study_fields?expr=AREA[NCTId]NCT02579226&fields=NCTId,BriefTitle,LocationFacility,LocationStatus,LocationCity,LocationState,LocationZip,LocationCountry,LocationContactName,LocationContactEmail,LocationContactPhone&fmt=json

So each of the fields listed below need to be their own columns in the final table:

• NCTId
• BriefTitle
• LocationFacility
• LocationStatus
• LocationCity
• LocationState
• LocationZip
• LocationCountry
• LocationContactName
• LocationContactEmail
• LocationContactPhone

All of those transforms are an attempt to get that original JSON format, shown below, into something easier to work with.

apart from the first two these fields all have multiple values. You want to expand these to their own rows?

let
    Source = Excel.Workbook(File.Contents("C:\Users\xxx\Downloads\10examples.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Added Custom" = Table.AddColumn(Sheet1_Sheet, "URL data", each Json.Document( Web.Contents("https://classic.clinicaltrials.gov/api/query/study_fields?expr=AREA[NCTId]" & [Column1] & " &fields=NCTId,BriefTitle,LocationFacility,LocationStatus,LocationCity,LocationState,LocationZip,LocationCountry,LocationContactName,LocationContactEmail,LocationContactPhone&fmt=json"))[StudyFieldsResponse][StudyFields]{0}),
    #"Expanded URL data" = Table.ExpandRecordColumn(#"Added Custom", "URL data", {"Rank", "NCTId", "BriefTitle", "LocationFacility", "LocationStatus", "LocationCity", "LocationState", "LocationZip", "LocationCountry", "LocationContactName", "LocationContactEMail", "LocationContactPhone"})
in
    #"Expanded URL data"

That is correct. In the first screenshot you'll see how the raw data comes in. In the second screenshot you'll see how I, after many transforms, get it formatted.

However, the issue isnt the formatting. The issue is that this formatting is not applied to each NCTId call when I invoke custom function. 

1.

2.

The custom function is the least of your worries.  The transforms require the use of List.Zip  to glue your separate result column lists into one table.  Aggravated by the fact that some of your lists like LocationContactName are empty.

It's a nice challenge for someone who is familiar with Power Query... but we need to figure this out by learning. I'll see what I can come up with.

Sounds good!!! I'll take a look at optimizing the transforms. 

As always, your help is much appreciated. 

This looks terrific! 

The only issue I ran into was with LocationContactName, LocationContactEmail, and LocationContactPhone. In the following example, LocationContactName pulls in both the contact ("Marissa Erickson") and the Principal Investigator ("Mark Daniels, MD"). 

If you run the following call for "NCT00097292", you'll see there are more contact names than there are locations due to the Principal Investigator being coupled into the LocationContactName field:

https://classic.clinicaltrials.gov/api/query/study_fields?expr=AREA[NCTId]NCT00097292&fields=NCTId,BriefTitle,LocationFacility,LocationStatus,LocationCity,LocationState,LocationZip,LocationCountry,LocationContactName,LocationContactEmail,LocationContactPhone&fmt=xml

In the screenshot below, you'll see the effect that has the data. To elaborate, "Mark Daniels, MD" is being listed with the "University of California San Francisco" when he is actually a seconday contact at "Childrens Hospital of Orange County", as shown in the first screenshot on this reply.

Would it be possible to have a row per contact so that their email and phone number info is not associated with the wrong facility like shown above?

In the screenshot below, you can see how the information gets more difficult to control as this facility has multiple contacts, emails, and phone numbers associated with it. 

Not sure what the limitations are here. I tried working with an index as well as a conditional column but have had no luck. 

You can plug "NCT00097292" and "NCT00006205" into your excel sheet to get the examples I mentioned loaded in. 

Would it be possible to have a row per contact so that their email and phone number info is not associated with the wrong facility like shown above?

I have no idea how to know what "wrong facility"  means in these cases.  One of the problems of flattening JSON or XML data into a tabular format that it often leads to data destruction (ie dropping of data that doesn't fit the target structure).  Your JSON is in a format that isn't even a proper hierarchy. Does it look any better in XML?