Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Andrew-KCMO
Frequent Visitor

Reports on ServiceNow incidents - few questions about import and modeling

First off, if anybody knows of any pre-built ServiceNow incident and SLA reports I can use to get me started, I'm all ears.  I see some posts from 2016 where MS seemed to have something available, but it's nowhere to be found now.  On to my questions...

 

I'm grabbing data from the ServiceNow table API.  It's expanding the json to records and in the end I get a bunch of columns but they're all named result.x and not just x.  I've tried looking at the M code and changing "result" to "incident" or just using empty quotes but it throws errors when I apply and close power query editor.  I'm not seeing settings in the GUI for any of the query steps.  


I just want a table called "incident" and the columns to just have the name that SN returns like "State", "Priority", not "result.priority", "result.state", it's confusing and manually updating all by hand seems like the wrong way to go.

 

When it comes to modeling the data the, is it not possible to have multiple relationships from 1 table to another? 

 

For example my incident table has 4 fields that are references to the user table and contain the user's primary key (but not the same user, fields are things like "caller" and "assigned_to".  I want to relate those all to the user primary key on the user table.  But don't seem to be able to do that.  I tried unpivoting those cols and that sort of works from a data model perspective but I'm not able to get anything plotted in a useful way.

 

I also tried just looking up the user name by key and adding those as new cols but looking up values from another table doens't seem to be supported behavior.  I must be approaching this issue all wrong.  My goal is to be able to plot or filter incidents by user and user's department fo the "caller" field, or the "assigned to" field on the incident record (and those fields only contain a user's PK value so not useful as-is).

 

I've attached a screen shot which illustrate both the "result.x" issue, note that all of these have it which come from the API source, and the model challenges I'm having related to resolving a name from a key. 

 

Thanks for anyone who has some tips.  I've done a fair amout of analysis work in R from previous life in some comp bio work but am just starting in PBI.

 

2023-01-17_17-22-15.jpg

 

0 REPLIES 0

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.