Need help API with refresh token

[Bokazoit]

Hi I am new to API and tried searching but did not get anything I could understand.

I am trying to get data from a product called HR-On and it needs to retrieve a new token every hour or so:

further they add this part:

if interested link is here: https://api.hr-on.com/#tag/Authentication

How to do that in power query?

[Bokazoit · Accepted solution]

I wrote this script:

let
    #"HR-On employees" = let
    // Parametre
    client_id = "xxx",
    client_secret = "yyy",
    api_url = "url",
    token_url = "token",

    // Obtain Authorization Credentials
    ObtainAuthorizationCredentials = Json.Document(
        Web.Contents(token_url,
            [
                Content = Text.ToBinary("grant_type=client_credentials&client_id=" & client_id & "&client_secret=" & client_secret),
                Headers = [#"Content-Type" = "application/x-www-form-urlencoded"]
            ]
        )
    ),

    refresh_token = ObtainAuthorizationCredentials[refresh_token],

    access_token = ObtainAuthorizationCredentials[access_token],

    // Brug token til at hente data
    Kilde = Json.Document(
        Web.Contents(
            api_url,
            [
                Headers = [Authorization = "Bearer " & access_token]
            ]
        )
    ),
    items = Kilde[items]
in
    Kilde,
    items = #"HR-On employees"[items],
    #"Konverteret til tabel" = Table.FromList(items, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Udvidet Column1" = Table.ExpandRecordColumn(#"Konverteret til tabel", "Column1", {"id", "companyAdmin", "leader", "systemUser", "archived", "picture", "systemFields", "customFields", "departments", "roles", "createdAt", "updatedAt", "customId"}, {"id", "companyAdmin", "leader", "systemUser", "archived", "picture", "systemFields", "customFields", "departments", "roles", "createdAt", "updatedAt", "customId"})
in
    #"Udvidet Column1"

And now I get the data.

[v-nmadadi-msft]

Hi @Bokazoit  ,
Thanks for reaching out to the Microsoft fabric community forum.

The simplest way to connect Power BI to a REST API is by using the Web data connector, which is available in both Power BI Desktop and Power BI Service. This connector allows you to enter a URL that returns data from a REST API and then choose how to transform and load the data into Power BI.

If the REST API requires authentication, you will be prompted to enter your credentials. You can choose from different authentication methods, such as Basic, OAuth2, API Key, or Anonymous. For example, if you are accessing the GitHub API, you can use an OAuth2 token or an API key to authenticate.


Another way to connect Power BI to a REST API is to use the M language, which is the native query language of Power Query. The M language allows you to write custom functions and expressions to access and manipulate data from various sources, including REST APIs. The M language gives you more flexibility and control over the data, but it also requires more coding skills and knowledge of the REST API structure and parameters.

In Power BI Desktop, go to the Home tab, select "Get Data," and choose "Blank Query." Then open the Advanced Editor and enter the M code needed to connect to the REST API. For instance, you can define a function that accepts a username and returns a list of GitHub repositories for that user.

let
    Source = (username as text) as table =>
        let
            url = "https://api.github.com/users/" & username & "/repos",
            response = Web.Contents(url),
            json = Json.Document(response),
            table = Table.FromRecords(json)
        in
            table
in
    Source

For more information on how to work with REST API please check out: Read API Data with Power BI using Power Query
For more information on how to handle authentication please check out: Handling authentication for Power Query connectors - Power Query | Microsoft Learn

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thank you

[Bokazoit]

This is not how this type of API works. 

It has a client_id and a client secret that is always the same. But there is also a refresh token. I need help to insert code to handle the refresh token.