Rest API timeout due to large dataset

[tross4012]

Thanks to @Daryl-Lynch-Bzy , 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. 

[Anonymous]

Hi @tross4012 ,

You can refer the following links to do it, hope they can help you resolve the problem.

1. Reduce the load time

Limitations in "add rows", API Rest

Optimization guide for Power BI

Use Performance Analyzer to examine report element performance

WHAT ARE THE OPTIONS WHEN A POWER BI REPORT IS SLOW? – POWER BI PERFORMANCE GUIDE

2. Timeout setting

Query Timeout on update

Best Regards

[tross4012]

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]

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,Org..." & [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

[tross4012]

Unfortunately, i run into an error when placing the Timeout in the position mentioned. See pictures below: