Connect Zammad API directly on PowerBI

Technical assistance
1

Hi everyone,
I’m very newbi with API and PowerBi so sorry if it’s a stupid question…
I want to connect my Zammad with my PowerBi, of course to manage my own reports. But I don’t understand how use my Zammad API and token in PowerBI.
Did I need to use the web import ? The JSON import ? Dit it possible ?
I try to use web import data but I don’t understand wich link enter… The API return a link with curl configuration so I can’t use…
Thank you for your advise.

1 Like
2

I don’t think the PowerBI web or JSON connection types will work, as they don’t support the required API key authentication header. However if you select ‘Blank Query’ from the Get Data menu, then right-click Query1 and select Advanced Editor, you can create a custom query to retrieve data from the Zammad API.

Here’s a simplified version of the query I’m using to pull all tickets into PowerBI (replace the token and hostname for your installation):

let
    token = "123***token***789",
    getPage = (page as number) => let
            w1 = Web.Contents("https://***hostname***/api/v1/tickets",[
                    Query = [expand="true",per_page="100",page=Number.ToText(page)],
                    Headers = [Authorization="Token token=" & token]
                ]
            ),
            p1 = Json.Document(w1),
            p2 = Value.ReplaceMetadata(p1,[NextPage = page+1])
        in p2,
    lst1 = List.Generate(
        ()=>getPage(1),
        (x) => not(List.IsEmpty(x)),
        (x) => getPage(Value.Metadata(x)[NextPage])
    ),
    lst2 = List.Combine(lst1),
    tbl1 = Table.FromList(lst2, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    tblExpand = Table.ExpandRecordColumn(tbl1, "Column1", {"id", "number", "title", "close_at", "created_at", "updated_at", "group", "state", "owner", "customer"}, {"id", "number", "title", "close_at", "created_at", "updated_at", "group", "state", "owner", "customer"}),
    tblFilterMerged = Table.SelectRows(tblExpand, each ([state] <> "merged")),
    tblTypes = Table.TransformColumnTypes(tblFilterMerged,{{"id", Int64.Type}, {"number", Int64.Type}, {"close_at", type datetimezone}, {"created_at", type datetimezone}, {"updated_at", type datetimezone}})
in
    tblTypes

Edit: revised the query to remove references to our custom fields.

3

@zerogeneral Thank you very much for sharing the information, it is very useful.
Question: Do you know how to obtain the total time of each ticket?

4

Once you have the query PowerQuery you should be able to add a calculated column to calculate the duration from the created_at and closed_at columns.

  • Go to Add Column (ribbon) → Custom Column
  • Set a ColumnName (e.g. duration)
  • Enter formula: Duration.TotalHours([close_at]-[created_at])

It should look something like this:

image
image1603×700 76 KB

Here’s some more info on the Duration formulas if you need different units (days, minutes, etc.): https://learn.microsoft.com/en-us/powerquery-m/duration-totalhours