Connect to Telescope

You'll add two parameters, four helper queries and six tables in Power BI Desktop. It takes about ten minutes, and you only copy and paste.

1. Open Power Query

In Power BI Desktop, select Home → Transform data. The Power Query Editor opens. Every step below happens there.

2. Add the parameters

Select Home → Manage Parameters → New Parameter and add two parameters:

Name	Type	Current value
TelescopeBaseUrl	Text	https://api.telescope.eco
TelescopeApiKey	Text	your API key, starting with tel_

The base URL is a parameter so that you can point the report at staging, and so that Power BI Service can refresh it. See why.

3. Add the helper queries

For each query below, select Home → New Source → Blank Query, then Home → Advanced Editor, replace everything with the code, and select Done. Then rename the query (right-click it in the Queries pane → Rename) to the name in the heading.

TelescopeGet

Requests one API path and returns the JSON.

(path as text, optional query as nullable record) =>
    Json.Document(
        Web.Contents(
            TelescopeBaseUrl,
            [
                RelativePath = path,
                Query = if query = null then [] else query,
                Headers = [#"X-API-Key" = TelescopeApiKey, Accept = "application/json"]
            ]
        )
    )

TelescopeList

Requests every page of a list endpoint and returns all results.

(path as text, optional filters as nullable record) =>
let
    BaseQuery = if filters = null then [] else filters,
    GetPage = (cursor as nullable text) =>
        TelescopeGet(path, if cursor = null then BaseQuery else BaseQuery & [cursor = cursor]),
    // Only the cursor is read from `next`, so requests always go to TelescopeBaseUrl
    NextCursor = (page as record) =>
        if page[next] = null then null else Uri.Parts(page[next])[Query][cursor],
    Pages = List.Generate(
        () => GetPage(null),
        (page) => page <> null,
        (page) => let cursor = NextCursor(page) in
            if cursor = null then null else GetPage(cursor),
        (page) => page[results]
    )
in
    List.Combine(Pages)

TelescopePropertiesRaw

Loads every property once. The tables below take their rows from it.

let
    Source = TelescopeList("external/properties/")
in
    Source

To only load some properties, pass filters as a record, for example TelescopeList("external/properties/", [hazard_relevant = "true"]).

TelescopeNested

Turns one of the nested lists of every property into rows with a property_id column.

(field as text, columns as list) as table =>
let
    Rows = List.Combine(
        List.Transform(
            TelescopePropertiesRaw,
            (property) => List.Transform(
                Record.Field(property, field),
                (item) => Record.SelectFields(
                    item & [property_id = property[id]],
                    columns,
                    MissingField.UseNull
                )
            )
        )
    )
in
    Table.FromRecords(Rows, columns)

Turn off loading for the helpers

The helpers aren't tables you want in your report. Right-click TelescopePropertiesRaw and clear Enable load. The functions and parameters aren't loaded anyway.

4. Add the tables

Add each of these the same way: a blank query, the code in the Advanced Editor, then rename it.

Properties

let
    Columns = {
        "id", "internal_reference_id", "name", "value_estimate", "portfolio_id",
        "address", "postal_code", "postal_area", "country", "matrikkelnummer",
        "created_at", "updated_at"
    },
    Rows = List.Transform(
        TelescopePropertiesRaw,
        each Record.SelectFields(_, Columns, MissingField.UseNull)
    ),
    Properties = Table.FromRecords(Rows, Columns),
    Typed = Table.TransformColumnTypes(Properties, {
        {"id", type text}, {"internal_reference_id", type text}, {"name", type text},
        {"value_estimate", Int64.Type}, {"portfolio_id", type text},
        {"address", type text}, {"postal_code", type text}, {"postal_area", type text},
        {"country", type text}, {"matrikkelnummer", type text},
        {"created_at", type datetimezone}, {"updated_at", type datetimezone}
    })
in
    Typed

Hazards

let
    Source = TelescopeNested(
        "hazards",
        {"property_id", "name", "exposure", "relevant", "sensitivity", "vulnerability", "updated_at"}
    ),
    Typed = Table.TransformColumnTypes(Source, {
        {"property_id", type text}, {"name", type text}, {"exposure", type text},
        {"relevant", type logical}, {"sensitivity", type text},
        {"vulnerability", type text}, {"updated_at", type datetimezone}
    }),
    // Lets you sort exposure from lowest to highest instead of alphabetically
    WithOrder = Table.AddColumn(
        Typed,
        "exposure_order",
        each List.PositionOf(
            {"NOT_EXPOSED", "VERY_LOW", "LOW", "MEDIUM", "HIGH", "VERY_HIGH"},
            [exposure]
        ),
        Int64.Type
    )
in
    WithOrder

Biodiversity

let
    Source = TelescopeNested(
        "biodiversity",
        {"property_id", "name", "relevant", "proximity", "updated_at"}
    ),
    Typed = Table.TransformColumnTypes(Source, {
        {"property_id", type text}, {"name", type text}, {"relevant", type logical},
        {"proximity", type text}, {"updated_at", type datetimezone}
    })
in
    Typed

Buildings

let
    Source = TelescopeNested(
        "buildings",
        {
            "property_id", "id", "government_id", "name", "usage",
            "construction_year", "usage_area", "num_floors"
        }
    ),
    Typed = Table.TransformColumnTypes(Source, {
        {"property_id", type text}, {"id", type text}, {"government_id", type text},
        {"name", type text}, {"usage", type text}, {"construction_year", Int64.Type},
        {"usage_area", type number}, {"num_floors", Int64.Type}
    })
in
    Typed

EnergyCertificates

let
    Source = TelescopeNested(
        "energy_certificates",
        {
            "property_id", "id", "building_id", "label", "energy_use_by_m2",
            "issued_at", "expires_at"
        }
    ),
    Typed = Table.TransformColumnTypes(Source, {
        {"property_id", type text}, {"id", type text}, {"building_id", type text},
        {"label", type text}, {"energy_use_by_m2", type number},
        {"issued_at", type datetimezone}, {"expires_at", type datetimezone}
    })
in
    Typed

Portfolios

The portfolio list only returns root portfolios, so this query walks the tree: it requests each portfolio's detail to find its children. The path column holds the full name, like Norway / Oslo / Office.

let
    Walk = (portfolio as record, parentPath as nullable text) as list =>
        let
            Detail = TelescopeGet("external/portfolios/" & portfolio[id] & "/"),
            Path = if parentPath = null then Detail[name] else parentPath & " / " & Detail[name],
            Row = [
                id = Detail[id],
                name = Detail[name],
                internal_reference_id = Detail[internal_reference_id],
                parent_id = Detail[parent_id],
                path = Path,
                updated_at = Detail[updated_at]
            ],
            Children = List.Transform(Detail[portfolios], (child) => @Walk(child, Path))
        in
            {Row} & List.Combine(Children),
    Roots = TelescopeList("external/portfolios/"),
    Rows = List.Combine(List.Transform(Roots, (root) => Walk(root, null))),
    Portfolios = Table.FromRecords(
        Rows,
        {"id", "name", "internal_reference_id", "parent_id", "path", "updated_at"}
    ),
    Typed = Table.TransformColumnTypes(Portfolios, {
        {"id", type text}, {"name", type text}, {"internal_reference_id", type text},
        {"parent_id", type text}, {"path", type text}, {"updated_at", type datetimezone}
    })
in
    Typed

This makes one request per portfolio. With hundreds of portfolios, it is the slowest part of a refresh.

5. Sign in

The first time a query runs, Power BI asks how to connect to https://api.telescope.eco. Choose Anonymous and select Connect. The API key already travels in the X-API-Key header, so Power BI shouldn't add credentials of its own.

If you picked something else by mistake, change it under File → Options and settings → Data source settings: select the Telescope URL, then Edit Permissions → Edit → Anonymous.

6. Load the data

Your Queries pane should now look like this:

Select Home → Close & Apply. Power BI loads the six tables. Continue with the data model.

Use staging

Set TelescopeBaseUrl to https://api-staging.telescope.eco and TelescopeApiKey to a staging key. Keys only work in the environment they were created in.