Custom Zendesk Connector Power Query Code

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 protected]/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:

• Suprizingly quick.

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-api/#cursor-based-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:

1. Good security and no auto refresh (custom connector with no gateway or OATH with the default Zendesk connector).
2. Good Security and semi auto refresh (custom connector with personal gateway).
3. 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.