API Call via MS PowerBI

Developer Forum
1

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

2

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?

3

Where do you enter your script? PowerQuery?

4
  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.

5

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

6

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“

7

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

8

Where can I set this?

9

Datenquelleneinstellungen → Berechtigungen

image
image1920×931 90.9 KB

10

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:

11

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

12

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

13

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

1 „Gefällt mir“
14

hey there, i recently started to work with power bi due to the fact that in awork custom fields are not present to be selected in reports. Thus for me it would be helpful to get a general introduction (how to) connecting power bi with awork. The goal should be that I am able to access all the data in awork. Please let me know if you have already such a guide (if yes, where can I find it) or if you can publish one.

Thanks in advance!

15

Hi @TiRe005, welcome. No, we don’t have a PowerBI guide that we can share. But PowerBI should be able to import awork data from the API without issues. The developer docs should give you all the details needed to connect the API to PowerBI, including getting an API token and fetching data. There are lots of examples. This forum is also a good place for questions. I hope this helps.

16

Hi Sebastian, I was able to create the connection wirh Power BI. Great would be if there is an easy understandable how to in which steps are needed to get access to the custom field „examplefield“. Is it possbile to publish something like that to me?

17

Hi @TiRe005, sounds good. I don’t have any experience with PowerBI unfortunately. I am happy to answer questions about the API. There is a dedicated section on working with custom fields via the API: Custom Fields — awork API | Documentation Does that help?

18

That helps generally but maybe you can show me the way where I have to insert what in Power BI to get access to the data of the custom fields.

19

As I said I unfortunately have not worked with PowerBI before. Nils posted some instructions a while ago on how this should work, maybe that helps: API Call via MS PowerBI - #4 von Nils