Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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:
Wish List/to do:
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/,
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:
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:
Code Limitations:
Thank you! This saved me so much time. The Authorization header gave me the error about requiring Anonymous Authentication so I removed it and it worked.
I had already used the Web Data Source to connect to "https://****.zendesk.com/api/v2/tickets.json" with my ZenDesk Admin Username and Password and I left the autogenerated query then copied it and replaced the query with your code but changed the first lines to the following.
let
ttable = "tickets",
Path = "/api/v2/" & ttable,
EntitiesPerPage = 100,
GetJson = (URLPath) =>
let Options = [RelativePath = URLPath],
RawData = Web.Contents("https://****.zendesk.com", Options),
Json = Json.Document(RawData)
in Json,
My pleasure (that is my code from an old account).
That looks like a potential good workaround. I might have to try it some time. Thank you for sharing.
Update:
if you have PowerBI premium then it looks like using a data flow might be possible: https://community.fabric.microsoft.com/t5/Community-Blog/Custom-Connector-in-Power-Automate-Microsof...
Useful link on using the personal gateway method: https://www.kasperonbi.com/how-to-enable-scheduled-refresh-for-your-custom-connector/, https://github.com/cskardon/Neo4jDataConnectorForPowerBi, https://xclave.co.uk/2021/07/01/power-bi-connector-auto-refresh/
could an option exist with the API; https://blog.crossjoin.co.uk/2022/08/07/calling-the-power-bi-enhanced-refresh-api-from-power-automat...
I am curious how third party connectors work. If there is no OOB / certified connector to the third party. Then you end up needed a custom connector to connect to the third party. Which seems redundant if we are trying to avoid using custom connectors.
Thus it makes sense to find a intermediary that has a OOB/certified connector. That is where my knowladge falls off. This is were I would suggest a more permanent solution with a proper pipeline leveraging Analysis Services etc.
Hello there! many thanks for posting this code as it looks very useful!
After building the .mez file and placing it into PowerBi under the bespoke connectors folder, when getting the different objects (Tickets, Organisations, etc), it is getting the below error: any idea why?
DataSource.Error: The downloaded data is HTML, which isn't the expected type. The URL may be wrong or you might not have provided the right credentials to the server.
This is how I'm extracting the data:
let
Source = Zendesk.Contents("ZendeskURL"),
Tickets1 = Source{[Name="Tickets"]}[Data]
in
Tickets1
I was having a typo in the username. After fixing the typo, the data loaded as expected
Hi @Anonymous ,
Thank you very much for sharing, I think it will inspire us to think.
Best Regards,
Henry
Hi @v-henryk-mstf, At lot of work has been added. Would be curous about your thoughts?
@Anonymous, I am not a power query guru when it comes to this code.
Any way you could tell me where I need to put in my Zendesk url, username and token?
All the spots I believe I need to do this seem to be references from prior in the code.
Thanks for any help you can provide.
this question is old, but to address this.
In the hard coded situation,
AccessTokenHeader = "Basic " &Binary.ToText(Text.ToBinary("email@email.com/token:0000000000000000000"), BinaryEncoding.Base64),
in the custom connector code:
When running the code, a pop up window will ask for username and API key.
User | Count |
---|---|
92 | |
86 | |
66 | |
62 | |
58 |
User | Count |
---|---|
150 | |
113 | |
95 | |
80 | |
72 |