Powerbi - Dynamic query - Refresh not working - Need support

[KhyaiBhavani]

Hi, 

I am Khyati, working on a client requirement to integrate the Servicenow and Powerbi to get last one year data of the Incidents and Tasks.

As the data is huge(morethan 60K items), I have used 2 different queries in PowerBi and combined these two in the third one and then used the 3rd query in the Graph. 

Source in 1st query:

TasksClosedThisYear= = Json.Document(Web.Contents("https://domain.service-now.com/api/now/table/sc_task?sysparm_query=closed_atONThis year@javascript:gs.beginningOfThisYear()@javascript:gs.endOfThisYear()^assignment_group=...

Source in 2nd query:

TasksCloseLastYear = = Text.Combine({"https://domain.service-now.com/api/now/table/sc_task?sysparm_query=closed_atBETWEENjavascript:...('" ,
Custom4,"','00:00:00')@javascript:gs.endOfLastYear()^stateIN-55,3^assignment_group=271defb96fc22100362401dfde3ee4a0^ORassignment_group=e6914d384ca741006ec244523f089d15^numberSTARTSWITHTAS^ORDERBYDESCnumber&sysparm_limit=37000&sysparm_display_value=true&sysparm_fields=number,closed_at"})

Custom4 is the dynamic date used

Source in 3rd query: Source= Table.Combine({TasksClosedThisYear, TasksCloseLastYear})

Once I publish the powerbi report to workspace, initially there were refresh options and the autorefresh is getting failed. But later the refresh options are also not visible.

Below is the error I got

Could someone help me on this on urgency.

Thanks,

Khyati.

[antonstorozhuk]

Hey Khyati @KhyaiBhavani 

I would like to propose you to try out an alternative option connecting Power BI and Servicenow:

Power BI Connector for ServiceNow app

• https://store.servicenow.com/sn_appstore_store.do#!/store/application/87a42573879e0110fb5033773cbb35...
• https://www.alphaservesp.com/products/servicenow/power-bi/

We have a handy User and Admin Guides for your convenience, but feel free to reach out support team at [email protected] if you need any assistance.

Cheers,

Anton

[lbendlin]

Please read this document thoroughly, especially the Query part.

Web.Contents - PowerQuery M | Microsoft Docs

That may help you to mask the dynamic part.

[KhyaiBhavani]

Hi Ibendlin,

Thank you for reply. I already tried this but not getting how to get the last one year tasks data from servicenow ina  single query as the data is more(near to 1L and 1L+). If you have any option to get the data in a single query please let me know.

Thanks,

Khyati.

[lbendlin]

That would be a question for the ServiceNow API - check if it supports pagination.