Power BI Reports

You can use Microsoft's Power BI desktop to generate sophisticated reports and graphs using the Agility Blue API. Microsoft offers a generous free tier for Power BI desktop (Windows only) that can be found on their Power BI site.

Connecting the Data Source

Power BI offers an OData data source connector, but it's not robust enough to utilize the authentication header scheme needed to connect to the Agility Blue Api. Fortunately, they do offer a way to connect to custom sources. The 2 key pieces of data that need to be supplied to the Agility Blue API is the API Key and the Workspace Id. Agility Blue will always return data in the JSON format, and Power BI can transform and mold these repsonses into tables that can then be used further in the reporting and visualization tool.

Create an API Key Parameter

Within the Power BI Power Query Editor, create a new parameter with these settings:

1. Name: "API KEY"
2. Required: Checked (True)
3. Type: Text
4. Suggested Values: Any value
5. Current Value: "ApiKey YOUR_API_KEY" (Paste in your API Key that you generated from Agility Blue in place of YOUR_API_KEY)

You can now use this API KEY parameter in the requests you want to make to the API.

Create a Workspace Id Parameter

Within the Power BI Power Query Editor, create a new parameter with these settings:

1. Name: "WORKSPACE ID"
2. Required: Checked (True)
3. Type: Text
4. Suggested Values: Any value
5. Current Value: Type in your workspace id. Your workspace id can be found within Agility Blue either by looking at the Workspaces grid view when you first login, or by looking at the URL while inside your workspace - it is the number after the "workspaces" portion of the URL. For example, in the URL https://agilityblue.com/workspaces/1/dashboard/allProjects, the number "1" is the workspace id.

You can now use this WORKSPACE ID parameter in the requests you want to make to the API. Your parameters list should look something like this (The Api Key displayed here is for demonstration purposes and not a valid key):

Basics of Making Requests

Once you have the parameters needed, you can start making requests. Within the Power BI Power Query Editor, create a new Blank Query.

The name of the query should be descriptive of what you're trying to request. If you're making a request to get a broad collection of matters, name the query "Matters". If you're making a request to get a specific subset of matters, such as "Active Matters", try to name the query something meaningful because this is the name of the query you will reference in the report editor.

Right-click on the new query, and choose "Advanced Editor". Paste in the following code and click done:

