Background:
The Default Zendesk Connector is in beta and for those who have tried it, will know that due to a bug it can not pull more then 1,000 rows due to an API limit. This bug prevents data being pulled in. By adding a filter you pull in less then 1,000 rows locally, but not in the service as this bug will prevent scheduled refresh. (side note: this is my experience and assume many others are having it based on the number of posts on the topic).
Aim:
This is a popular issue and thus I thought sharing Power Query code to connect to zendesk would help many. In addition, there a number of ways the code can be improved or have different versions with different features. So a place to collectively work on the issue would hopefully be valuable.
Code:
let
ttable = "tickets",
Path = "/api/v2/" & ttable,
AccessTokenHeader = "Basic " &Binary.ToText(Text.ToBinary("email@email.com/token:0000000000000000000"), BinaryEncoding.Base64),
EntitiesPerPage = 100,
GetJson = (URLPath) =>
let Options = [RelativePath = URLPath, Headers=[ #"Authorization" = AccessTokenHeader ]],
RawData = Web.Contents("https://****.zendesk.com", Options),
Json = Json.Document(RawData)
in Json,
GetEntityCount = () =>
let URLPath = Path,
Json = GetJson(URLPath),
Count = Json[#"count"]
in Count,
GetPage = (Page.no) =>
let URLPath = Path & "?page=" & Text.From(Page.no),
Json = GetJson(URLPath),
Value = Record.FieldValues(Record.SelectFields(Json, ttable)){0}
in Value,
EntityCount = List.Max({ EntitiesPerPage, GetEntityCount() }),
PageCount = Number.RoundUp(EntityCount / EntitiesPerPage),
Pages = List.Generate(() => [result = try GetPage(1) otherwise null, page=1],
each [page] <> PageCount,
each [result = try GetPage([page]+1) otherwise null, page=[page]+1],
each [result]
),
#"Converted to Table" = Table.FromList(Pages, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table", "Column1"),
#"Expanded Column2" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1", {"url", "id", "external_id", "via", "created_at", "updated_at", "type", "subject", "raw_subject", "description", "priority", "status", "recipient", "requester_id", "submitter_id", "assignee_id", "organization_id", "group_id", "collaborator_ids", "follower_ids", "email_cc_ids", "forum_topic_id", "problem_id", "has_incidents", "is_public", "due_at", "tags", "custom_fields", "satisfaction_rating", "sharing_agreement_ids", "fields", "followup_ids", "brand_id", "allow_channelback", "allow_attachments"}, {"Column1.url", "Column1.id", "Column1.external_id", "Column1.via", "Column1.created_at", "Column1.updated_at", "Column1.type", "Column1.subject", "Column1.raw_subject", "Column1.description", "Column1.priority", "Column1.status", "Column1.recipient", "Column1.requester_id", "Column1.submitter_id", "Column1.assignee_id", "Column1.organization_id", "Column1.group_id", "Column1.collaborator_ids", "Column1.follower_ids", "Column1.email_cc_ids", "Column1.forum_topic_id", "Column1.problem_id", "Column1.has_incidents", "Column1.is_public", "Column1.due_at", "Column1.tags", "Column1.custom_fields", "Column1.satisfaction_rating", "Column1.sharing_agreement_ids", "Column1.fields", "Column1.followup_ids", "Column1.brand_id", "Column1.allow_channelback", "Column1.allow_attachments"})
in
#"Expanded Column2"
Code Pros:
Code Limitations:
- pulls only unarchived tickets. Limitation due to the API used.
- Unsure if the 1,000 rows limit applies (my zendesk does not have 1,000 unarchived tickets). If it does PageCount can be limited to a max of 10.
- Does not appear to work in the PBI Service. I tried making the table a parameter and changing it in the service, but the data didn't update after a refresh like it does locally..
Wish List/to do:
- get authentication to work with username & password. Not everyone has admin access. My attempts so far on this have not worked.
- have more secure authentication, hard coding credentials is eeek.
- Create version that works with incremental refresh (according to the documentation can go above 1k limit). Even better if this would work together with PBI servers' incremental refresh.
References:
Majority of the code comes from this post: https://community.powerbi.com/t5/Power-Query/Handle-pagination-in-Zendesk-Basic-Auth/td-p/1382042
Incremental Refresh from Zendesk: https://developer.zendesk.com/api-reference/ticketing/ticket-management/incremental_exports/,
https://developer.zendesk.com/documentation/ticketing/managing-tickets/using-the-incremental-export-...
Not hard coding the Authentication:
The only way this seems to be possible is with a full custom connector. Custom connectors' big limitation is that you need a gateway to enable auto refresh. So assuming you can get the above code to work with the Service & you don't want to deal with a Gateway (e.g. daily refreshing using a personal gateway won't work if laptop will be off or cost of a server for Standard). This leads to a choice between:
- Good security and no auto refresh (custom connector with no gateway or OATH with the default Zendesk connector).
- Good Security and semi auto refresh (custom connector with personal gateway).
- bad security with auto refresh (the top code).
I have created an idea that could fix this issue, by storing the .mez files in one drive here.
Here is the custom connector using the visual studio SDK. This returns a column of records for each table and not all the columns (will need to expand the data to get the ones you want).
// This file contains your Data Connector logic
// Implimentation
section Zendesk;
[DataSource.Kind="Zendesk", Publish="Zendesk.Publish"]
shared Zendesk.Contents = Value.ReplaceType(ZenNavTable, type function (url as Uri.Type) as any);
ZenNavTable = (url as text) =>
let
objects = #table(
{"Name", "Data", "ItemKind", "ItemName", "IsLeaf"},{
{"Articles", GetHelpTableData(url, "articles"), "Table", "Table", true},
{"Organizations", GetMainTableData(url, "organizations"), "Table", "Table", true},
{"Tickets", GetMainTableData(url, "tickets"), "Table", "Table", true},
{"Ticket Metrics", GetMainTableData(url, "ticket_metrics"), "Table", "Table", true},
{"Users", GetMainTableData(url, "users"), "Table", "Table", true}
}),
NavTable = Table.ToNavigationTable(objects, {"Name"}, "Name", "Data", "ItemKind", "ItemName", "IsLeaf")
in
NavTable;
// API Table location
GetHelpTableData = (zendeskUrl as text, tableName as text) as table =>
let
BaseUrl = zendeskUrl&"/api/v2/help_center/"&tableName,
TableData = GetTableData(BaseUrl, tableName)
in
TableData;
GetMainTableData = (zendeskUrl as text, tableName as text) as table =>
let
BaseUrl = zendeskUrl&"/api/v2/"&tableName,
TableData = GetTableData(BaseUrl, tableName)
in
TableData;
// Authentication
GetJson = (Url) =>
let
UserName = Extension.CurrentCredential()[Username],
APIToken = Extension.CurrentCredential()[Password],
AccessTokenHeader = "Basic " &Binary.ToText(Text.ToBinary(UserName&"/token:"&APIToken), BinaryEncoding.Base64),
Options = [Headers=[ #"Authorization" = AccessTokenHeader ]],
RawData = Web.Contents(Url, Options),
Json = Json.Document(RawData)
in
Json;
GetTableData = (zendeskUrl as text, tableName as text) as table =>
let
BaseUrl = zendeskUrl,
EntitiesPerPage = 100,
GetEntityCount = () =>
let Url = BaseUrl,
Json = GetJson(Url),
Count = Json[#"count"]
in Count,
GetPage = (Page.no) =>
let Url = BaseUrl & "?page=" & Text.From(Page.no),
Json = GetJson(Url),
Value = Record.FieldValues(Record.SelectFields(Json,tableName)){0}
in Value,
EntityCount = List.Max({ EntitiesPerPage, GetEntityCount() }),
PageCount = Number.RoundUp(EntityCount / EntitiesPerPage),
Pages =
if PageCount > 1 then
List.Generate(() => [result = try GetPage(1) otherwise null, page=1],
each [page] <> PageCount,
each [result = try GetPage([page]+1) otherwise null, page=[page]+1],
each [result]
)
else
GetPage(1),
ConvertedtoTable = Table.FromList(Pages, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
ExpandedColumn1 =
if PageCount > 1 then
Table.ExpandListColumn(ConvertedtoTable, "Column1")
else
ConvertedtoTable
in
ExpandedColumn1;
// Data Source Kind description
Zendesk = [
Authentication = [
// Key = [],
UsernamePassword = [
Label = "Admin Authentication",
PasswordLabel = "API Token"
]
// Windows = [],
//Implicit = []
],
Label = Extension.LoadString("DataSourceLabel")
];
// Data Source UI publishing description
Zendesk.Publish = [
Beta = true,
Category = "Other",
ButtonText = { Extension.LoadString("ButtonTitle"), Extension.LoadString("ButtonHelp") },
LearnMoreUrl = "https://powerbi.microsoft.com/",
SourceImage = Zendesk.Icons,
SourceTypeImage = Zendesk.Icons
];
Zendesk.Icons = [
Icon16 = { Extension.Contents("Zendesk16.png"), Extension.Contents("Zendesk20.png"), Extension.Contents("Zendesk24.png"), Extension.Contents("Zendesk32.png") },
Icon32 = { Extension.Contents("Zendesk32.png"), Extension.Contents("Zendesk40.png"), Extension.Contents("Zendesk48.png"), Extension.Contents("Zendesk64.png") }
];
// Common Functions
Table.ToNavigationTable = (
table as table,
keyColumns as list,
nameColumn as text,
dataColumn as text,
itemKindColumn as text,
itemNameColumn as text,
isLeafColumn as text
) as table =>
let
tableType = Value.Type(table),
newTableType = Type.AddTableKey(tableType, keyColumns, true) meta
[
NavigationTable.NameColumn = nameColumn,
NavigationTable.DataColumn = dataColumn,
NavigationTable.ItemKindColumn = itemKindColumn,
Preview.DelayColumn = itemNameColumn,
NavigationTable.IsLeafColumn = isLeafColumn
],
navigationTable = Value.ReplaceType(table, newTableType)
in
navigationTable;
Code Pros:
- Suprizingly quick, much faster then the default connector
- secure
- dynamic, easy to add other tables.
- only bring back what you need, can start filtering the data when you expand the records for each table.
Code Limitations:
- pulls only unarchived tickets. Limitation due to the API used.
- requires a gateway to auto refresh
- only show a single column of records for each table in the nagivation panel, you will need to adapt the code if you want it show the data.
