Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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"
Hi @CristiDragu ,
You may check proxy settings and create a support ticket for assistance if necessary.
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.