let // Get current date in the correct format for the API CurrentDate = Date.From(DateTime.LocalNow()), FormattedDate = Date.ToText(CurrentDate, "yyyy-MM-dd"), // Function to fetch a single page of data FetchPage = (pageNumber as number) => let Source = Json.Document(Web.Contents("https://api.openalex.org/works", [ Query = [ filter = "institutions.ror:https://ror.org/016476m91,is_paratext:false,primary_location.source.type:journal,from_publication_date:2021-01-01,to_publication_date:" & FormattedDate, per_page = "200", page = Text.From(pageNumber) ], Headers = [ #"User-Agent" = "PowerBI Dashboard (mailto:AA_YOUR_EMAIL@xxxx)" ] ])), Results = Source[results], Metadata = Source[meta] in [Results = Results, Metadata = Metadata], // Fetch first page to get total count FirstPage = FetchPage(1), TotalCount = FirstPage[Metadata][count], TotalPages = Number.RoundUp(TotalCount / 200), // Generate a list of all page numbers PageList = List.Numbers(1, TotalPages), // Fetch all pages AllPages = List.Transform(PageList, each FetchPage(_)[Results]), // Combine all results into a single list CombinedResults = List.Combine(AllPages), // Convert the results to a table ResultsTable = Table.FromList(CombinedResults, Splitter.SplitByNothing(), null, null, ExtraValues.Error), // Expand the nested record in the single column ExpandedTable = Table.ExpandRecordColumn(ResultsTable, "Column1", {"id", "doi", "title", "publication_year", "publication_date", "type", "open_access", "primary_location", "authorships", "concepts", "sustainable_development_goals", "grants", "primary_topic"}), ExtractResearchFields = (primary_topic) => if primary_topic = null then [SubfieldName = "", FieldName = "", DomainName = ""] else [ SubfieldName = if primary_topic[subfield]? <> null then primary_topic[subfield][display_name] else "", FieldName = if primary_topic[field]? <> null then primary_topic[field][display_name] else "", DomainName = if primary_topic[domain]? <> null then primary_topic[domain][display_name] else "" ], WithResearchFields = Table.AddColumn(ExpandedTable, "ResearchFields", each ExtractResearchFields([primary_topic])), ExpandedResearchFields = Table.ExpandRecordColumn(WithResearchFields, "ResearchFields", {"SubfieldName", "FieldName", "DomainName"}), // Expand the open_access column ExpandedOpenAccess = Table.ExpandRecordColumn(ExpandedResearchFields, "open_access", {"is_oa", "oa_status", "any_repository_has_fulltext"}, {"Output_open_access", "oa_status", "Green_open_access"}), // Expand the primary_location column ExpandedPrimaryLocation = Table.ExpandRecordColumn(ExpandedOpenAccess, "primary_location", {"is_oa","source", "license"}, {"Journal_is_oa","source", "License"}), // Expand the source column ExpandedSource = Table.ExpandRecordColumn(ExpandedPrimaryLocation, "source", {"display_name", "host_organization_name", "is_oa"}, {"Journal", "PublisherName", "Publisher_is_oa"}), // Extract author information ExtractAuthorInfo = (authorships as list) => let firstAuthor = List.First(authorships), correspondingAuthor = List.FirstN(List.Select(authorships, each [is_corresponding] = true), 1), firstAuthorName = if firstAuthor = null then null else firstAuthor[author][display_name], firstAuthorOrg = if firstAuthor = null or List.IsEmpty(firstAuthor[institutions]) then null else firstAuthor[institutions]{0}[display_name], corrAuthorName = if List.IsEmpty(correspondingAuthor) then null else correspondingAuthor{0}[author][display_name], corrAuthorOrg = if List.IsEmpty(correspondingAuthor) or List.IsEmpty(correspondingAuthor{0}[institutions]) then null else correspondingAuthor{0}[institutions]{0}[display_name], allCountries = List.Distinct(List.Combine(List.Transform(authorships, each [countries]))), countriesList = Text.Combine(allCountries, ", ") in [ FirstAuthorName = firstAuthorName, FirstAuthorOrg = firstAuthorOrg, CorrespondingAuthorName = corrAuthorName, CorrespondingAuthorOrg = corrAuthorOrg, Countries = countriesList ], WithAuthorInfo = Table.AddColumn(ExpandedSource, "AuthorInfo", each ExtractAuthorInfo([authorships])), ExpandedAuthorInfo = Table.ExpandRecordColumn(WithAuthorInfo, "AuthorInfo", {"FirstAuthorName", "FirstAuthorOrg", "CorrespondingAuthorName", "CorrespondingAuthorOrg", "Countries"}), // Extract grants information ExtractGrantsInfo = (grants as list) => let GrantsCount = if grants = null then 0 else List.Count(grants), FunderNames = if grants = null then null else Text.Combine(List.Transform(grants, each [funder_display_name]), "; "), AwardIDs = if grants = null then null else Text.Combine(List.Transform(grants, each [award_id]), "; ") in [ GrantsCount = GrantsCount, FunderNames = FunderNames, AwardIDs = AwardIDs ], WithGrantsInfo = Table.AddColumn(ExpandedAuthorInfo, "GrantsInfo", each ExtractGrantsInfo([grants])), ExpandedGrantsInfo = Table.ExpandRecordColumn(WithGrantsInfo, "GrantsInfo", {"GrantsCount", "FunderNames", "AwardIDs"}), // Extract SDGs ExtractSDGs = (sdgs as list) => if sdgs = null or List.IsEmpty(sdgs) then null else Text.Combine(List.Transform(sdgs, each [display_name]), ", "), WithSDGs = Table.AddColumn(ExpandedGrantsInfo, "SDGs", each ExtractSDGs([sustainable_development_goals])), // Extract collaboration information ExtractCollaborationInfo = (authorships as list) => let AberdeenAuthors = List.Select(authorships, each List.MatchesAny(_[institutions], each [ror] = "https://ror.org/016476m91")), CollaboratingAuthors = List.Select(authorships, each not List.MatchesAny(_[institutions], each [ror] = "https://ror.org/016476m91")), CollaboratingInstitutions = List.Distinct(List.Combine(List.Transform(CollaboratingAuthors, each _[institutions]))) in [ AberdeenAuthors = Text.Combine(List.Transform(AberdeenAuthors, each _[author][display_name]), "; "), CollaboratingAuthors = Text.Combine(List.Transform(CollaboratingAuthors, each _[author][display_name]), "; "), CollaboratingInstitutions = Text.Combine(List.Transform(CollaboratingInstitutions, each _[display_name]), "; ") ], WithCollaborationInfo = Table.AddColumn(WithSDGs, "CollaborationInfo", each ExtractCollaborationInfo([authorships])), ExpandedCollaborationInfo = Table.ExpandRecordColumn(WithCollaborationInfo, "CollaborationInfo", {"AberdeenAuthors", "CollaboratingAuthors", "CollaboratingInstitutions"}), // Remove unnecessary columns ColumnsToRemove = {"authorships", "concepts", "sustainable_development_goals", "grants", "primary_topic"}, FinalTable = Table.RemoveColumns(ExpandedCollaborationInfo, ColumnsToRemove), // Apply various transformations and replacements #"Changed Type" = Table.TransformColumnTypes(FinalTable,{{"publication_date", type date}}), #"Replaced Value" = Table.ReplaceValue(#"Changed Type","Springer Science+Business Media","Springer Nature",Replacer.ReplaceText,{"PublisherName"}), #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Springer Nature (Netherlands)","Springer Nature",Replacer.ReplaceText,{"PublisherName"}), #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","Elsevier BV","Elsevier",Replacer.ReplaceText,{"PublisherName"}), #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","Cell Press","Elsevier",Replacer.ReplaceText,{"PublisherName"}), #"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","WileyOpen","Wiley",Replacer.ReplaceText,{"PublisherName"}), #"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4","Springer International Publishing","Springer Nature",Replacer.ReplaceText,{"PublisherName"}), #"Replaced Value6" = Table.ReplaceValue(#"Replaced Value5","Institute of Electrical and Electronics Engineers","IEEE Group",Replacer.ReplaceText,{"PublisherName"}), #"Replaced Value7" = Table.ReplaceValue(#"Replaced Value6","IEEE Computer Society","IEEE Group",Replacer.ReplaceText,{"PublisherName"}), #"Replaced Value8" = Table.ReplaceValue(#"Replaced Value7","IEEE Communications Society","IEEE Group",Replacer.ReplaceText,{"PublisherName"}), #"Replaced Value9" = Table.ReplaceValue(#"Replaced Value8","IEEE Sensors Council","IEEE Group",Replacer.ReplaceText,{"PublisherName"}), #"Replaced Value10" = Table.ReplaceValue(#"Replaced Value9","IEEE Power & Energy Society","IEEE Group",Replacer.ReplaceText,{"PublisherName"}), #"Replaced Value11" = Table.ReplaceValue(#"Replaced Value10","diamond","Diamond",Replacer.ReplaceText,{"oa_status"}), #"Replaced Value12" = Table.ReplaceValue(#"Replaced Value11","green","Green",Replacer.ReplaceText,{"oa_status"}), #"Replaced Value13" = Table.ReplaceValue(#"Replaced Value12","gold","Gold",Replacer.ReplaceText,{"oa_status"}), #"Replaced Value14" = Table.ReplaceValue(#"Replaced Value13","bronze","Bronze",Replacer.ReplaceText,{"oa_status"}), #"Replaced Value15" = Table.ReplaceValue(#"Replaced Value14","closed","Closed",Replacer.ReplaceText,{"oa_status"}), #"Replaced Value16" = Table.ReplaceValue(#"Replaced Value15","hybrid","Hybrid",Replacer.ReplaceText,{"oa_status"}), #"Replaced Value17" = Table.ReplaceValue(#"Replaced Value16","article","Article",Replacer.ReplaceText,{"type"}), #"Replaced Value18" = Table.ReplaceValue(#"Replaced Value17","review","Review",Replacer.ReplaceText,{"type"}), #"Replaced Value19" = Table.ReplaceValue(#"Replaced Value18","editorial","Editorial",Replacer.ReplaceText,{"type"}), #"Replaced Value20" = Table.ReplaceValue(#"Replaced Value19","letter","Letter",Replacer.ReplaceText,{"type"}), #"Replaced Value21" = Table.ReplaceValue(#"Replaced Value20","preprint","Preprint",Replacer.ReplaceText,{"type"}), #"Replaced Value22" = Table.ReplaceValue(#"Replaced Value21","book","Book",Replacer.ReplaceText,{"type"}), #"Replaced Value23" = Table.ReplaceValue(#"Replaced Value22","report","Report",Replacer.ReplaceText,{"type"}), #"Replaced Value24" = Table.ReplaceValue(#"Replaced Value23","erratum","Erratum",Replacer.ReplaceText,{"type"}), #"Added Custom" = Table.AddColumn(#"Replaced Value24", "GroupedCategory", each let categories = [ #"Medical and Health Sciences" = {"Neuroscience", "Health Professions", "Immunology and Microbiology", "Pharmacology, Toxicology and Pharmaceutics", "Dentistry", "Nursing", "Medicine", "Biochemistry, Genetics and Molecular Biology"}, #"Life and Earth Sciences" = {"Agricultural and Biological Sciences", "Veterinary", "Environmental Science", "Earth and Planetary Sciences"}, #"Physical and Mathematical Science" = {"Physics and Astronomy", "Mathematics", "Chemistry"}, #"Social Sciences and Humanities" = {"Social Sciences", "Arts and Humanities", "Psychology", "Decision Sciences"}, #"Computing Sciences" = {"Computer Science"}, #"Engineering and Technology" = {"Engineering", "Materials Science", "Energy", "Chemical Engineering"}, #"Business and Economics" = {"Economics, Econometrics and Finance", "Business, Management and Accounting"} ], FindCategory = (field) => let matchingCategory = List.FirstN(List.Select(Record.FieldNames(categories), each List.Contains(Record.Field(categories, _), field)), 1) in if List.IsEmpty(matchingCategory) then "Other" else matchingCategory{0} in FindCategory([FieldName])), #"Filtered Rows" = Table.SelectRows(#"Added Custom", each true), #"Added Custom1" = Table.AddColumn(#"Filtered Rows", "groupedFunders", each let GroupFunder = (funder as text) => let ukri = {"Medical Research Council", "Natural Environment Research Council", "Biotechnology and Biological Sciences Research Council", "Engineering and Physical Sciences Research Council", "UK Research and Innovation", "Economic and Social Research Council", "Medical Research Council Centre for Medical Mycology", "Innovate UK", "Science and Technology Facilities Council"}, wellcome = {"Wellcome Trust", "Wellcome"}, ec = {"European Commission", "H2020 European Research Council", "H2020 Marie Skłodowska-Curie Actions", "European Research Council", "HORIZON EUROPE Framework Programme", "European Regional Development Fund", "HORIZON EUROPE European Research Council", "HORIZON EUROPE European Innovation Ecosystems", "HORIZON EUROPE Marie Sklodowska-Curie Actions", "European Maritime and Fisheries Fund", "H2020 Environment", "H2020 Future and Emerging Technologies", "Marie Curie", "Horizon 2020 Framework Programme","Horizon 2020"} in if List.Contains(ukri, Text.Trim(funder)) then "UKRI" else if List.Contains(wellcome, Text.Trim(funder)) then "Wellcome Trust" else if List.Contains(ec, Text.Trim(funder)) then "European Commission" else funder, GroupedFunders = Text.Combine( List.Transform( Text.Split([FunderNames], ";"), each GroupFunder(Text.Trim(_)) ), "; " ) in GroupedFunders) in #"Added Custom1"