POST API works well in Postman, not in Power BI

[katietran0467]

Hi everyone,

I followed the API Documentation here: https://docs.clockify.me/#tag/Time-Entry-Report/operation/generateDetailedReport

In Postman, it works very well.

However, it does not work in Power BI.

The script in Power BI as below.

The questions are:
1) Why could the same API request response in the different ways in Postman and Power BI?

2) How could we find the required actions for API in Power BI? For example, Postman will return the message to show what fields required but Power BI does not show any message but error.

Thanks everyone.

[katietran0467 · Accepted solution]

Hi everyone,

After many attempts, I found the reason: my Postman and my Power BI's API key is not the same: (1) with full permission and (2) with the limited permission.

I tested the API key with limited permission in Postman and the result is as below.

After testing, with the old API key with limited permission, it returns (400): Bad Request. If I added ""amountShown"": ""HIDE_AMOUNT"" in the body, it works as well.

Hope it helps.

[OwenAuger]

Hi @katietran0467 

I did some testing myself with a Clockify account, and was able to get the query working with what looks to be the same M code you posted.

I notice that the Clockify site links to a a convenient PBIT file by OscarValerock, with pre-configured Clockify API queries. I was also able to get this working too:

https://github.com/OscarValerock/Clockify-PowerBI#user-content-2-clockify-template

1. The API response should be the same if the exact same headers/body are posted.
2. Power Query doesn't provide great error messages for API calls like this - I think it is best to validate with Postman or another tool.

Here's my code with the keys obscured, that appears to match yours:

let
  url = "https://reports.api.clockify.me/v1/workspaces/XXXXXXXXXXXXXXXXXXXXXXXX/reports/detailed",
  headers = [
    #"content-type" = "application/json",
    #"X-Api-Key" = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX"
  ],
   body = "{
     ""dateRangeStart"": ""2023-01-01T00:00:00.000Z"",
     ""dateRangeEnd"": ""2023-12-31T00:00:00.000Z"",
     ""detailedFilter"": {
       ""page"": 0,
       ""pageSize"": 0       
     }
  }",

  Source = Json.Document(
    Web.Contents(
      url,
      [
        Headers = headers,
        Content=Text.ToBinary(body)
      ]
    )
  )
in
    Source

It returns a record containing two-fields: totals & timeentries, which matches what you had in Postman.

Just checking you're using Anonymous authentication for all the API data sources?

[katietran0467]

Thanks for your reply and effort to test the API.

I connected to the API with param in URL, it is okay to connect. However, with this API, I could not connect.

I checked the permissions and it is anonymous.