API Call via MS PowerBI

Hi,
I’m failing how to connect PowerBI to awork via API.
I use „Get Data“ and select „Web“.
I switch to „Advanced Mode“ and put the API Endpoint URL https://api.awork.com/api/v1/ in the first line.

And then I’m struggling where to put the Bearer Credentials in. In the drop-down is no „Authorization“ entry. Is „Accept“ the right choice? And where to but the short token and the long key?
In Postman everything works fine, but PowerBI is awful.
May someone have a great idea, how to do this?
Thanks in advance,
Axel

Hi @a.schroeder ,

i have no idea how the UI works, but i build a few months a script to get for example with powerbi all timeentries:

let
    baseUrl = "https://api.awork.com/api/v1/timeentries",
    headers = [#"Content-Type"="application/json", Authorization="Bearer {yourkToken}"],
    getPageOfTimeTrackings = (page) =>
        Json.Document(
            Web.Contents(baseUrl, [Headers=headers, Query=[page=Text.From(page), pageSize="1000"]])
        ),
    allTimeTrackings = List.Generate(
        () => 1,
        each List.Count(getPageOfTimeTrackings(_)) > 0,
        each _ + 1,
        each try getPageOfTimeTrackings(_) otherwise null
    ),
    timtrackingRecords = List.Combine(allTimeTrackings),
    timetrackingTable = Table.FromList(timtrackingRecords, Splitter.SplitByNothing(), null, null, ExtraValues.Error)

  
in
    timetrackingTable

Does that help you?

Where do you enter your script? PowerQuery?

  1. Open Power BI Desktop:

• Start Power BI Desktop on your computer.

  1. Open the Power Query Editor:

• Click on the Home tab.

• Select Transform Data to open the Power Query Editor.

  1. Enter the M Script:

• In the Power Query Editor, click on Home > Advanced Editor.

• Delete any existing code and paste your M script into the Advanced Editor.

  1. Adjust the Script (if necessary):

• Replace {yourToken} with your actual API token in the Authorization header.

  1. Run the Script:

• Click Done to close the Advanced Editor.

• Power Query will run the script and fetch data from the API.

• You should see the data loaded into the Power Query Editor.

  1. Load the Data:

• After verifying and transforming the data as needed in the Power Query Editor, click on Close & Load to load the data into Power BI.

@a.schroeder i fixed also the script, there was the url of our test environment, should work now, just tested it.

The script stopps at the ‚getPageofTimeTrackings‘ function.

The full error is: „Expression.Error: Der Header ‚Authorization‘ wird nur unterstützt, wenn eine Verbindung anonym hergestellt wird. Diese Header können mit allen Authentifizierungstypen verwendet werden: Accept, Accept-Charset, Accept-Encoding, Accept-Language, Cache-Control, Content-Type, If-Modified-Since, Prefer, Range, Referer“

Yes, you need to specify anonymous access, was the default for me, otherwise powerbi tries to overwrite the Authorization header…

Where can I set this?

Datenquelleneinstellungen → Berechtigungen

I really don’t know, what I’m doing wrong.
I can’t connect, because now it says the credentials are not ok. But they are.
The token are the „short“ digits, the key is the very long. Right?

Not my day today… :frowning:

Its important that you not use the client secret but an api key.
https://support.awork.com/en/articles/5415664-client-applications-and-api-keys and there the section API Key without User Authentication

I exactly did it like mentioned, in Postman it works, in PowerBI not.

do you want to send me the script to nils@awork.com, so i can check?

1 Like