let
    Request = Json.Document(Web.Contents("https://api.agilityblue.com/odata/workspace/matters",
        [Headers=[#"Authorization"=#"API KEY", #"X-Workspace-Id"=#"WORKSPACE ID"]])),
    Result = Request[value]
in
    Result

Provided you have no syntatical errors and that your API Key is valid, you should see 2 rows in the main window:

• @odata.context: https://api.agilityblue.com/odata/workspace/$metadata#Matters
• value: [List]

You have successfully connected to the api and retreived data! Now you need to format the data into a meaningful way that allows you to generate visual reports.

Basics of Data Modeling

Once you have a response from the Agility Blue API, you can start modeling the data. As mentioned in the previous section, there are 2 rows that can be seen in the main window. The one that we are interested in, is the "value" row. We need to convert that value into a table and assist the query with data types, column orders, field names, sorting, and any other further merging or splitting you may want to do.

To convert the data into a table, make sure that the last "Navigation" step is highlighted in the applied steps pane and click on the "To Table" button on the command ribbon at the top. Leave the defaults, and click OK. You will now see a 1-column table with many more options available in the ribon where each row has the clickable text "Record".

To expand the record data into individual columns, click on the "Expand Data" button on the "Column1" header. The button is located on the right of the header and has two arrows pointing in opposite directions.

You will be presented with the column expander that contains all of the fields returned from the API. Select whichever columns you would like included in the table. Be sure to uncheck the "Use original column name as prefix" option.

At this point you now have a workable table of data that you can apply further processing on. We recommend converting data types from the "General" data type to specific types, specifically for boolean and date types. All Agility Blue date times use the "Date/Time/Timezone" type available in Power BI. You can change the data types by clicking on the "ABC 123" icon on the left of each column header and choose the appropriate type.

Entities with Custom Fields

If you have entities that define custom fields, you may have noticed that those fields are not present in the data returned in your request. Custom field information needs to be "expanded" in the initial request to be included. To expand data in the request, the following query parameter needs to be added:

$expand=fields($expand=value)

To continue with our Matters example, you would need to modify the request code to look like this:

= Json.Document(Web.Contents("https://api.agilityblue.com/odata/workspace/matters?$expand=fields($expand=value)",
        [Headers=[#"Authorization"=#"API KEY", #"X-Workspace-Id"=#"WORKSPACE ID"]]))

Change the data type of the primary key to a Whole Number and sort by that field in ascending order. Be sure to perform these steps after the Expanded Column1 and before expanding the Fields column. The primary key is the name of the Entity with an "Id" suffix (ex. "ClientId" for clients, "MatterId' for Matters, "ContactId" for contacts, etc.).

Because you modified the initial request, the API will now include a list field called "Fields" that contains the custom fields for each entity. In the step above about data modeling, include this column while expanding data. You will have the option to expand the Fields column, similar to the step you performed for the initial set of records.

When you click the expand data button, choose the "Expand to New Rows" option. This will convert the Fields from a list of values to records. Click the expand button again and you will see the field names available from the Fields object. In order for the pivot operation to work properly, you should only include these fields:

• Label
• Value

The "Label" field is the custom field label, and the "Value" field is a single object that contains the custom field value depending on what type of data the field is.

Expand the "Value" field to reveal the value of the custom field. In order for the pivot operation to work properly, you should only include these fields:

• ValueAsString
• ValueAsBoolean
• ValueAsNumber
• ValueAsDecimal
• ValueAsDate

These fields will be populated based on the Agility Blue data type defined for each custom field in each entity.

In order to compact the expanded custom record data back into single entity records, you will need to perform a series of transformations:

1. Merge the 5 value columns into one column.
   Because we want only one value represented for each custom field, we need to merge the "ValueAsString", "ValueAsBoolean", "ValueAsNumber", "ValueAsDecimal", and "ValueAsDate" columns into one column. We recommend using a delimiter that Agility Blue doesn't allow to be stored in these fields, like the TAB character.

= Table.CombineColumns(Table.TransformColumnTypes(#"Expanded Value", {{"ValueAsBoolean", type text}, {"ValueAsNumber", type text}, {"ValueAsDecimal", type text}}, "en-US"),{"ValueAsString", "ValueAsBoolean", "ValueAsNumber", "ValueAsDecimal", "ValueAsDate"}, Combiner.CombineTextByDelimiter("#(tab)", QuoteStyle.None),"Value")

2. Trim the new Value column.
   Once the data is combined, we want to trim the white space. This will allow the data to help reveal the first instance of a value, which is what we need to feed into the next step.

= Table.TransformColumns(#"Merged Columns",{{"Value", Text.Trim, type text}})

3. Extract the text before the first delimiter.
   If there are any "Non-Null" values, they will be now be available at the start of the Value data. This step ensures that we grab exactly the first instance of the value. In some cases, there may be data in more than one value column (if, for example, the data type was changed in Agility Blue after data had already been populated).

= Table.TransformColumns(#"Trimmed Text", {{"Value", each Text.BeforeDelimiter(_, "#(tab)"), type text}})

4. Pivot the Data.
   Pivoting data takes records and pivots them to columns. For this to work properly, the primary key column must be defined as a Whole Number in Power BI (as mentioned previously). Select the "Label" column, and choose the "Pivot Column" option in the Transform menu on the command ribbon at the top. Select the "Value" column, expand the Advanced options arrow, and select the "Don't Aggregate" option. Click OK. The M formula should look something like this:

= Table.Pivot(#"Extracted Text Before Delimiter", List.Distinct(#"Extracted Text Before Delimiter"[Label]), "Label", "Value")

You should end up with single rows of entities where each custom field is now added as a column with values populated. Check the primary key field to ensure that you no longer have repeated rows.

Be sure to convert the custom field data types to the appropriate Power BI data types based on the custom fields defined in Agility Blue, as this helps with writing the reports and visualizations later on and is considered an overall best practice for data modeling:

• The Agility Blue Basic Text, Rich Text, Single Choice, and Multiple Choice data types should be converted to the Power BI Text data type.
• The Agility Blue Whole Number data type should be converted to the Power BI Whole Number data type.
• The Agility Blue Decimal Number data type should be converted to the Power BI Decimal Number or Fixed Decimal Number data types.
• The Agility Blue Yes or No data type should be converted to the Power BI True/False data type.
• The Agility Blue Date and Time data type should be converted to the Power BI Date/Time/Timezone data type.
• The Agility Blue Date Only data type should be converted to the Power BI Date data type.

Request Considerations

In almost all cases, the Agility Blue API will return a maximum of 1,000 entities. If you expect that your requests are going to involve more than 1,000 entities, you should consider filtering the data and/or implement a paging strategy. The API allows you to use the $filter, $orderby, $top, and $skip query parameters as defined by the OASIS OData 4 protocol. You can retrieve the total number of records for each collection endpoint via the $countquery parameter.

Filter Examples

You would like to retrieve clients where the name field contains the text "AGM":

$filter=contains(name, 'agm')

You would like to retrieve clients where the name field does NOT contain the text "AGM":

$filter=not contains(name, 'agm')

You would like to retrieve matters where the client reference is exactly "000123":

$filter=clientreference eq '000123'

You would like to retrieve matters where the client reference is NOT "000123":

$filter=clientreference ne '000123'

You would like to retrieve matters where the matter reference has any value:

$filter=reference ne null

You would like to retrieve matters where the matter reference has NO value:

$filter=reference eq null

You would like to retrieve matters where the client name contains the text "AGM" OR the matter reference is exactly "000123":

$filter=(contains(clientname, 'agm') or reference eq '000123')

You would like to retrieve matters where the client reference is exactly"000123" AND the matter is active:

$filter=(clientreference eq '000123' and active eq true)

You would like to only retrieve matters that were created in February of 2020:

$filter=year(createdon) eq 2020 and month(createdon) eq 2

You would like to retrieve matters that were created during a specific date range (March 1st through March 31st):

$filter=createdon ge 2020-03-01 and createdon lt 2020-04-01

Custom Field Filtering

Custom fields require the following format when being referenced for filtering:

CF_{CUSTOM FIELD ID}

Custom field IDs can be retrieved from the Objects API endpoint and expanding the Fields property.

https://api.agilityblue.com/odata/workspace/objects?$expand=fields

For example, if you have a basic text field with a field id of 2075 named "Basic Text Field", you would construct a filter query to contain the text "agm" that looks like this:

$filter=contains(CF_2075, 'agm')

Paging Data

In order to page data, you need to include the $top and $skip query parameters in the request. For example, if you only want to return pages of 50 records at a time from the API, but anticipate having more than 50 records total, you would use a $top value of 50, and provide a $skip value equal to the page index multiplied by the $top value depending on which page you'd like to view. The page index is 0-based (starts at 0).

For example, to retrieve the first 50 records (page index 0), you would include in your request ($skip = 0 * 50 = 0):

$top=50&$skip=0

And to get the 2nd page (page index 1) ($skip = 1 * 50 = 50)

$top=50&$skip=50