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
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.
1 Like
@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?
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:
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
Hello, when doing the incremental update using the created_at column, power bi onilne returns the close_at with the wrong date, how can I assign the date in the API call or another solution so that the close_at date is correct?
What do I add for token = “123*****token789” and then [quote=“zerogeneral, post:2, topic:12353”]
Headers = [Authorization="Token token=" & token]
[/quote]
I tried putting in the API token in both and it wouldn’t work, not sure what to add
The token needs to be a ‘Personal Access Token’ generated at Profile → Token Access → click Create. Give the token a name, and select Permissions - I used report and ticket.agent, then click Create. Copy the generated token string and save it somewhere, as .
Update the PowerBI query 2nd line to:
token = “token string copied from above”
and update the 4th line to put the correct hostname for your environment into the URL.
Note that the monitoring token at Admin → Monitoring → Current Token isn’t the same as an Personal Access Token. The monitoring token works for the health check url, but not for accessing ticket data.