Hilti API

[Jimmy801]

Dear members

I'm looking for help for getting a token for the Hilti-API ON!Track. On the homepage of hilti, there is the specification for postman, but there is no way I'm able to write the M-code properly. Here you can find the specifciation: https://unite.ontrack3.hilti.com/developer/guides/confedit . I have ClientID, ClientSecret, Username and Password. Can somebody help me?

Many thanks

Jimmy

[Martin_D]

Hi @Jimmy801 ,

Unfortunatelly, Power BI does not support the OAuth Client Credential authentication out of the box. 

If you only want to get data into Power BI Desktop (not refresh in the service), then this article is a good starting point how to implement this in Power Query, but it might not work with scheduled refresh: https://pbi-guy.com/2023/03/25/connect-with-power-bi-to-the-power-bi-rest-api-with-a-service-princip...

If you want a scheduled refresh in the Power BI service, then you can implement a custom connector (might be worth sharing the idea of providing one with Hilti). This might be a good starting point https://jussiroine.com/2019/02/building-a-custom-connector-for-power-bi-that-supports-oauth2-to-visu... or the Microsoft documentation.

If you want to use scheduled refresh, I would try copying the required data to a database or datalake using Azure Data Factory and loading it from there into Power BI because Azure Data Factory supports OAuth Client Credential login out of the box. But it's a bit special that the Hilti API requires you to provide ClientID and ClientSecret and Username and Password in one token request and this is not supported in Azure Data Factory. So either it works with ClientID and ClientSecret only, or you need to implement the custom connector, or any custom script (PowerQuery, Python, C#, ...) that gets your data from the Hilti API to an intermediate storage of your choice (datalake, database, SharePoint, ...).

BR

Martin

[Jimmy801 · Accepted solution]

Hello

i was now able to make Power Query work to access the Hilti App. I will post the code used here, if somebody has the same problem

let
    client_id = "Client_ID",
    client_secret = "Client_Secret",
    token_uri = "https://cloudsso.hilti.com/hc/token", 
    username = "UserName",
    password = "Password",
    scope = "HC.Request.AllScopes",
    requestPayload = [
        username = username,
        password = password,
        grant_type = "password",
        scope = scope
    ],
    requestHeaders = [
        #"Content-Type" = "application/x-www-form-urlencoded",
        #"Authorization" = "Basic " & Binary.ToText(Text.ToBinary(client_id & ":" & client_secret), BinaryEncoding.Base64)
    ],
    tokenResponse = Json.Document(
        Web.Contents(
            token_uri,
            [
                Content = Text.ToBinary(Uri.BuildQueryString(requestPayload)),
                Headers = requestHeaders,
                ManualStatusHandling = {400, 401, 402, 403, 404, 405, 406, 500}
            ]
        )
    )
in
    tokenResponse

[Martin_D]

Hi @Jimmy801 ,

Great solution! Did you try whether it works with scheduled refresh in the service, and did it work there as well? The concern was that your query returns a token, dynamically, i.e. potentially a new one with every refresh, and then the actual data query uses this token, which might be considered an insecure dynamic query by the Power BI service.

BR

Martin

[Jimmy801]

Hello

I just needed it to connect Excel to this API - no scheduled refresh. But as you wrote, it might not be allowed in service. What would be needed to fix that? How is the process to establish a better query?
BR Jimmy

[Martin_D]

For Excel the solution is totally fine. For Power BI scheduled refresh, the next step would either be to implement a custom connector or to put the API access into an external script that puts the data in e.g. a datalake for Power BI to fetch it from there.

[Martin_D]

At least the Hilti API is definitely a good test case for how universal an ETL tool is.