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 ISample Data/Link to the file:
Here is the link to the file, which includes the sample data. The main items of reference here are the "New Source" table and the "Clean/Format" function:
Expected Results:
I click on the "Table" in the "Clean/Format" column for row 1.
This results are correct:
Then I click on the "Table" in the "Clean/Format" column for row 2:
This results are incorrect:
The only way to fix this, is if I manually change the index in my M script from:
Record = #"Added Index"{0}[Record],
to:
Record = #"Added Index"{1}[Record],
I expect each "Table" in the "Clean/Format" column to display information respective to the value in the NCTId column. See the following example.
I click on the "Table" in the "Clean/Format" column for row 7:
Without MANUALLY changing the M script to:
Record = #"Added Index"{6}[Record],
The following table should display:
Please let me know if there is any other information that you need to further understand my question/problem!
Super Usercan you please provide the "10examples.xlsx" file as well?
- tross4012
Here is the "10examples.xlsx" file:
Thanks for taking the time to help me with this!
- lbendlin
Thank 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.