Need Help With API Call: Query Limit Exceeded

[JayPow28]

Hi All,

I am trying to connect to an API to get time entries into a report, however the number of time entries exceeds the allowed limit of the API call. Therefore, I need to add a filter to the API call. What is the best way to do this using either the From Web datsource connector or a Power Query in the advanced editor?

Here is my call:

curl -g -X GET -H 'Content-Type: application/json' -H 'Authorization: bearer <token>' -G --data-urlencode '{filter:{"date":"Last Week"}}' 'https://eu.celoxis.com/psa/api/v2/timeEntries'

For other entites like User or Project the authorization header works fine and i get a response, but how can I handle this part in the web connector or a power query:  -G --data-urlencode '{filter:{"date":"Last Week"}}'

Any help appreciated,

Jason

[JayPow28 · Accepted solution]

Hi,

So this is the solution that worked for me:

let
    baseUrl = "https://eu.celoxis.com/psa/api/v2/timeEntries",
    query = "?filter=%7B%22date%22%3A%22Last%203%20Months%22%7D",  // URL-encoded JSON
    fullUrl = baseUrl & query,
    response = Web.Contents(fullUrl, [
        Headers = [
            #"Content-Type" = "application/json",
            #"Authorization" = "bearer <token>"
        ]
    ]),
    json = Json.Document(response)
in
    json

Thanks,

Jason

[v-pgoloju]

Hi @JayPow28,

Thanks for the update, and I hope you had a great vacation! Just checking in were you able to try out the suggested solution? Let us know if it worked for you or if you need any further help.

Thanks & Regards,

Prasanna Kumar

[v-pgoloju]

Hi @JayPow28,

We wanted to kindly check in to see if everything is working as expected after trying the suggested solution. If there’s anything else we can assist with, please don’t hesitate to ask.

Warm regards,

Prasanna Kumar

[JayPow28]

Appologies, I was away on vacation. I will check th9is solution.

Thanks,

Jay

[v-pgoloju]

Hi @JayPow28,

Just following up to see if the solution provided was helpful in resolving your issue. Please feel free to let us know if you need any further assistance.

Best regards,

Prasanna Kumar

[v-pgoloju]

Hi @JayPow28,

Thank you for reaching out to the Microsoft Fabric Forum Community, and special thanks to @grazitti_sapna  for their prompt and helpful response.

Just following up to see if the solutions provided by community members were helpful in addressing the issue.

If one of the responses helped resolve your query, please consider marking it as the Accepted Solution. Feel free to reach out if you need any further clarification or assistance.

Best regards,
Prasanna Kumar

[grazitti_sapna]

Hi @JayPow28,

To connect to an API using Power BI’s build the full query string manually in M (Power Query language).

USe below M query:

let
url = "https://api.example.com/time_entries",
filterParam = Json.FromValue([date = "Last Week"]),
queryString = [
filter = Text.FromBinary(Uri.EscapeDataString(Json.FromValue([date = "Last Week"])))
],
source = Web.Contents(
url,
[
Headers = [
#"Authorization" = "Bearer <token>",
#"Content-Type" = "application/json"
],
Query = queryString
]
),
json = Json.Document(source)
in
json

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

[JayPow28]

Hi,

Thank you for the suggestion, I got the following error when I tried it:

Expression.Error: We cannot convert a value of type Binary to type Text.
Details:
Value=[Binary]
Type=[Type]

Regards,

Jason