Show reports in power bi for problem list

[chandan1994]

Hello Team,

i have a requirement to show the report in power bi from servicenow problem table, please how to do this.

should i do power bi integration , if yes please provide the steps too as i am new to integration.

Thanks in advance.

[Raks]

let
  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://<host>.com/api/now/table/problem?sysparm_display_value=true&sysparm_exclude_reference_link=true&sysparm_limit=1000&sysparm_offset=1",

    [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")
in

#"Expanded result"

Use this code made some changes

[chandan1994]

Hello Raks,

Getting above error any luck on this, And thank you so much for quick replay on this.

[Raks · Accepted solution]

I guess there some issue with the URL you are using:

Go to REST APi explorer and in there below the Prepare request option select the problem table from the drop down.

Click on the send button you will get a correct url for the request .

replace the url in code with the one you will get in the REST API explorer.

Remeber to change this part only. 

https://<host>.com/api/now/table/problem

[chandan1994]

Thank you so much Raks, you are a rock star really appreciated your help.

 why it doesn't require any user name password.

and could you help me what you mean by this"Please note this will not support incremental refresh as ServiceNow rest API does not support query folding" on your 1st replay.

[Raks]

It will not ask for authorizarion if you have connected anonymously to the web api. As I can see you are using a test instance of ServiceNow.

By incremental refresh I meant that if you publish your dashboard or report in power bi services. You can fine tune your data model to refresh complete data once and than refresh only the data points that you want to refresh.

This is called incremental refresh. This is achieved only if your data source support query folding, which is the the query is running at the source and data is returned as per the query.

This does not happen in dataset which does not support query folding. So all the data is first imported in memory to your system and than all transformations are applied.

I hope I am able to answer your question