Overview

This guide gives step-by-step instructions on how to connect to the Cherre API as the data source in Excel using Power Query. An Excel 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
- Please reach out to your Cherre Sales or Customer Success representative to get access
Reach out to your CS rep for a Sample Excel Template

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 (this has been set up with the sample template)
Cherre API Key

Since Cherre sample template workbook has been set up with #1 (Connection as api_url in the workbook). So now we will cover how to add your Cherre API Key to the workbook for #2.

Add Your Cherre API Key to the Workbook

Open the Cherre Power Query Template workbook. Navigate to the Data tab, then select Queries & Connections.

This will open up a panel on the right to view all Queries and Connections within the workbook.

Double-click on the connection labeled as api_key. You should see something like the following, which has two Applied Steps cherre and authorization.
1. cherre
  1. This is where you put in the actual API key to connect to Cherre <Insert API Key here> in the formula bar.

*For security reasons, the API key has been removed from the template workbook. Please note that the API key will travel with the workbook when sharing.

Once you are done, save the changes. Now you have a working API key in the spreadsheet.

authorization
1. The second applied step authorization takes the API key from the previous step and formats it as an authorization request that will be sent to the Cherre GraphQL API endpoint. No additional action is required!

Now we have successfully connected to your own data model within Cherre. There are a few Sample Queries to Cherre Foundation in the template workbook that can be used as references for your own queries.

All queries should refer to the API connection api_url and api_key to pull data back. Specifically, queries all start with the following definition.

For example, in this sample query to get demographics data the first two lines are:

let

url = api_url,

authorization = api_key

Your data will be loaded to a new tab within your workbook, and you should see the query listed in the Queries & Connections pane at the right (also available in the Data toolbar). Double-click this to continue editing if you desire - there’s a lot more functionality within Power Query to explore.

Dynamic Parameterized Queries

Now that you’ve created a connection, we’ll walk you through how to use query parameters that will allow you to do dynamic filtering to select what data you’re pulling back.

Incorporating the Parameters into your Query

From here, we will work off of the query created in the Connecting to Cherre section of this documentation.

After inputting your API key above, you should still be in the Power Query Editor window. Select a sample query fnGetDdemographicsDataByZip from earlier, open the Advanced Editor, and we can begin.

First, we define the data types of our parameters. Above our request_body definition, we’ve created a step called variable_values that lists the parameters you created earlier along with their types. In this example, we’ve added “zip_code” and “geo_type” as the query filters, which will be used to filter demographics to a zipcode level.

As mentioned previously, it’s important that these types correspond to the type of field you’ll be filtering. In our example, the data types are:

"$zip_code: String!, $geo_type: String!"

Next, the query will use these parameters to filter our data in a where clause. It’s important to note that you have the full capabilities of GraphQL available to you here, and although we will showcase only a simple example, your selection logic can be arbitrarily complex.

"where: {
geography_type: {_eq: $geo_type},
geography_code: {_eq: $zip_code}
}”
Now we will show how the “front-end” tab Query Parameters controls the back-end query. When we input a new address from the dropdown, our built-in GraphQL functions will look for the zip code associated with the property and automatically search USA demographics on the zip code filter as outlined in previous steps.

Incorporating into Existing Excel Models

In the sample Excel template, we’ve created a “Display Model” tab, which is essentially bringing all 3 queries into one single sheet. This gives you an example of how you could leverage the Cherre connection to power live updates in existing Excel spreadsheets.

Reference

Sample Queries to Cherre Foundation

fnGetTaxAssessorDataByAddress

() =>
let
  url = api_url,
  authorization = api_key,

 
  variables_types = "
    $address: String!,
  ",
  where_clause = "where: {
    display_address: {_eq: $address},
  }",
  variables_values = Text.Format(
    """variables"": {
      ""address"": ""#[address]""
    }",
    [
      address = Text.From(fnGetParam("Address"))
    ]
  ),
  request_body = Text.Format("{""query"":
    ""query TaxAssessorQuery(#[variables_types]) {
      cherre_address(
        limit: #[records_per_page]
        #[where_clause]) {
          display_address
          tax_assessor__property_address {
            year_built
            lot_size_acre
            building_sq_ft
            units_count
            last_sale_amount
            last_deed_sale_date
            zip
          } 
      }
    }"",
    #[variables_values]
  }",
  [
    records_per_page = #"records_per_page",
    variables_types = #"variables_types",
    where_clause = #"where_clause",
    variables_values = #"variables_values"
  ]
  ),
  request_cleaned = Text.Clean(request_body),
  request_encoded_as_binary = Text.ToBinary(request_cleaned),
  json_response = Json.Document(Web.Contents(url,[
    Headers = [#"Authorization"=authorization,#"Content-Type"="application/json"],
    Content = request_encoded_as_binary
  ] )),
  data = json_response[data]
in
  data

fnGetDemographicsDataByZip

() =>
let
  url = api_url,
  authorization = api_key,
 
  variables_values = Text.Format(
    """variables"": {
      ""zip_code"": ""#[zip_code]"",
      ""geo_type"": ""ZI""
    }",
    [
      zip_code = fnGetZip(){0}
    ]
  ),
  request_body = Text.Format("{""query"":
    ""query DemographicsQuery($zip_code: String!, $geo_type: String!) {
      usa_demographics(
        limit: #[records_per_page]
        order_by: {year: desc}
        where: {
          geography_type: {_eq: $geo_type} 
          geography_code: {_eq: $zip_code}
        }) 
        {
          year
          population_2010_count
          median_household_income
          population_median_age
          crime_total_risk 
        }
    }"",
    #[variables_values]
  }",
  [
    records_per_page = #"records_per_page",
    variables_values = #"variables_values"
  ]
  ),
  request_cleaned = Text.Clean(request_body),
  request_encoded_as_binary = Text.ToBinary(request_cleaned),
  json_response = Json.Document(Web.Contents(url,[
    Headers = [#"Authorization"=authorization,#"Content-Type"="application/json"],
    Content = request_encoded_as_binary
  ] )),
  data = json_response[data]
in
  data

fnGetReisDataByAddress (requires REIS data license)

() =>
let
  url = api_url,
  authorization = api_key,
 
  variables_types = "
    $address: String!,
  ",
  where_clause = "where: {
    display_address: {_eq: $address},
  }",
  variables_values = Text.Format(
    """variables"": {
      ""address"": ""#[address]""
    }",
    [
      address = Text.From(fnGetParam("Address"))
    ]
  ),
  request_body = Text.Format("{""query"":
    ""query ReisQuery(#[variables_types]) {
      cherre_address(
        limit: #[records_per_page]
        #[where_clause]) {
          display_address
          tax_assessor__property_address {
            tax_assessor_reis_boundaries__bridge {
              reis_boundaries__cherre_reis_boundaries_pk {
                submarket_name
                demo_reis_market_trend__msa_sector {
                  sector
                  year
                  quarter
                  asking_rent
                  vacant_stock
                }
              }
            }
          } 
      }
    }"",
    #[variables_values]
  }",
  [
    records_per_page = #"records_per_page",
    variables_types = #"variables_types",
    where_clause = #"where_clause",
    variables_values = #"variables_values"
  ]
  ),
  request_cleaned = Text.Clean(request_body),
  request_encoded_as_binary = Text.ToBinary(request_cleaned),
  json_response = Json.Document(Web.Contents(url,[
    Headers = [#"Authorization"=authorization,#"Content-Type"="application/json"],
    Content = request_encoded_as_binary
  ] )),
  data = json_response[data]
in
  data