Forum Discussion

tross4012's avatar
tross4012
Helper I
4 years ago

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 amount of time that the load takes?

2. Increase the time-out value that Power BI current has set? Currently, the load times-out when I set the offset <= 75000. 

 

 

Need Help

4 Replies

    • tross4012's avatar
      tross4012
      Helper I
      4 years ago

      Where can I implement the Timeout clause in my call? Every place I insert it results in error. 

       

      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]),[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"

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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