Forum Discussion

tross4012's avatar
tross4012
Icon for Helper I rankHelper I
3 years ago
Solved

How to reference an index from another table in a function

Hey everyone,   Just started using Power BI last week and am running into a roadblock. I have a function, Clean/Format, that needs to be applied to every table in the "Record" column. I want to ref...
  • lbendlin's avatar
    lbendlin
    3 years ago

     

    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

tross4012's avatar
tross4012
Icon for Helper I rankHelper I
3 years ago

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.