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:gs.dateGenerate('{0}','{1}')^sys_updated_on<javascript: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"
