New API connection

[Giovanni_Brutto]

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.

Here some API documentation provided: JobBOSS² API Documentation

Thanks!!!!

[kushanNa · Accepted solution]

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]

Hi @Giovanni_Brutto 

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

[Giovanni_Brutto]

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]

Hi @Giovanni_Brutto 

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

[Giovanni_Brutto]

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]

Hi @Giovanni_Brutto 

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 .

[Giovanni_Brutto]

Hi @kushanNa ,

Apology if I disturb you but I'm in trouble with it.

Do you had my message. Any thoughts?

Kind regards

Giovanni

[kushanNa]

Hi @Giovanni_Brutto 

No worries, What do you see in the step after GetPage step?

[Giovanni_Brutto]

Hi @kushanNa 

GetPages asking for a Skip and in my previous message I reported any values I can use it will invoke a function with error.

Pages at the moment returning an emapty list.

Thanks

Giovanni

[kushanNa]

Okay, this is the problem ,I think the API is not returning any data.
You mentioned that you were able to get job-materials from a query, right?
Which query did you use to get it?

Please try refreshing that query and check whether you can still retrieve the data.
If you can, copy that query here.

[Giovanni_Brutto]

Hi @kushanNa 

True but the limitation is I can pull only 1000 rows and I need the full dataset of data.

We achieve that with 2 scripts:

1 - Token Script

2- Pull job-materials data

As the token part has not been modified here only the script of job-materials:

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"
]
]
)
),
Data = apiResponse[Data],
#"Converted to Table" = Table.FromList(Data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"jobStatus", "jobClosedDate", "orderNumber", "jobNumber", "partNumber", "description", "stepNumber", "binLocation1", "quantityPosted1", "binLocation2", "quantityPosted2", "binLocation3", "quantityPosted3", "binLocation4", "quantityPosted4", "binLocation5", "quantityPosted5", "datePosted", "stockingCost", "stockUnit", "postedBy", "resalePrice", "pricingUnit", "productCode", "GLCode", "vendorCode", "vendorInvoiceNumber", "PONumber", "PODate", "vendorType", "receiverNumber", "packingListNumber", "receiverDate", "packingListDate", "lotNumber1", "lotNumber2", "lotNumber3", "lotNumber4", "lotNumber5", "uniqueID", "binLocationCounter", "originalBinCost", "subAssemblyJobNumber", "manufacturingJobNumber", "lastModDate", "lastModUser", "POItemNumber", "mainPart", "outsideService", "postedFromStock"}, {"Column1.jobStatus", "Column1.jobClosedDate", "Column1.orderNumber", "Column1.jobNumber", "Column1.partNumber", "Column1.description", "Column1.stepNumber", "Column1.binLocation1", "Column1.quantityPosted1", "Column1.binLocation2", "Column1.quantityPosted2", "Column1.binLocation3", "Column1.quantityPosted3", "Column1.binLocation4", "Column1.quantityPosted4", "Column1.binLocation5", "Column1.quantityPosted5", "Column1.datePosted", "Column1.stockingCost", "Column1.stockUnit", "Column1.postedBy", "Column1.resalePrice", "Column1.pricingUnit", "Column1.productCode", "Column1.GLCode", "Column1.vendorCode", "Column1.vendorInvoiceNumber", "Column1.PONumber", "Column1.PODate", "Column1.vendorType", "Column1.receiverNumber", "Column1.packingListNumber", "Column1.receiverDate", "Column1.packingListDate", "Column1.lotNumber1", "Column1.lotNumber2", "Column1.lotNumber3", "Column1.lotNumber4", "Column1.lotNumber5", "Column1.uniqueID", "Column1.binLocationCounter", "Column1.originalBinCost", "Column1.subAssemblyJobNumber", "Column1.manufacturingJobNumber", "Column1.lastModDate", "Column1.lastModUser", "Column1.POItemNumber", "Column1.mainPart", "Column1.outsideService", "Column1.postedFromStock"})
in
#"Expanded Column1"

[kushanNa · Accepted solution]

Hi @Giovanni_Brutto 

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

[Giovanni_Brutto]

Hi @kushanNa 

This one works like a clock!!!

Thanks you very much for the support 5*

Giovanni

[Giovanni_Brutto]

Hi, @kushanNa 

First step as blocker is Get Page step: is asking a parameter: as I said before an anutomatic loop would be better.

Even If I enter a parameter (I tried 1) the invoked function created end in error. 

As I said before if it could help I'm avaiable to a Team session. Just let me know.

[Giovanni_Brutto]

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 · Accepted solution]

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 

[Giovanni_Brutto]

Hi @kushanNa 

I was able to make your script works. The problem was about Data Source credential which need to switch to Anonymous.

Now I pull all the tables I face another issue which I dont' expected. It seems only 1000 rows are pulled. Do you may know how to increase this limit as I would like to pull the entire table.

Thanks again for your support.

Giovanni

[kushanNa]

Nice!

To retrieve additional pages, you need to include paging parameters. This is mentioned in the document you shared.

Please try the code below and check if it works.

let
    // Token
    BearerToken = GetTokenNew(),

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

    PageSize = 200,      // API default, but you can adjust
    GetPage =
        (Skip as number) =>
            let
                url = BaseUrl & "?" &
                      "take=" & Number.ToText(PageSize) &
                      "&skip=" & Number.ToText(Skip),

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

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

    // Generate list of pages
    PageList =
        List.Generate(
            () => [Skip = 0, Data = GetPage(0)],
            each List.Count([Data]) > 0,
            each [Skip = [Skip] + PageSize, Data = GetPage([Skip] + PageSize)],
            each [Data]
        ),

    // Combine all pages into one list
    CombinedList = List.Combine(PageList),

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

[Giovanni_Brutto]

Hi @kushanNa 

I tried but it asked me the page but I expected to automatically load all the pages. Is it possible?

Giovanni

[kushanNa]

I wonder like i said earlier having two queries causing this issue , try this query ? 

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. Function: Get a single page
    // -----------------------------
    GetPage = (Skip as number) =>
        let
            url = "https://api-jb2.integrations.ecimanufacturing.com/api/v1/job-materials" &
                  "?" & Uri.BuildQueryString([take = 200, skip = Skip]),

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

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


    // -----------------------------
    // 3. Auto-loop pages until empty
    // -----------------------------
    PageSize = 200,

    PageList =
        List.Generate(
            () => [Skip = 0, Data = GetPage(0)],
            each List.Count([Data]) > 0,
            each [Skip = [Skip] + PageSize, Data = GetPage([Skip] + PageSize)],
            each [Data]
        ),

    // Flatten list of lists
    AllRows = List.Combine(PageList),


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

[Giovanni_Brutto]

Hi @kushanNa ,

Almost there I think.

Everything works until the apiResponse with the error below. Any advice?

Best regards

Giovanni