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]

Hi Chandan,

Use below code to extract the data from ServiceNow to power bi using rest API.:

Please note this will not support incremental refresh as ServiceNow rest API does not support query folding

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_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"

[chandan1994]

Hey Raks,

Thank you so much for the quick replay, could you please help me where to write this scrip as i am new to integration.

Steps to get this integration.

Thanks in advance. Appreciated your help.

[Raks]

You can write this using the blank query option in the Get Data. Juts copy paste this code into the query after removing any default code.

You need to replace your instance of servicenow in the host part of the code.

[Nishant9]

Hi Raks,

The above query helped to fetch the records. Can we modify fetch specific columns specifying the sysparm_fields. 

I tried to do that but it still shows all the fields in the result.

Regards,

Nishant