Power Query Asynchronous API

[CristiDragu]

Dear all,

Looking for support with the below query .. which is supposed to bring in data from service now via rest api. 

Currently we are trying to run it in Power BI Service - as a new dataflow (after we have all the columns expanded etc). After 22 min we get an error: 

"Error: Data Source Error : Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host... RootActivityId = xxxxxxxxxxx.Param1 = Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host. Request ID: xxxxxxxxxxxxxxxx"

Service Now mentioned that they don't see any issue and that the connection is no longer open on Power BI service side....

Can anyone have a look at the below code and propose a different approach? 

Any ideas are more than appreciated! 

let

  url = "https://XXXXX.service-now.com/ ",

  RelativePath = "api/now/table/incident?sysparm_display_value=all&sysparm_fields=sys_id%2Cnumber%2Cclosed_at%2Csys_class_name%2Cactive%2Cassigned_to%2Cassignment_group%2Cu_owner_group%2Cbusiness_duration%2Ccalendar_duration%2Cservice_offering%2Cbusiness_service%2Ccmdb_ci%2Ccategory%2Csubcategory%2Ccontact_type%2Copened_at%2Copened_by%2Cresolved_at%2Cresolved_by%2Cstate%2Chold_reason%2Cdescription%2Cshort_description%2Cpriority%2Creassignment_count%2Cmade_sla%2Ccaller_id%2Csys_tags%2Cbusiness_stc%2Ccalendar_stc%2Cproblem_id%2Crfc%2Cclose_notes%2Cclose_code%2Cu_wipro_group_assigned%2Cu_wipro_responded%2Ccaller_id%2Cchild_incidents%2Csys_created_on%2Csys_updated_on&sysparm_query=sys_updated_on>=javascript&colon;gs.dateGenerate('{0}','{1}')^sys_updated_on<javascript&colon;gs.dateGenerate('{2}','{3}')",

  Tables = List.Generate(

    () => [i=-1,table=#table({},{}), tableIsEmpty = false, hourCheck = false, createdOn = if #datetime(2019,6,14,0,0,0) < RangeStart then RangeStart else #datetime(2019,6,14,0,0,0) ,createdOnOneHour=createdOn + #duration(0,1,0,0)],   // initialize loop variables

    each [hourCheck] = false,

    each [

    i=[i]+1,

    relativePath1 = Text.Replace(RelativePath, "{0}", Date.ToText(DateTime.Date(createdOn), "yyyy-MM-dd")),

    relativePath2 = Text.Replace(relativePath1, "{1}", Time.ToText(DateTime.Time(createdOn), "hh:mm:ss")),

    relativePath3 = Text.Replace(relativePath2, "{2}", Date.ToText(DateTime.Date(createdOnOneHour), "yyyy-MM-dd")),

    relativePath4 = Text.Replace(relativePath3, "{3}", Time.ToText(DateTime.Time(createdOnOneHour), "hh:mm:ss")),

    Source2  = Json.Document(Web.Contents(url, [RelativePath=relativePath4])),

    table = Table.FromRecords(Source2[result]),

    createdOn = [createdOnOneHour],

    createdOnOneHour = [createdOnOneHour] + #duration(0,0,30,0),

    hourCheck = DateTime.IsInCurrentHour(createdOn) or (Duration.Days(RangeEnd-createdOn) = 0 and Duration.Hours(RangeEnd-createdOn) = 0)

    ],

    each [table]

    ),

  #"Converted to Table" = Table.FromList(Tables, Splitter.SplitByNothing(), null, null, ExtraValues.Error)

in 

  #"Converted to Table"

[v-frfei-msft]

Hi @CristiDragu ,

You may check proxy settings and create a support ticket for assistance if necessary.

[CristiDragu]

@v-frfei-msft thanks for the tip, however the connection doesn't go through the enterprise gateway as it is set-up to be cloud-to-cloud via dataflows.

Already raised a ticket and discussed with the colleagues however issue is not resolved and wanted to see if anyone from the comunity could have an idea in relation to the code....maybe there's a better / diferent way of writing it...

Thanks again! 

C