Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 months ago
Solved

New API connection

Hi all,   Our Vendor send us an API configuration with ClientId and SecretId.   This is the first time we use PowerBI with an API connection any helps would be appreciated to familiarise with it....
Need Help
  • kushanNa's avatar
    kushanNa
    8 months ago

    okay maybe you will need to pass the token as a function then , try to created the following function and query 

     

     open blank query and copy past , name the function as GetTokenNew 

     

    () as text =>
let
    // -----------------------------
    // ECI OAuth Token Request
    // -----------------------------
    client_id     = "YOUR_CLIENT_ID",
    client_secret = "YOUR_CLIENT_SECRET",

    token_url = "https://api-user.integrations.ecimanufacturing.com/oauth2/api-user/token",

    tokenBody =
        "grant_type=client_credentials" &
        "&scope=openid" &
        "&client_id=" & client_id &
        "&client_secret=" & client_secret,

    tokenResponse =
        Json.Document(
            Web.Contents(
                token_url,
                [
                    Headers = [
                        #"Content-Type" = "application/x-www-form-urlencoded",
                        Accept = "application/json"
                    ],
                    Content = Text.ToBinary(tokenBody)
                ]
            )
        ),

    // Extract raw token
    access_token = tokenResponse[access_token],

    // Return as "Bearer xxxx"
    BearerToken = "Bearer " & access_token
