Forum Discussion
Helper IHow to reference an index from another table in a function
let expand = (tbl) => Table.SplitColumn(Table.TransformColumns(Table.FromList(tbl, Splitter.SplitByNothing(), null, null, ExtraValues.Error), {"Column1", each Text.Combine(List.ReplaceValue(_,null,"",Replacer.ReplaceValue), "|"), type text}), "Column1", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv),{"LocationFacilty","LocationStatus","LocationCity","LocationState","LocationZip","LocationCountry","LocationContactName","LocationContactEmail","LocationContactPhone"}), 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"}), #"Added Custom1" = Table.AddColumn(#"Expanded URL data", "Custom", each try expand(List.Zip({[LocationFacility],[LocationStatus],[LocationCity],[LocationState],[LocationZip],[LocationCountry],[LocationContactName],[LocationContactEMail],[LocationContactPhone]})) otherwise #table({"LocationFacilty", "LocationStatus", "LocationCity", "LocationState", "LocationZip", "LocationCountry", "LocationContactName", "LocationContactEmail", "LocationContactPhone"},{{"", "", "", "", "", "", "", "", ""}})), #"Replaced Value" = Table.ReplaceValue(#"Added Custom1",each [BriefTitle],each List.First([BriefTitle]),Replacer.ReplaceValue,{"BriefTitle"}), #"Removed Other Columns" = Table.SelectColumns(#"Replaced Value",{"Column1", "BriefTitle", "Custom"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"LocationFacilty", "LocationStatus", "LocationCity", "LocationState", "LocationZip", "LocationCountry", "LocationContactName", "LocationContactEmail", "LocationContactPhone"}) in #"Expanded Custom"This works for the test IDs.
Note 1: because of the empty lists I had to remove nulls with empty strings
Note 2: The second to last ID has no extra data at all, so I had to introduce a dummy stand-in table
Super User
let
expand = (tbl) => Table.SplitColumn(Table.TransformColumns(Table.FromList(tbl, Splitter.SplitByNothing(), null, null, ExtraValues.Error), {"Column1", each Text.Combine(List.ReplaceValue(_,null,"",Replacer.ReplaceValue), "|"), type text}), "Column1", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv),{"LocationFacilty","LocationStatus","LocationCity","LocationState","LocationZip","LocationCountry","LocationContactName","LocationContactEmail","LocationContactPhone"}),
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"}),
#"Added Custom1" = Table.AddColumn(#"Expanded URL data", "Custom", each try expand(List.Zip({[LocationFacility],[LocationStatus],[LocationCity],[LocationState],[LocationZip],[LocationCountry],[LocationContactName],[LocationContactEMail],[LocationContactPhone]})) otherwise #table({"LocationFacilty", "LocationStatus", "LocationCity", "LocationState", "LocationZip", "LocationCountry", "LocationContactName", "LocationContactEmail", "LocationContactPhone"},{{"", "", "", "", "", "", "", "", ""}})),
#"Replaced Value" = Table.ReplaceValue(#"Added Custom1",each [BriefTitle],each List.First([BriefTitle]),Replacer.ReplaceValue,{"BriefTitle"}),
#"Removed Other Columns" = Table.SelectColumns(#"Replaced Value",{"Column1", "BriefTitle", "Custom"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"LocationFacilty", "LocationStatus", "LocationCity", "LocationState", "LocationZip", "LocationCountry", "LocationContactName", "LocationContactEmail", "LocationContactPhone"})
in
#"Expanded Custom"
This works for the test IDs.
Note 1: because of the empty lists I had to remove nulls with empty strings
Note 2: The second to last ID has no extra data at all, so I had to introduce a dummy stand-in table
Helper IThis 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:
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.
- lbendlin
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?