Simpro To Power BI.

[sravan.reddy]

Hi,

Trying to connect simpro data to power Bi. I have gone through your documenation. I want to know whats the best way to do this. Below is an example power query snippet that i created. Am i heading in the right direction?

let
BaseUrl = "https://xxx.simprosuite.com",
CompanyID = "0",
Endpoint = "/api/v1.0/companies/" & CompanyID & "/jobs",
PageSize = 250,
AccessToken = GetAccessToken(),

GetPage = (page as number) =>
let
Response = Web.Contents(BaseUrl, [
RelativePath = Endpoint,
Query = [
page = Text.From(page),
pageSize = Text.From(PageSize)
],
Headers = [
Authorization = "Bearer " & AccessToken,
#"Content-Type" = "application/json"
]
]),
JsonResponse = Json.Document(Response),
Items = if Record.HasFields(JsonResponse, "items") then JsonResponse[items] else {}
in
Items,

AllPages = List.Generate(
() => [Page = 1, Data = GetPage(1)],
each List.Count([Data]) > 0,
each [Page = [Page] + 1, Data = GetPage([Page] + 1)],
each [Data]
),

Combined = List.Combine(AllPages),
ResultTable = Table.FromRecords(Combined)
in
ResultTable


I am able to retrieve the access token. But the result of this is an empty table with no data. Can someone help please?

[FFsimprouser]

I would also like to know the answer to this question

[SimproAPITeam]

Hi there,

In terms of connecting Simpro Data to BI, I would advise to first validate the request in Postman to see that it is working correctly there. If you are able to perform your desired request through Postman with no issue, then this should be fine in sending the request through Power BI.

I can see that you are passing in the AccessToken as a Bearer in the Authorization header, which is the correct procedure for API authentication, just ensure that the AccessToken is a created API Key in the system setup page.

If you have any issues in the Postman validation of your desired request, please denote the error message and the request you have used and we can investigate the issue further.

Cheers,