in
    BearerToken

    create another query and past 

    let
    // Call token function
    BearerToken = GetTokenNew(),

    api_url = "https://api-jb2.integrations.ecimanufacturing.com/api/v1/job-materials",

    apiResponse =
        Json.Document(
            Web.Contents(
                api_url,
                [
                    Headers = [
                        Authorization = BearerToken,
                        Accept = "application/json"
                    ]
                ]
            )
        ),

    // Convert list/record into table
    AsList =
        if apiResponse is list then
            apiResponse
        else if apiResponse is record and Record.HasFields(apiResponse, "data") then
            apiResponse[data]
        else
            {},

    TableOut =
        if List.Count(AsList) > 0 then
            let
                tbl = Table.FromList(AsList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
                firstRecord = tbl{0}[Column1],
                expanded = Table.ExpandRecordColumn(tbl, "Column1", Record.FieldNames(firstRecord))
            in
                expanded
        else
            #table({}, {})
in
    TableOut

     

    but it's better if you change the get-schedule url(in the first working query) and get the job-materials value in one query first , then try above method 

  • kushanNa's avatar
    kushanNa
    8 months ago

    Hi Anonymous 

     

    Can you try this code and see if it returns a value to pages step ? 

    let
    // ---------------------------------------------------
    // 1. OAuth Token Request  (leave your real values)
    // ---------------------------------------------------
    client_id     = "YOUR_CLIENT_ID",
    client_secret = "YOUR_CLIENT_SECRET",

    token_url = "https://api-user.integrations.ecimanufacturing.com/oauth2/api-user/token",

    tokenResponse =
        Json.Document(
            Web.Contents(
                token_url,
                [
                    Headers = [
                        #"Content-Type" = "application/x-www-form-urlencoded",
                        Accept = "application/json"
                    ],
                    Content = Text.ToBinary(
                        "grant_type=client_credentials" &
                        "&scope=openid" &
                        "&client_id=" & client_id &
                        "&client_secret=" & client_secret
                    )
                ]
            )
        ),

    access_token = tokenResponse[access_token],


    // ---------------------------------------------------
    // 2. Paging Function
    // ---------------------------------------------------
    PageSize = 200,

    GetPage = (Skip as number) =>
        let
            url =
                "https://api-jb2.integrations.ecimanufacturing.com/api/v1/job-materials" &
                "?take=" & Number.ToText(PageSize) &
                "&skip=" & Number.ToText(Skip),

            response =
                Json.Document(
                    Web.Contents(
                        url,
                        [
                            Headers = [
                                Authorization = "Bearer " & access_token,
                                Accept = "application/json"
                            ]
                        ]
                    )
                ),

            // FIXED: correct field is "Data" (case-sensitive)
            items =
                if response is list then
                    response
                else if response is record and Record.HasFields(response, "Data") then
                    response[Data]
                else
                    {}
        in
            items,


    // ---------------------------------------------------
    // 3. Generate pages until empty page encountered
    // ---------------------------------------------------
    Pages =
        List.Generate(
            () => [Skip = 0, Data = GetPage(0)],
            each List.Count([Data]) > 0,
            each [Skip = [Skip] + PageSize, Data = GetPage([Skip] )],
            each [Data]
        ),

    AllItems = List.Combine(Pages),


    // ---------------------------------------------------
    // 4. Convert to Table and Expand
    // ---------------------------------------------------
    Output =
        if List.Count(AllItems) = 0 then
            #table({}, {})
        else
            let
                tbl = Table.FromList(AllItems, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
                firstRecord = tbl{0}[Column1],
                fieldNames = Record.FieldNames(firstRecord),
                expanded = Table.ExpandRecordColumn(tbl, "Column1", fieldNames, fieldNames)
            in
                expanded

in
    Output



kushanNa's avatar
kushanNa
Super User
9 months ago

Hi Anonymous 

 

Copy and past this code to a blank query and replace your client_id and client_secret and see if it works ? 

 

Edit Credentials > Anonymous > Connect

 

let
    // -----------------------------
    // 1. OAuth Token Request
    // -----------------------------
    client_id     = "YOUR_CLIENT_ID",
    client_secret = "YOUR_CLIENT_SECRET",

    token_url = "https://api-user.integrations.ecimanufacturing.com/oauth2/api-user/token",

    tokenResponse =
        Json.Document(
            Web.Contents(
                token_url,
                [
                    Headers = [
                        #"Content-Type" = "application/x-www-form-urlencoded",
                        Accept = "application/json"
                    ],
                    Content = Text.ToBinary(
                        "grant_type=client_credentials" &
                        "&scope=openid" &
                        "&client_id=" & client_id &
                        "&client_secret=" & client_secret
                    )
                ]
            )
        ),

    access_token = tokenResponse[access_token],


    // -----------------------------
    // 2. CALL /get-schedule (NO options)
    // -----------------------------
    api_url = "https://api-jb2.integrations.ecimanufacturing.com/api/v1/eci-aps/get-schedule",

    apiResponse =
        Json.Document(
            Web.Contents(
                api_url,
                [
                    Headers = [
                        Authorization = "Bearer " & access_token,
                        Accept = "application/json"
                    ]
                ]
            )
        ),


    // -----------------------------
    // 3. Convert JSON → Table
    // -----------------------------
    AsList =
        if apiResponse is list then
            apiResponse
        else if apiResponse is record and Record.HasFields(apiResponse, "data") then
            apiResponse[data]
        else
            {},

    TableOut =
        if List.Count(AsList) > 0 then
            let
                tbl = Table.FromList(AsList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
                expanded = Table.ExpandRecordColumn(tbl, "Column1", Record.FieldNames(tbl{0}[Column1]))
            in
                expanded
        else
            #table({}, {})
in
    TableOut

 

Anonymous's avatar
Anonymous
Not applicable
9 months ago

Hi kushanNa 

 

Thanks, you code works great for schedule but unfortunately we don't use schedule and it's empty so I cannot really see any data. I'm interested to check 2 things:

1. It's possible to detach connections to the import of a table: I would like to have a unique script for connection and then all the other separate scripts using that token.

2. One table I'm sure it's with data is Job Materials. I tried to amend your script but it's not working. Could you please help me to understand what's I'm doing wrong? Job Materials is for sure one table I need to achieve to pull.

 

Thanks in advance for the help!!!!

  • kushanNa's avatar
    kushanNa
    Super User
    9 months ago

    Hi Anonymous 

     

    To answer your second question first:
    When you said you tried to amend it, did you simply replace the URL with
    api_url = "https://api-jb2.integrations.ecimanufacturing.com/api/v1/job-materials"
    If so it should work , what error did you receive when you tried it?

    For your first question:
    Yes, you can pass the token parameter from one query to another, as shown below . However, it is generally better to keep the token generation within the same query unless you have a specific requirement. Even a slight delay between query refreshes can cause issues if the token expires or refreshes out of sync.

     

    GetToken query 

     

    let
    client_id     = "YOUR_CLIENT_ID",
    client_secret = "YOUR_CLIENT_SECRET",

    token_url = "https://api-user.integrations.ecimanufacturing.com/oauth2/api-user/token",

    tokenBody =
        "grant_type=client_credentials"
        & "&scope=openid"
        & "&client_id=" & client_id
        & "&client_secret=" & client_secret,

    tokenResponse =
        Json.Document(
            Web.Contents(
                token_url,
                [
                    Headers = [
                        #"Content-Type" = "application/x-www-form-urlencoded",
                        Accept = "application/json"
                    ],
                    Content = Text.ToBinary(tokenBody)
                ]
            )
        )
in
    tokenResponse

     

    JobMaterials query 

    let
    // Read token from GetToken query
    AccessToken = GetToken()[access_token],

    api_url = "https://api-jb2.integrations.ecimanufacturing.com/api/v1/job-materials",

    apiResponse =
        Json.Document(
            Web.Contents(
                api_url,
                [
                    Headers = [
                        Authorization = "Bearer " & AccessToken,
                        Accept = "application/json"
                    ]
                ]
            )
        ),

    // API usually returns a list of records
    AsList =
        if apiResponse is list then
            apiResponse
        else if apiResponse is record and Record.HasFields(apiResponse, "data") then
            apiResponse[data]
        else
            {},

    TableOut =
        if List.Count(AsList) > 0 then
            let
                tbl = Table.FromList(AsList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
                firstRecord = tbl{0}[Column1],
                expanded = Table.ExpandRecordColumn(tbl, "Column1", Record.FieldNames(firstRecord))
            in
                expanded
        else
            #table({}, {})
in
    TableOut

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 months ago

      Hi kushanNa,

       

      Token Query works like a clock. As you can see the default timeout is 3600min more than enough to pull all the data I need.

       

      JobMaterial Query is not working instead. 2 issues has been highlighted:

      1. Token value cannot be converted.

      2. Formula.Firewall issue on apiresponse say 'JobMaterials' query reference to other queries. 

      Thanks again for your help!!

       

      Avaiable to Team call in case it can help to tackle the issue faster.

      Giovanni

       

       

      • kushanNa's avatar
        kushanNa
        Super User
        8 months ago

        okay maybe you will need to pass the token as a function then , try to created the following function and query 

         

         open blank query and copy past , name the function as GetTokenNew 

         

        () as text =>
let
    // -----------------------------
    // ECI OAuth Token Request
    // -----------------------------
    client_id     = "YOUR_CLIENT_ID",
    client_secret = "YOUR_CLIENT_SECRET",

    token_url = "https://api-user.integrations.ecimanufacturing.com/oauth2/api-user/token",

    tokenBody =
        "grant_type=client_credentials" &
        "&scope=openid" &
        "&client_id=" & client_id &
        "&client_secret=" & client_secret,

    tokenResponse =
        Json.Document(
            Web.Contents(
                token_url,
                [
                    Headers = [
                        #"Content-Type" = "application/x-www-form-urlencoded",
                        Accept = "application/json"
                    ],
                    Content = Text.ToBinary(tokenBody)
                ]
            )
        ),

    // Extract raw token
    access_token = tokenResponse[access_token],

    // Return as "Bearer xxxx"
    BearerToken = "Bearer " & access_token
in
    BearerToken

        create another query and past 

        let
    // Call token function
    BearerToken = GetTokenNew(),

    api_url = "https://api-jb2.integrations.ecimanufacturing.com/api/v1/job-materials",

    apiResponse =
        Json.Document(
            Web.Contents(
                api_url,
                [
                    Headers = [
                        Authorization = BearerToken,
                        Accept = "application/json"
                    ]
                ]
            )
        ),

    // Convert list/record into table
    AsList =
        if apiResponse is list then
            apiResponse
        else if apiResponse is record and Record.HasFields(apiResponse, "data") then
            apiResponse[data]
        else
            {},

    TableOut =
        if List.Count(AsList) > 0 then
            let
                tbl = Table.FromList(AsList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
                firstRecord = tbl{0}[Column1],
                expanded = Table.ExpandRecordColumn(tbl, "Column1", Record.FieldNames(firstRecord))
            in
                expanded
        else
            #table({}, {})
in
    TableOut

         

        but it's better if you change the get-schedule url(in the first working query) and get the job-materials value in one query first , then try above method 

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 months ago

      Hi kushanNa,

       

      It's still required the pages as variable (and if I entry some values it go in error). What I need is a loop from page 1 to the end to pull all the pagination. Hope it make sense.

       

       

      Giovanni

      • kushanNa's avatar
        kushanNa
        Super User
        8 months ago

        Hi Anonymous 

         

        Can you try this code and send me a screenshot of the first step you are getting an error? 

        let
    // -----------------------------
    // 1. OAuth Token Request
    // -----------------------------
    client_id     = "YOUR_CLIENT_ID",
    client_secret = "YOUR_CLIENT_SECRET",

    token_url = "https://api-user.integrations.ecimanufacturing.com/oauth2/api-user/token",

    tokenResponse =
        Json.Document(
            Web.Contents(
                token_url,
                [
                    Headers = [
                        #"Content-Type" = "application/x-www-form-urlencoded",
                        Accept = "application/json"
                    ],
                    Content = Text.ToBinary(
                        "grant_type=client_credentials" &
                        "&scope=openid" &
                        "&client_id=" & client_id &
                        "&client_secret=" & client_secret
                    )
                ]
            )
        ),

    access_token = tokenResponse[access_token],


    // -----------------------------
    // 2. Paging Function (calls API)
    // -----------------------------
    PageSize = 200,

    GetPage = (Skip as number) =>
        let
            url =
                "https://api-jb2.integrations.ecimanufacturing.com/api/v1/job-materials" &
                "?take=" & Number.ToText(PageSize) &
                "&skip=" & Number.ToText(Skip),

            response =
                Json.Document(
                    Web.Contents(
                        url,
                        [
                            Headers = [
                                Authorization = "Bearer " & access_token,
                                Accept = "application/json"
                            ]
                        ]
                    )
                ),

            items =
                if response is list then
                    response
                else if response is record and Record.HasFields(response, "data") then
                    response[data]
                else
                    {}
        in
            items,

    // -----------------------------
    // 3. Generate list of pages until empty
    // -----------------------------
    Pages =
        List.Generate(
            () => [Skip = 0, Data = GetPage(0)],
            each List.Count([Data]) > 0,
            each [Skip = [Skip] + PageSize, Data = GetPage([Skip] + PageSize)],
            each [Data]
        ),

    AllItems = List.Combine(Pages),


    // -----------------------------
    // 4. Convert to Table
    // -----------------------------
    Output =
        if List.Count(AllItems) = 0 then
            #table({}, {})
        else
            let
                tbl = Table.FromList(AllItems, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
                expanded = Table.ExpandRecordColumn(tbl, "Column1", Record.FieldNames(tbl{0}[Column1]))
            in
                expanded

in
    Output

        The function is in there not for us to enter data manually actually it's in there to add data automatically .