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
Helper IHere is the "10examples.xlsx" file:
Thanks for taking the time to help me with this!
Super UserThank you.
You seem to be applying a lot of nested (and redundant) transforms, for example
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.AddColumn(#"Changed Type", "Custom", each Table.AddColumn(#"Changed Type", "Add Url", each ...
That's probably making your life harder than necessary. Can you describe what you are ultimately trying to achieve? Fetch the records for your example IDs from the website and put them into a table? Does their API also support other formats besides XML? I see you are reading the XML as CSV and then are trying to interpret that. Impressive but not really how you want to do that. There is a native XML parser in Power Query.
Something like this
let
Source = Excel.Workbook(File.Contents("C:\Users\xxx\Downloads\10examples.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "URL data", each Xml.Document( Web.Contents("https://classic.clinicaltrials.gov/api/query/full_studies?expr=AREA[NCTId]" & [Column1] & "&fmt=xml"))[Value]{0})
in
#"Added Custom"
- tross4012
Yeah, this is one of my first times using Power BI so theres a lot of optimization to be done. Their API only supports XML and JSON formats so the aforementioned transforms are just trying to parse the data and assign them to the correct columns/rows.
I fetch the information using the NCTId and then put it into a table, which looks like the screenshot below.
Ultimately, I want to automate this so it makes the call for each NCTId and formats it. The final NCTId list will have 17,000 records so I cannot do this manually!
Thanks again.
- lbendlin
This is how the output looks like
classic.clinicaltrials.gov/api/query/full_studies?expr=AREA[NCTId]NCT02579226&fmt=xml
XML is hierarchical, so you will have to indicate the path to each of the data points you want to extract. There will be some friction as Power BI expects a table whereas XML is hierarchical. Oh wait, I mentioned that already...
- tross4012
So you're using the full study call, which is used in the table called "Sheet1". You can disregard that.
Look at the table called "Original Source". This utilizes the study fields call, which looks like this:
The "full study" call is easier to format but brings in a lot of unneeded data. The "study fields" call is harder to format but brings in the exact information I need.