Overview
This guide gives step-by-step instructions on how to connect to the Cherre API as the data source in Power BI using Power Query. A Power BI template using Cherre Foundation sample queries will be used as a reference implementation.
Before getting started, you will need the following:
An active Cherre API Key to access your own data model within Cherre
Sample Power BI Template that has been shared with this guide
Please reach out to your Cherre Sales or Customer Success representative to get access to both
Connecting to Cherre
This section will walk you through the process of using the Cherre GraphQL API endpoint as the data source in Power Query.
A Cherre Power Query connection requires two parts:
Cherre GraphQL API Endpoint
Cherre API Key
Since Cherre sample template Power BI file has been set up with #1 (Connection as Cherre_api_connection in the Power Query). So now we will cover how to add your Cherre API Key to the Paramter_Api_Key for #2.
Add Your Cherre API Key to the Parameter in Power BI Power Query Editor
Open the Cherre Power BI Template PBIX file. Select Transform Data Button to open the Power Query Editor.
This will open up Power Query Editor in a New window.
While in the Power Query Window, enter the Parameter_API_URL and the Paramter_API_Key
Parameter_API_URL
This is where you put in the actual API URL to connect to Cherre . It is currently set by default as https://graphql.cherre.com
Paramter_API_Key
This is where you put in the actual API Key to connect to Cherre <Insert API Key Here> in the Current Value bar.
*For security reasons, the API key has been removed from the template Power BI file. Please note that the API key will travel with the PBIX file when sharing.
Once you are done entering the key, the default connection with a sample Cherre Foundation Query will be active.
Table Formatting
By default since the API returns JSON, the result needs to be converted to a table. This can be done in the Advanced Editor directly or it can be done in the Power Query UI. Depending on the number of variables being returned it can sometimes be more effective to do it in the UI.
Click on Record
Click Continue in the popup option.
When you See “List” in the Data Section Click on Into Table Button. You can also Right Click List and Into Table.
Then select the Expand Rows Button and Select “Expand to New Rows”
Click the Expand Columns Button Again and Select Desired Columns
*IMPORTANT
Uncheck Use Original Column Name as Prefix
Click “OK”
Being that the data comes in as JSON, it will be text by default if you need to retype, you can click on “ABC 123” to change the data types in Power Query.
Select Close and Apply in the Upper Navigation Menu
Now you are off and running!
Now we have successfully connected to your own data model within Cherre. There are a few Sample Queries to Cherre Foundation in the template that can be used as references for your own queries.
To Change the GraphQL query you will need to enter the Advanced Editor on the Cherre API Connection.
Select Cherre_API_Connection
Click the Advanced Editor
To change the graphQL query, you will need to paste the query after the third set of double quotes and before the next set of double-quotes.
*VERY VERY IMPORTANT – There must be NO LINE BREAKS IN THE Query OR Unnecessary Spaces.
There may be a better solution or Mquery script to handle the above issue. Let us know and Starbucks gift card on Cherre if you do!
Modifying Data source settings if you receive the following error:
Data Source Error related to not having an anonymous connection:
Select Data Source Settings
Select Edit Permissions in the Data Source Settings Dialogue Window
Within the Edit Permissions dialog window the Type should be “Anonymous” if it is not select “Edit”
In the Credentials Dialog window select “Anonymous” from the Left Hand Menu and then Click “Save”
Select “Ok” in Edit Permissions dialog window
Select “Close” in the Data Source Settings dialog window
And you should be all set! You may have to click “Refresh Preview” in the Upper menu but it should automatically refresh.
Reference
Links
More about Power Query
Converting JSON to usable Tables in Power BI
How to Use Cherre API
GraphQL Documentation
Sample Queries to Cherre Foundation
Query 1
let
Query1 = let
Source = Web.Contents( Parameter_API_URL ,
[
Headers=[
#"Method"="POST",
#"Content-Type"="application/json",
#"Authorization"="Bearer " & Parameter_API_Key
],
Content=Text.ToBinary("{""query"":""query parentFilters {tax_assessor(where: {gross_sq_ft: {_gt: 10000}, tax_assessor_id: {_gte: 672273624}} order_by: {tax_assessor_id: asc} limit: 100) {tax_assessor_id gross_sq_ft recorder__tax_assessor_id {document_type_code document_amount document_recorded_date}}}""}")
]
),
#"JSON" = Json.Document(Source),
data = JSON[data]
in
#"JSON"
in
Query1
Query 2
let
Query2 = let
Source = Web.Contents( Parameter_API_URL ,
[
Headers=[
#"Method"="POST",
#"Content-Type"="application/json",
#"Authorization"="Bearer " & Parameter_API_Key
],
Content=Text.ToBinary("{""query"":""query parentFilters {tax_assessor(where: {gross_sq_ft: {_gt: 100000},tax_assessor_id: {_gte: 672273624}}order_by: {tax_assessor_id: asc}limit: 100){tax_assessor_id gross_sq_ft}}""}")
]
),
#"JSON" = Json.Document(Source),
data = JSON[data]
in
#"JSON"
in
Query2