Hi All,
I have a few dataflows where the data is coming from a Service Now cloud service using REST API. Everything was working fine till yesterday, but since today morning I am facing issues related to JSON file.
Error: We found an invalid character between object key and value in JSON input...
Below is my query which I am using to extract the data from REST API
Pagination = List.Skip(List.Generate( () => [WebCall=[result = {0}], Page = 0, Counter=0], // Start Value
each List.Count([WebCall][result])>0 or [Counter] =0, // Condition under which the next execution will happen
each [ WebCall = Json.Document(Web.Contents("https://servicecentral.xxx.com/api/now/table/incident?sysparm_display_value=true&sysparm_exclude_ref...",
[Query=[sysparm_offset =Text.From([Counter])]])),
Page = [Page]+1,
Counter = [Counter]+1000// internal counter
]
) ,1),
#"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"WebCall", "Page", "Counter"}, {"WebCall", "Page", "Counter"}),
#"Expanded WebCall" = Table.ExpandRecordColumn(#"Expanded Column1", "WebCall", {"result"}, {"result"}),
#"Expanded result" = Table.ExpandListColumn(#"Expanded WebCall", "result"),
#"Expanded result 1" = Table.ExpandRecordColumn(#"Expanded result", "result", {"promoted_by", "parent", "caused_by", "watch_list", "u_mim_coordinator", "upon_reject", "sys_updated_on", "approval_history", "skills", "number", "proposed_by", "lessons_learned", "state", "sys_created_by", "knowledge", "order", "cmdb_ci", "contract", "impact", "u_desk_number", "active", "work_notes_list", "priority", "sys_domain_path", "business_duration", "group_list", "u_region_impacted", "u_mim_team_contacted_at", "approval_set", "major_incident_state", "short_description", "correlation_display", "work_start", "u_site_impacted", "additional_assignee_list", "u_geography_impacted", "notify", "service_offering", "sys_class_name", "closed_by", "follow_up", "parent_incident", "reopened_by", "u_set_contact_type", "reassignment_count", "assigned_to", "sla_due", "comments_and_work_notes", "u_service_owner_causing_service", "u_bridge_attendees", "u_category", "escalation", "u_incident_caused_by_change", "upon_approval", "correlation_id", "timeline", "made_sla", "promoted_on", "child_incidents", "hold_reason", "resolved_by", "sys_updated_by", "opened_by", "user_input", "sys_created_on", "sys_domain", "proposed_on", "actions_taken", "u_causing_service", "calendar_stc", "closed_at", "business_service", "business_impact", "rfc", "time_worked", "expected_start", "u_cancel_reason", "opened_at", "work_end", "caller_id", "reopened_time", "resolved_at", "subcategory", "work_notes", "close_code", "assignment_group", "business_stc", "cause", "description", "calendar_duration", "close_notes", "sys_id", "contact_type", "incident_state", "urgency", "problem_id", "company", "u_type", "activity_due", "severity", "overview", "comments", "u_multiple_resolving_teams_involved", "approval", "due_date", "sys_mod_count", "u_bridge_details", "reopen_count", "sys_tags", "location"}, {"promoted_by", "parent", "caused_by", "watch_list", "u_mim_coordinator", "upon_reject", "sys_updated_on", "approval_history", "skills", "number", "proposed_by", "lessons_learned", "state", "sys_created_by", "knowledge", "order", "cmdb_ci", "contract", "impact", "u_desk_number", "active", "work_notes_list", "priority", "sys_domain_path", "business_duration", "group_list", "u_region_impacted", "u_mim_team_contacted_at", "approval_set", "major_incident_state", "short_description", "correlation_display", "work_start", "u_site_impacted", "additional_assignee_list", "u_geography_impacted", "notify", "service_offering", "sys_class_name", "closed_by", "follow_up", "parent_incident", "reopened_by", "u_set_contact_type", "reassignment_count", "assigned_to", "sla_due", "comments_and_work_notes", "u_service_owner_causing_service", "u_bridge_attendees", "u_category", "escalation", "u_incident_caused_by_change", "upon_approval", "correlation_id", "timeline", "made_sla", "promoted_on", "child_incidents", "hold_reason", "resolved_by", "sys_updated_by", "opened_by", "user_input", "sys_created_on", "sys_domain", "proposed_on", "actions_taken", "u_causing_service", "calendar_stc", "closed_at", "business_service", "business_impact", "rfc", "time_worked", "expected_start", "u_cancel_reason", "opened_at", "work_end", "caller_id", "reopened_time", "resolved_at", "subcategory", "work_notes", "close_code", "assignment_group", "business_stc", "cause", "description", "calendar_duration", "close_notes", "sys_id", "contact_type", "incident_state", "urgency", "problem_id", "company", "u_type", "activity_due", "severity", "overview", "comments", "u_multiple_resolving_teams_involved", "approval", "due_date", "sys_mod_count", "u_bridge_details", "reopen_count", "sys_tags", "location"}),
#"Changed column type" = Table.TransformColumnTypes(#"Expanded result 1", {{"sys_updated_on", type datetime}, {"sys_created_on", type datetime}, {"proposed_on", type datetime}, {"closed_at", type datetime}, {"resolved_at", type datetime}}),
#"Removed columns" = Table.RemoveColumns(#"Changed column type", {"Page", "Counter"}),
#"Removed columns 1" = Table.RemoveColumns(#"Removed columns", {"promoted_by", "parent", "caused_by", "watch_list", "u_mim_coordinator", "upon_reject", "approval_history", "skills", "proposed_by", "lessons_learned", "sys_created_by", "knowledge", "order", "cmdb_ci", "contract", "impact", "u_desk_number", "active", "work_notes_list", "sys_domain_path", "group_list", "u_mim_team_contacted_at", "approval_set", "short_description", "correlation_display", "work_start", "additional_assignee_list", "notify", "closed_by", "follow_up", "parent_incident", "reopened_by", "u_set_contact_type", "sla_due", "comments_and_work_notes", "u_service_owner_causing_service", "u_bridge_attendees", "escalation", "u_incident_caused_by_change", "upon_approval", "correlation_id", "timeline", "made_sla", "promoted_on", "child_incidents", "hold_reason", "resolved_by", "sys_updated_by", "opened_by", "user_input", "sys_domain", "proposed_on", "actions_taken", "u_causing_service", "service_offering", "calendar_stc", "business_impact", "rfc", "time_worked", "expected_start", "opened_at", "work_end", "reopened_time", "subcategory", "work_notes", "close_code", "business_stc", "cause", "description", "close_notes", "sys_id", "urgency", "problem_id", "company", "u_type", "activity_due", "severity", "overview", "comments", "u_multiple_resolving_teams_involved", "approval", "due_date", "sys_mod_count", "u_bridge_details", "sys_tags"}),
#"Transform columns" = Table.TransformColumnTypes(#"Removed columns 1", {{"number", type text}, {"state", type text}, {"priority", type text}, {"business_duration", type text}, {"u_region_impacted", type text}, {"major_incident_state", type text}, {"u_site_impacted", type text}, {"u_geography_impacted", type text}, {"sys_class_name", type text}, {"reassignment_count", type text}, {"assigned_to", type text}, {"u_category", type text}, {"business_service", type text}, {"u_cancel_reason", type text}, {"caller_id", type text}, {"assignment_group", type text}, {"calendar_duration", type text}, {"contact_type", type text}, {"incident_state", type text}, {"reopen_count", type text}, {"location", type text}}),
#"Replace errors" = Table.ReplaceErrorValues(#"Transform columns", {{"number", null}, {"state", null}, {"priority", null}, {"business_duration", null}, {"u_region_impacted", null}, {"major_incident_state", null}, {"u_site_impacted", null}, {"u_geography_impacted", null}, {"sys_class_name", null}, {"reassignment_count", null}, {"assigned_to", null}, {"u_category", null}, {"business_service", null}, {"u_cancel_reason", null}, {"caller_id", null}, {"assignment_group", null}, {"calendar_duration", null}, {"contact_type", null}, {"incident_state", null}, {"reopen_count", null}, {"location", null}})
in
#"Replace errors"
I tried recreating the same query in power bi desktop also but facing the same issues. Not sure how to resolve it.
