Power BI by Microsoft is a collection of software services, apps, and connectors that work together to turn your unrelated sources of data into interactive insights. Using the Power BI Connector by Zendesk allows you to import your Zendesk data to Power BI.
This article covers the following topics:
- Overview of the Power BI Connector by Zendesk
- Signing in
- Using the Power Query M language to query data
Related articles:
Overview of the Power BI Connector by Zendesk
The existing Zendesk Power BI connector by Microsoft has a known issue where it can’t retrieve records for more than 1000 rows. For example, if an account has more than 1000 tickets, it returns the following 422 error:
Zendesk has developed a new Power BI connector that solves this issue and is currently in beta. The new connector uses Cursor Based Pagination (CBP) to retrieve data from the Zendesk API.
The new connector is named Zendesk Data (Beta). It will eventually replace the legacy connector, which is named Zendesk (Beta Legacy).
Signing in
The sign in experience is the same as the legacy connector.
After you sign in, you’ll see all existing endpoints are available in the new connector.
Using the Power Query M language to query data
If you are using the Power Query M language to query data, there’s a slight difference in the beginning part of the query. The legacy connector uses Zendesk.Tables() to initialize the Source:
let
Source = Zendesk.Tables("https://yoursubdomain.zendesk.com"),
views = Source{[Key="tickets"]}[Data]
in
views
The new connector uses ZendeskData.Contents() to initialize the Source:
let
Source = ZendeskData.Contents("https://yoursubdomain.zendesk.com", []),
Users = Source{[Name="Users"]}[Data]
in
Users
If you have existing queries using the legacy connector, change the Source initialization part and the rest of the code will work properly.
If you are using the Power BI connector in any other way, please contact Zendesk Customer Support, and we will provide a migration path.
40 comments
Jin Huang
Hi Scott Tynan , the search api only supports Offset Based Pagination, alternatively, you can use the Export Search Results api which supports Cursor Based Pagination.
Example: /api/v2/search/export?filter[type]=ticket&page[size]=1000&query=created>2024-01-01 created<2024-01-31
This will return tickets, including archived ones, for January 2024, with pagination. You can adjust the number of records to 1000 per page, although Zendesk recommends sticking to 100 records per page to avoid performance issues.
This article explains how to query data using cursor pagination.
0
Lucas Neves Silva
Hello team,
Could you help me understand how to establish a relationship between the Tickets table and the Organization table?
1
Scott Tynan
Lucas Neves Silva Tickets > Requester > Organization
0
Yosief Kassa
I have successfully connected to Zendesk with Power BI using the Beta connector. However,
1. I can't locate any of our model numbers, and without them, the data is incomplete and unusable. I created a ticket about this but no luck. Please help
2. How do get the older data? with the Beta connector can only access recent tickets.
3. why it takes hours to refresh? is there a way to tune it?
1
Magnus Buseth
Is there a way to set up incremental refresh in Power BI dataflows using the new connector? Loading all data takes too much time and is not efficient. I've tried filtering on the updated_at and created_at fields in the ticket table, but it seems like the filter is not query folding back to the source.
If I understand correctly, the connector will only allow us to fetch the last 120 days of tickets, since it does not include archived tickets. This greatly reduces the value, since we need to see data and trends over a longer time span.
3
Scott Tynan
This would be a great feature to support!
0
Operations Specialist
Is there a way to filter the Ticket Data as it is coming in, rather than having to filter it after?
In the query editor I can use M code to slice it any way I want, however, the first step always brings in the full Tickets table. My organization has a large amount of tickets, so when I try to bring in that table, it just breaks as there is too much data.
When pulling data from various SQL databases there is usually an option to pull in the data with a query, meaning I can just pull in the rows and columns I want from the get go. This is essential as I can limit the amount of data I am pulling in on the first step and it will actually load. Is there any way to do something similar with this connector?
Without the ability to pull old tickets as other commentors have mentioned and without the ability to filter data on retrieve, this connector is pretty much worthless for most business scenarios. Like why even bother making this thing at all
5
Magnus Buseth
Operations Specialist :
Unfortunately the connector does not support query folding. I contacted Zendesk Support and they confirmed this. So the connector will load all data, then filter it after it reaches Power BI. This makes the connector not usable if you have large ticket volumes, since the refresh will eventually time out. The fact that it will only let you pull the last 120 days of tickets, was also a showstopper for us.
I ended up using the CData Zendesk connector for Power BI, and this works great. It does require that you have a Power BI gateway set up to allow for refresh in Power BI service. The CData connector supports incremental refresh / query folding on the created_at and updated_at fields, and it allows you to load all historical data. I was able to load all +1 million tickets dating back to 2019.
The alternative would be to use the Zendesk API directly in Power BI, but this requires quite a bit of effort to set up and maintain.
1
MR VIDOOSH SING
Hi which end point has the time logged data?
Thanks.
0
MR VIDOOSH SING
Hi
Which end point has the time recorded data?
Thanks.
0