How to pull data from API to PowerBI

[Alee01]

I am using EmploymentHero as data source and it has an API documentation.
https://developer.employmenthero.com/api-references/#

The Employment Hero API is a RESTful-based API that returns JSON-encoded responses and uses standard HTTP response codes, authentication, and verbs.

Can someone please enlighten me how to pull that on PoweBI Desktop? If possible can you create a code that I will put into the PowerQuery?

Let's say let's pull a sample table by using that API documentation.

I hope an expert can help on this.

Thank you so much!

[ManuelBolz]

Hello @Alee01 ,

If my post helped you, please give me a 👍kudos and mark this post with Accept as Solution.

It depends very much on what you specifically want to do with the API and how you have configured your API token. But the following is an example to access the employees table.

let
    // API
    apiUrl = "https://api.employmenthero.com/api/v1/organisations/:id/employees",

    // Headers
    headers = [
        #"Authorization" = "Bearer XXXXXXXXXXXXXXXXX"
    ],

    // Request with headers
    response = Web.Contents(apiUrl, [Headers=headers]),

    // Parse
    jsonResponse = Json.Document(response),

    // Convert to a Table
    employeesTable = Table.FromList(jsonResponse, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    employeesTable

Best regards from Germany
Manuel Bolz

🟦Follow me on LinkedIn
🟨How to Get Your Question Answered Quickly
🟩Fabric Community Conference
🟪My Solutions on Github

[Alee01]

Why do we have Access Token and Refresh Token? What is the purpose of that?
Why is it included on the documentation on the early stages of scripting?

Why is not included on your code?

[ManuelBolz]

Hello @Alee01,

every API has its special feature. Basically, an access token is required to gain access to the internal data. Comparable to a password. In principle, an access token is only valid for a limited time (for example 2 weeks). After that it can no longer be used or the API reports an error. A new access token can be generated with a refresh token.

Best regards from Germany
Manuel Bolz

🟦Follow me on LinkedIn
🟨How to Get Your Question Answered Quickly
🟩Fabric Community Conference
🟪My Solutions on Github