Zendesk
Power BI is an incredibly powerful tool for data visualization and analysis. One of the key features that makes Power BI so powerful is its ability to connect to a wide variety of data sources. In this article, we’ll focus on connecting to Zendesk, a popular customer support platform. Specifically, we’ll explore how to use Power Query M Language code to connect to Zendesk from inside Power BI.
What is Power Query M Language?
Before we dive into the specifics of connecting to Zendesk, let’s take a moment to talk about Power Query M Language. Power Query M Language is a programming language used by Power Query to interact with data sources. It’s a functional language, meaning that it operates on data in a set-based manner. This makes it ideal for working with large datasets.
Power Query M Language is used to write queries that pull, transform, and load data into Power BI. These queries are typically written in the Power Query Editor within Power BI. However, it’s also possible to write Power Query M Language code directly in the Advanced Editor within the Power Query Editor.
Connecting to Zendesk with Power Query M Language
Now that we have a basic understanding of Power Query M Language, let’s dive into connecting to Zendesk. To do this, we’ll need to use the Zendesk REST API.
The first step in connecting to Zendesk is to obtain an API token. This token is used to authenticate your requests to the Zendesk API. You can obtain an API token by logging into your Zendesk account and navigating to Settings > API > Add API Token.
Once you have an API token, you can use it to connect to Zendesk from within Power BI. To do this, open Power BI and select Get Data > Web from the Home tab. In the Web dialog box, enter the URL for the Zendesk API, which is typically “`https://yoursubdomain.Zendesk.com/api/v2“`. Replace “yoursubdomain” with the subdomain for your Zendesk account.
Next, click OK and you’ll be prompted to enter your API token. Enter your token and click Connect. Power BI will now connect to Zendesk and retrieve a list of available tables.
Writing Power Query M Language Code
Now that we’ve connected to Zendesk, we can start writing Power Query M Language code to retrieve and transform data. We’ll use the Advanced Editor in the Power Query Editor to write this code.
To open the Advanced Editor, click View > Advanced Editor from the Home tab in the Power Query Editor. In the Advanced Editor, you’ll see a blank query with some default code. Replace this code with the following:
let
Source = Web.Contents(“https://yoursubdomain.Zendesk.com/api/v2/tickets.json”),
#”Converted to Table” = Json.Document(Source)[tickets],
#”Converted to Table1″ = Table.FromList(#”Converted to Table”, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#”Expanded Column1″ = Table.ExpandRecordColumn(#”Converted to Table1″, “Column1”, {“id”, “subject”, “description”, “created_at”, “updated_at”, “priority”}, {“id”, “subject”, “description”, “created_at”, “updated_at”, “priority”})
in
#”Expanded Column1″
This code retrieves a list of tickets from Zendesk and transforms the data into a table. The resulting table contains columns for ticket ID, subject, description, creation date, update date, and priority.
Conclusion
In this article, we’ve explored how to use Power Query M Language code to connect to Zendesk from inside Power BI. We’ve covered the basics of Power Query M Language, how to obtain an API token for Zendesk, and how to write Power Query M Language code to retrieve and transform data from Zendesk. With this knowledge, you should be well-equipped to start connecting to and analyzing data from Zendesk in Power BI.
