Forum Discussion
Rest API timeout due to large dataset
Thanks to Anonymous , I was able to get this GET working. The issue is that the dataset I need to generate is close to 412,000 rows. Does anyone know if there there is a way to: 1. Decrease the amou...
Hi tross4012 ,
Please update the applied codes in Advanced Editor as below, the part with red font is added ones...
| let Source = List.Generate( () => [offset = 0, getstudies = 1] , each [offset] <= 10000, each [offset = [offset] + 1000, getstudies = [offset] ], each [getstudies]), #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Min"}}), #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Min] <> 0)), #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Max", each [Min] + 999), #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Min", type text}, {"Max", type text}}), #"Add URL" = Table.AddColumn(#"Changed Type", "Add Url", each "https://ClinicalTrials.gov/api/query/study_fields?&fields=NCTId,LeadSponsorName,Phase,BriefTitle,OrgStudyID,OverallStatus&fmt=csv&max_rnk=" & [Max] & "&min_rnk=" & [Min], type text ), #"Get Data" = Table.AddColumn(#"Add URL", "Csv Data", each Table.PromoteHeaders( Table.Skip( Csv.Document(Web.Contents([Add Url], [Timeout=#duration(0,0,15,0)]),[Delimiter=",", Columns=7, Encoding=65001, QuoteStyle=QuoteStyle.None]),10))), #"Table Append" = Table.Combine( #"Get Data"[Csv Data] ), #"Sorted Rows" = Table.Sort(#"Table Append",{{"Rank", Order.Ascending}}), #"Changed Type1" = Table.TransformColumnTypes(#"Sorted Rows",{{"Rank", Int64.Type}}), #"Filtered Rows1" = Table.SelectRows(#"Changed Type1", each ([OverallStatus] <> "Completed" and [OverallStatus] <> "Terminated" and [OverallStatus] <> "Withheld")), #"Sorted Rows1" = Table.Sort(#"Filtered Rows1",{{"Rank", Order.Ascending}}) in #"Sorted Rows1" |
Best Regards
Unfortunately, i run into an error when placing the Timeout in the position mentioned. See pictures below: