All Collections
Developer Guide
API Documentation
Using Crelate API 3 in Power BI
Using Crelate API 3 in Power BI

This article showcases how to use Crelate's latest API within Power BI to get even more out of your data.

P
Written by Phillip Hunt
Updated over a week ago

Overview

Crelate offers powerful reporting capabilities within the application today, but it is also possible to leverage Crelate’s API in a separate BI tool to get even more out of your data.

This guide will show you how to leverage Crelate’s latest API in Microsoft Power BI. The endpoints that can be used for reporting use the GET HTTP verb. For clients requiring technical assistance or full configuration, our Professional Services team can be contracted for setup & support. Connect with us here for more details.

You can read more about Crelate’s general API capabilities here.

Getting Started

To use these features your organization must already have access to Crelate’s API which requires a License SKU of Business or higher. In order to connect and retrieve data you will need to first generate an API Key. This key should be considered sensitive as it will grant callers access to any data the key owner has access to.

Connecting

To start getting data we must create a query connection -

  1. In the Data group, expand Get data.

  2. Select Web and a dialog will open.

  3. Choose the Advanced option.

  4. Specify the GET endpoint you are trying to hit (e.g., https://app.crelate.com/api3/contacts).

  5. In the HTTP request header parameters section, add the parameter “x-api-key” and specify your API Key as its value.

  6. Click OK.

Table Transformation

After the initial retrieve is done you should see some columns (Data, Metadata, Errors). This initial format is not immediately usable for reporting, so we need to do additional transformation.

  1. In the Data column, click on the word “List” to drill down to a list of records.

  2. In the Convert group, click To Table.

  3. Keep the default values of “None” as the Delimiter and “Show as errors” for How to handle extra columns.

  4. Click OK.

  5. To expand the records further, click the expander icon in the header cell for Column1.

  6. A pop up will appear that asks which columns you want, so choose what you need. You should only select the columns you intend to use as including more columns will increase the size of the download and how long it takes. Also, you probably want to uncheck Use original column name as prefix to reduce the noise on the column names.

  7. Click OK.

Adding a Basic Chart

Now that the data is in a tabular format, we can display it in a report. We will get to columns that have “Record” as their cell value further down.

  1. To use the data, you will need to Apply your query, which can be found in the Close group.

  2. Navigate back to the Report View.

  3. Over in the Visualizations pane, add a standard Table by clicking its icon.

  4. To populate the chart with data, expand your query in the Data pane.

  5. Select a few columns to populate the Table and drag them into Columns (e.g., ContactNum, LastName, ModifiedOn).

  6. We can further refine the chart by applying local filters (e.g., top 20), sorting (e.g., ModifiedOn desc), titles, etc. by using the Filters pane and options in the Visual Editor.

Expanding Concepts

While we were able to add a basic chart, there is more we can do with the data returned from API 3. Let’s broaden into some additional concepts.

Special Data Type Transforms

A few fields are returned from API 3 as objects and will be visualized as “Record” in the column’s cell.

These cannot be used without some additional handling.

Some of these types include –

  • Addresses

  • Education

  • Emails

  • Lookups

  • Phone Numbers

  • Position

  • URLs

To use these fields, simply –

  1. Click the expander icon in the column’s header.

  2. In this instance you may want to check Use original column name as prefix as the property names will most likely repeat for additional records which could lead to confusion.

  3. Click OK.

  4. The column’s object will then be separated into its properties and will be easier to use.

Manipulating Data with Custom Columns

We are not regulated to only using the data as-is from the API. Since the query is just a regular data source, we can utilize standard Power BI features to give more context to our data. A simple example is creating a custom date column to show a trend of record creation.

  1. Click the Add Column tab in the Ribbon.

  2. Click Custom Column in the General Group.

  3. We are going to add a formula to populate our column which uses standard Power Query M syntax. This will format the UTC date as “Month Year” e.g., January 2024.

    Date.MonthName(DateTime.From([CreatedOn])) & " " & Text.From((Date.Year(DateTime.From([CreatedOn]))))

  4. After we have our new column, we can apply it to the query and switch back to Report View.

  5. Add a standard Stacked Column Chart by clicking its icon.

  6. Now make the X-axis our new custom column “Create Date” and the Y-axis should just be “Count of Id”. This will show how many records were created in a month/year combo. However, the dates will not be in chronological order at first.

  7. Next, add the standard CreatedOn field to the Tooltips section. This will allow you to sort the chart on its value.

  8. Click the More options menu (ellipsis top right of chart) and expand Sort axis.

  9. Select “First CreatedOn” and “Sort ascending”.

  10. After applying the changes, the chart should then display left to right in chronological order with each column totaling the number of records created in that month/year combo.

Advanced Concepts

Now that we have a handle on the basics of connecting, retrieving data, transforming simple types, and adding columns, let us delve into a few more advanced concepts.

Paging Data

Retrieving large amounts of data is possible through Crelate’s API but the process requires you to page that data in chunks.

All retrieve endpoints that return a list of records support pagination through querystring parameters. This is handled by offset (number of records to offset the retrieve by – this is 0 based) and limit (how many records to return per page, default is 50, max is 100).

Paging takes some additional setup to get working within Power BI. Follow this process –

  1. Create a connection to a data source as we have done before, like Contacts.

  2. Once you have the base data you want, click Advanced Editor in the Query group.

  3. You will need to add a snippet of code above the “let” in the query to convert the query into a function. This will instruct Power BI to pass in a variable called “PageStart” that will then be used as the offset in the URL. Follow the example below (note that the limit has been increased to the max 100 here too) –

  4. Click Done.

  5. Now you will probably want to rename the function in the Query Settings pane so you can easily refer to it later e.g., “fxContacts”.

  6. We then need to create a separate query where we will invoke this function. Right-click in the Queries pane and select New Query -> Blank Query.

  7. A blank query will then open. We are going to use this as the basis for our offsets so we will initially populate it with a list of values from 0 to 900 using {0..900} as the formula. Press Enter to populate the list. This will essentially give us the first 10 pages. This is only an example as you can use other queries to give you a more dynamic range instead.

  8. Once you have the entire list, you will need to convert the List into a Table.

  9. We cannot use this table as-is because it would cause a great degree of duplicate records to be returned – we must first trim it down to only the offsets that will yield unique pages.

  10. We need to add a new Modulo column to aid with this. Click the Add Column tab in the Ribbon Bar and go to Standard -> Modulo in the Number group.

  11. Specify 100 as the Value of the Modulo. This is because we are using 100 as our page size.

  12. We then need to filter the table to only show values that have a remainder of 0, so select that number to filter down the first column.

  13. Click OK.

  14. Now that the Modulo filtering has cut down our first column to only the unique page offsets, we can remove the Modulo column.

  15. Since we are creating a table of PageStart values, we need to convert the values from numbers to text.

  16. To do this, right-click the first column and go to Change Type -> Text.

  17. It is probably also a good idea to rename the first column to something like “PageStart” to make its usage more obvious.

  18. Now it is time to get the data by page. To start this process, click Invoke Custom Function while on the Add Column tab in the query we have been working in.

  19. This will open a dialog asking which function to invoke and which variable to use. We want to invoke our “fxContacts” function and use the “PageStart” column as the “PageStart” variable.

  20. Once this runs you will see several tables bound by offset. Some of these entries will display “Error” if the page is blank. We will correct that next.

  21. Right-click the “fxContacts” column and choose Replace Errors.

  22. You want to replace the missing tables with lowercase “null” as seen below to essentially blank out these pages. We have to do this because in our example we are always retrieving 10 pages.

  23. After this is done, you can expand fxContacts like we have done previously and it will expand out into usable tabular data.

Dealing with Lists

A few fields are returned from API 3 as object arrays and will be visualized as “List” in the column’s cell.

These cannot be used without some additional handling.

Some of these types include –

  • Multi-Lookups

  • Owners

Unfortunately, expanding the column as we did for the other “Record” objects will only give you the first element of the array. We will need to use a different approach.

  1. We can aggregate the values of a List by utilizing a Custom Column with a formula, so let’s add another Custom Column. Follow the steps outlined above.

  2. The example formula used here is basic but takes all the Title values of the Owners List and collapses it in a single comma delimited string. It is important to note that you should handle nulls otherwise the column will display an error. In this case we just show the text “None” if the value is null. Depending on your data you may want to alphabetize it, truncate it, change the delimiter, etc.

    if [Owners] = null 
    then "None" 
    else Text.Combine(List.Transform
    ([Owners],
     each Text.From(Record.Field(_, "Title"))
    ),
    ", "
    )

  3. The list will then be visualized as a comma delimited string in the new column.

  4. This column can then be used like any other column in a chart. This example shows Contact Ownership by people or group of people.

Cross Entity Queries

There are times when you need to show data from multiple entities at once or need it for filtering purposes. Power BI supports this through multiple queries and merging.

For our example we will show the human readable status name instead of the underlying integer value.

  1. You can add another source of data with the Query Editor by click New Source in the New Query Group.

  2. Select Web and you will see the connection dialog as before. This time we want to hit the https://app.crelate.com/api3/contacts/info endpoint which will give us Metadata about Contacts.

  3. You may see a dialog like this, Anonymous access is fine because the Header will contain the API Key. Click Connect.

  4. Unfortunately, when the data comes back Power BI will automatically expand it into something that is not that useful for our scenario. We will need to trim things down by rewinding the Applied Steps.

  5. Right-click the “Expanded Data” step and select Delete Until End. This will remove everything after the initial table conversion, and we can drill down to only the bits of data we care about.

  6. Once we only have the baseline table, we will work our way down to the EntityStatus List.

  7. Drill down into Record in Data.

  8. Drill down into Record in Fields.

  9. Drill down into the EntityStatus field.

  10. Click into List under Statuses.

  11. Convert the List into a Table.

  12. Expand Column1.

  13. You should now see the Entity Statuses.

  14. Your Applied Steps should look something like this –

  15. Now that we have the list of Statuses, we can combine it with our previous Contacts query and get the Status Name per Contact.

  16. Swap the query to the Contacts query in the Queries pane. This is important as we want to use Contacts as our base.

  17. Click Merge Queries in the Combine Group.

  18. A dialog will open that will ask you to select the columns that map up. For Contacts this will be EntityStatus. For Info this will be Value.

  19. Make sure to select the correct Info query from the dropdown.

  20. Once you have both columns selected, click OK.

  21. This will add a new Table column to the Contacts query.

  22. Use the expander to expand the Table and select the “Name” property.

  23. You may want to rename the column to something like “Status”.

  24. Once this is done you can apply the query and use the column in a chart. The example here shows Contacts in the system by Status –

Did this answer your question?