cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
staj25 Occasional Visitor
Occasional Visitor

Service Now Assignment Group only displays encoded values after column expansion

I am new to Power BI and I was trying to get data from Service Now Web API and I passed the source query as JSON application. After I converted the query into table and expanded the columns, I am getting the encoded value for these columns. Below is the advanced editor query:

 

let
Source = Json.Document(Web.Contents("https://xxxxxxxxxxxservice-now.com/api/now/table/task?
sysparm_display_value=true
&sysparm_fields=ref_change_request.end_date,sys_updated_on,number,category,
sys_updated_by,opened_by,sys_created_on,state,sys_created_by,impact,active,priority,opened_at,
work_notes,short_description,description,work_notes,sys_class_name,urgency,assigned_to,comments,
approval,cmdb_ci,sys_id,assignment_group,ref_change_request.u_bso_approver
&sysparm_query=assignment_group.name=ABCDEF^
sys_class_name!=change_task
",[Headers=[#"Accept" = "application/json"]])),
result = Source[result],
#"Converted to Table" = Table.FromList(result, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"ref_change_request.end_date", "short_description", "comments", "assignment_group", "cmdb_ci", "approval", "impact", "active", "description", "sys_updated_on", "priority", "ref_change_request.u_bso_approver", "sys_class_name", "number", "opened_at", "sys_id", "sys_updated_by", "opened_by", "urgency", "sys_created_on", "state", "work_notes", "sys_created_by", "assigned_to"}, {"Column1.ref_change_request.end_date", "Column1.short_description", "Column1.comments", "Column1.assignment_group", "Column1.cmdb_ci", "Column1.approval", "Column1.impact", "Column1.active", "Column1.description", "Column1.sys_updated_on", "Column1.priority", "Column1.ref_change_request.u_bso_approver", "Column1.sys_class_name", "Column1.number", "Column1.opened_at", "Column1.sys_id", "Column1.sys_updated_by", "Column1.opened_by", "Column1.urgency", "Column1.sys_created_on", "Column1.state", "Column1.work_notes", "Column1.sys_created_by", "Column1.assigned_to"}),
#"Expanded Column1.assignment_group" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.assignment_group", {"link", "value"}, {"Column1.assignment_group.link", "Column1.assignment_group.value"})
in
#"Expanded Column1.assignment_group"

 

The Result of this is an encoded alphanumeric number like:

7442be1d138ca200d58c7e276144b05f

 

 

I dont understand what I am doing wrong or if missing something in the query settings or anything to get it right.

I also tried to copy my query in a browser and it gives me the correct XML with actual display value rather than the encoded one.