Skip to main content

Tutorial for Connecting Power BI to the Corva Data API - Drilling Metrics


Introduction to the Corva Data API​

The Corva Data API is a REST API that allows access to real-time operational data. It provides secure, HTTP-based endpoints that return data in JSON format, ideal for use in analytics applications such as Power BI, Excel, or other data visualization tools.

Corva endpoints allow queries on drilling metrics such as ROP (Rate of Penetration), connection times, weight on bit, among others. Each call requires authentication via an API key.


Prerequisites​

NOTE: Power BI is only available in Windows environments.

  1. Install Power BI here

  2. Request a Corva API key.
    2.1 Request from Corva Solution Engineer or Dev Advocate


Implementation Steps​

Step 1: Launch Power BI Desktop.

Step 2: Select “Get Data” and then “Blank Query”.

Step 3: Access the “Advanced Editor” and insert the following code:

let

url \= "https://data.corva.ai/api/v1/data/corva/metrics/",

query \= \[

    query \= "{""asset\_id"": ASSET\_HERE, ""data.key"":""on\_bottom\_time""}",

    limit \= "10000",

    sort \= "{""timestamp"": 1}"

\],

source \= Json.Document(

    Web.Contents(

        url,

        \[

            Headers \= \[

                \#"Authorization" \= "API YOUR\_API\_KEY\_HERE"

            \],

            Query \= query

        \]

    )

),

results \= source

in

results

Step 4: Replace `Asset_HERE` with the desired asset identifier and `YOUR_API_KEY_HERE` with your valid API key.

Step 5: In the results, click on `List` and then `Convert to Table`.

Step 6: Expand the columns to display the data individually.


Metrics Available in the Corva Metrics Dataset​

Please be so kind as to review the CSV linked here for you to check the metrics available for our Corva Metrics Dataset.

Metrics Corva


How to Query the Corva Metrics Dataset​

1. Get On-Bottom ROP (Rate of Penetration) for the Last 24 hrs​

Objective: Retrieve the average "On-Bottom ROP" (Rate of Penetration) during the last 24 hours.

  • Dataset: corva#metrics

  • Query:

https://data.corva.ai/api/v1/data/corva/metrics/aggregate/?match={"company_id":{company_id}, "data.key":"rop","data.type":"last24h","data.value": {"$gt": 0},"data.year": {current_year}, "data.month": {previous_month_index}, "data.day": {current_date}, "data.hour": {current_hour}}&limit=10000&sort={"timestamp": -1}&project={"_id": 0, "data.asset_id": 1, "data.key": 1, "data.type": 1, "data.value": 1}

  • Query Requirements:

  • company_id: Your company's unique identifier.

  • previous_month_index: The index of the previous month, used to fetch the correct data range. Months are zero-indexed, meaning:

    • January \= 0

    • February \= 1

    • March \= 2

    • April \= 3

    • May \= 4

    • June \= 5

    • and so on.

    • For example:

      • If the current month is May (calendar month 5), then previous_month_index will be 4 (for April).

      • If the current month is July (calendar month 7), then previous_month_index will be 6 (for June).

  • current_year: The current year for the query.

  • current_date: The current day of the month for the query range.

  • current_hour: The current hour for querying the last 24 hours of data.

  • Desired Data:

    • data.rop: The rate of penetration data for the last 24 hours, which can be averaged in Power BI.

Power BI Query Implementation for On-Bottom ROP for the Last 24 hrs​

In Power BI, the script should be adjusted to query for the On-Bottom ROP based on the above query and use it to generate visualizations of the average penetration rate. The following steps can be used to configure the query in Power BI:

  1. Modify Query Parameters:

    • Replace {company_id}, {current_year}, {current_month_minus_1}, {current_date}, and {current_hour} with appropriate dynamic values in Power BI to calculate and fetch the correct data.

  2. Use the "Json.Document" function:

    • After setting up the correct API query, use the Json.Document function in Power BI to retrieve the ROP data.

  3. Process the Data:

    • In Power BI, aggregate the data.value field to calculate the average ROP over the last 24 hours for each asset.

Power BI Script Example for On-Bottom ROP for the Last 24 hrs​

let
    url = "https://data.corva.ai/api/v1/data/corva/metrics/aggregate/",
    query = [
        match = "{
            ""company_id"": your_company_id_here,
            ""data.key"": ""rop"",
            ""data.type"": ""last24h"",
            ""data.value"": {""$gt"": 0},
            ""data.year"": 2025,
            ""data.month"": 4,
            ""data.day"": 29,
            ""data.hour"": 16
        }",
        limit = "10000",
        sort = "{""timestamp"": -1}",
        project = "{
            ""_id"": 0,
            ""data.asset_id"": 1,
            ""data.key"": 1,
            ""data.type"": 1,
            ""data.value"": 1
        }"
    ],
    source = Json.Document(
        Web.Contents(
            url,
            [
                Headers = [ #"Authorization" = "API YOUR_API_KEY_HERE" ],
                Query = query
            ]
        )
    ),
    results = source
in
    results

2. Get On-Bottom ROP (Rate of Penetration) by BHA for the Last 24 hrs​

Objective: Retrieve the "On-Bottom ROP" (Rate of Penetration) data for each BHA (Bottom Hole Assembly) during the last 24 hours.

  • Dataset: corva#metrics

  • Query:

https://data.corva.ai/api/v1/data/corva/metrics/aggregate/?match={"company_id":{company_id}, "data.key":"rop","data.type":"bha","data.value": {"$gt": 0},"timestamp": {"$gte": {current_unix_timestamp_minus_86400_seconds}}}&limit=10000&sort={"timestamp": -1}&project={"_id": 0, "data.asset_id": 1, "data.key": 1, "data.type": 1, "data.value": 1, "data.bha_id": 1, "data.well_sections": 1}

  • Query Requirements:

  • company_id: Your company's unique identifier.

  • current_unix_timestamp_minus_86400_seconds: The Unix timestamp representing 24 hours ago from the current time, used to fetch data for the last 24 hours.

  • Desired Data:

    • data.rop: The rate of penetration for each Bottom Hole Assembly (BHA), which can be aggregated or averaged as needed in Power BI.

    • data.bha_id: The BHA identifier to distinguish between different assemblies.

    • data.well_sections: Information on the well sections where the ROP data was collected.

Power BI Query Implementation for On-Bottom ROP by BHA for Last 24 hrs​

In Power BI, the script should be adjusted to query for the "On-Bottom ROP" by BHA based on the above query and generate visualizations. Here’s the step-by-step process:

  1. Modify Query Parameters:

    • Replace {company_id} and {current_unix_timestamp_minus_86400_seconds} with the dynamic values in Power BI to calculate and fetch the correct data.

  2. Use the "Json.Document" function:

    • After setting up the correct API query, use the Json.Document function in Power BI to retrieve the ROP data.

  3. Process the Data:

    • In Power BI, aggregate the data.value field (ROP) over the last 24 hours, grouping by BHA or asset as needed, to visualize the data.

Power BI Script Example for On-Bottom ROP by BHA for Last 24 hrs​

let
    // Step 1: Set company ID and calculate Unix timestamp for 24 hours ago
    companyId = your_company_id_here,
    currentTime = DateTimeZone.UtcNow(),
    unixNow = Duration.TotalSeconds(currentTime - DateTimeZone.From(#datetime(1970,1,1,0,0,0))),
    unix24hrsAgo = Number.RoundDown(unixNow - 86400),  // 24 hours ago

    // Step 2: Set API endpoint
    url = "https://data.corva.ai/api/v1/data/corva/metrics/aggregate/",

    // Step 3: Build API query parameters
    query = [
        match = "{
            ""company_id"": " & Text.From(companyId) & ",
            ""data.key"": ""rop"",
            ""data.type"": ""bha"",
            ""data.value"": {""$gt"": 0},
            ""timestamp"": {""$gte"": " & Text.From(unix24hrsAgo) & "}
        }",
        limit = "10000",
        sort = "{""timestamp"": -1}",
        project = "{
            ""_id"": 0,
            ""data.asset_id"": 1,
            ""data.key"": 1,
            ""data.type"": 1,
            ""data.value"": 1,
            ""data.bha_id"": 1,
            ""data.well_sections"": 1
        }"
    ],

    // Step 4: Call the API using Authorization header
    source = Json.Document(
        Web.Contents(
            url,
            [
                Headers = [
                    #"Authorization" = "API YOUR_API_KEY_HERE"
                ],
                Query = query
            ]
        )
    ),

    // Step 5: Return results
    results = source
in
    results


3. Get On-Bottom ROP (Rate of Penetration) by Hole Section Last 24 hrs​

Objective: Retrieve the "On-Bottom ROP" (Rate of Penetration) data for each hole section during the last 24 hours.

  • Dataset: corva#metrics

  • Query:

https://data.corva.ai/api/v1/data/corva/metrics/aggregate/?match={"company_id":{company_id}, "data.key":"rop","data.type":"well_section","data.value": {"$gt": 0},"timestamp": {"$gte": {current_unix_timestamp_minus_86400_seconds}}}&limit=10000&sort={"timestamp": -1}&project={"_id": 0, "data.asset_id": 1, "data.key": 1, "data.type": 1, "data.value": 1, "data.well_section": 1}

  • Query Requirements:

  • company_id: Your company's unique identifier.

  • current_unix_timestamp_minus_86400_seconds: The Unix timestamp representing 24 hours ago from the current time, used to fetch data for the last 24 hours.

  • Desired Data:

    • data.rop: The rate of penetration for each hole section, which can be averaged as needed in Power BI.

    • data.well_section: The well section identifier to distinguish between different sections within the same well.

Power BI Query Implementation for On-Bottom ROP by Hole Section for Last 24 hrs​

In Power BI, the script should be adjusted to query for the "On-Bottom ROP" by hole section based on the above query and generate visualizations. Here’s the process:

  1. Modify Query Parameters:

    • Replace {company_id} and {current_unix_timestamp_minus_86400_seconds} with appropriate dynamic values in Power BI to calculate and fetch the correct data.

  2. Use the "Json.Document" function:

    • After setting up the correct API query, use the Json.Document function in Power BI to retrieve the ROP data.

  3. Process the Data:

    • In Power BI, aggregate the data.value field (ROP) over the last 24 hours, grouping by hole section or asset as needed, to visualize the data.

Power BI Script Example for On-Bottom ROP by Hole Section for Last 24 hrs​

let
    // Step 1: Define company ID and calculate Unix timestamp for 24 hours ago
    companyId = your_company_id_here,
    currentTime = DateTimeZone.UtcNow(),
    unixNow = Duration.TotalSeconds(currentTime - DateTimeZone.From(#datetime(1970, 1, 1, 0, 0, 0))),
    unix24hrsAgo = Number.RoundDown(unixNow - 86400),  // 24 hours ago in seconds

    // Step 2: Corva API base URL
    url = "https://data.corva.ai/api/v1/data/corva/metrics/aggregate/",

    // Step 3: Build the query object with match, limit, sort, and projection
    query = [
        match = "{
            ""company_id"": " & Text.From(companyId) & ",
            ""data.key"": ""rop"",
            ""data.type"": ""well_section"",
            ""data.value"": {""$gt"": 0},
            ""timestamp"": {""$gte"": " & Text.From(unix24hrsAgo) & "}
        }",
        limit = "10000",
        sort = "{""timestamp"": -1}",
        project = "{
            ""_id"": 0,
            ""data.asset_id"": 1,
            ""data.key"": 1,
            ""data.type"": 1,
            ""data.value"": 1,
            ""data.well_section"": 1
        }"
    ],

    // Step 4: Make the API call with the Authorization header
    source = Json.Document(
        Web.Contents(
            url,
            [
                Headers = [
                    #"Authorization" = "API YOUR_API_KEY_HERE"
                ],
                Query = query
            ]
        )
    ),

    // Step 5: Return the parsed results
    results = source
in
    results

4. Get Weight to Weight for the Last 24 hrs​

Objective: Retrieve the "Weight to Weight" average data for the last 24 hours.

  • Dataset: corva#metrics

  • Query:

https://data.corva.ai/api/v1/data/corva/metrics/aggregate/?match={"company_id":{company_id}, "data.key":"weight_to_weight_average","data.type":"last24h","data.value": {"$gt": 0},"data.year": {current_year}, "data.month": {previous_month_index}, "data.day": {current_date}, "data.hour": {current_hour}}&limit=10000&sort={"timestamp": -1}&project={"_id": 0, "data.asset_id": 1, "data.key": 1, "data.type": 1, "data.value": 1}

  • Query Requirements:

  • company_id: Your company's unique identifier.

  • current_year: The current year for the query.

  • previous_month_index: The index of the previous month, used to fetch the correct data range. Months are zero-indexed, meaning:

    • January \= 0

    • February \= 1

    • March \= 2

    • April \= 3

    • May \= 4

    • June \= 5

    • and so on.

  • For example:

    • If the current month is May (calendar month 5), then previous_month_index will be 4 (for April).

    • If the current month is July (calendar month 7), then previous_month_index will be 6 (for June).

  • current_date: The current day for the query range.

  • current_hour: The current hour for querying the last 24 hours of data.

  • Desired Data:

    • data.weight_to_weight_average: The average "Weight to Weight" data for the last 24 hours, which can be averaged in Power BI.

    • data.asset_id: The asset ID associated with the retrieved weight to weight data.

Power BI Query Implementation for Weight to Weight for the Last 24 hrs​

In Power BI, the script should be adjusted to query for the "Weight to Weight" data based on the above query and use it for visualizations. Here’s the process:

  1. Modify Query Parameters:

    • Replace {company_id}, {current_year}, {previous_month_index}, {current_date}, and {current_hour} with appropriate dynamic values in Power BI to calculate and fetch the correct data.

  2. Use the "Json.Document" function:

    • After setting up the correct API query, use the Json.Document function in Power BI to retrieve the data.

  3. Process the Data:

    • In Power BI, aggregate the data.value field to calculate the average "Weight to Weight" over the last 24 hours for each asset.

Power BI Script Example for Weight to Weight for the Last 24 hrs​

let
    // Hardcoded input values
    companyId = your_company_id_here,
    year = 2025,
    previousMonthIndex = 3,  // April (zero-indexed)
    day = 29,
    hour = 16,

    // API Endpoint
    url = "https://data.corva.ai/api/v1/data/corva/metrics/aggregate/",

    // Build query parameters with hardcoded values
    query = [
        match = "{
            ""company_id"": " & Text.From(companyId) & ",
            ""data.key"": ""weight_to_weight_average"",
            ""data.type"": ""last24h"",
            ""data.value"": {""$gt"": 0},
            ""data.year"": " & Text.From(year) & ",
            ""data.month"": " & Text.From(previousMonthIndex) & ",
            ""data.day"": " & Text.From(day) & ",
            ""data.hour"": " & Text.From(hour) & "
        }",
        limit = "10000",
        sort = "{""timestamp"": -1}",
        project = "{
            ""_id"": 0,
            ""data.asset_id"": 1,
            ""data.key"": 1,
            ""data.type"": 1,
            ""data.value"": 1
        }"
    ],

    // Execute API call
    source = Json.Document(
        Web.Contents(
            url,
            [
                Headers = [
                    #"Authorization" = "API YOUR_API_KEY_HERE"
                ],
                Query = query
            ]
        )
    ),

    // Output
    results = source
in
    results

5. Get Weight to Weight by BHA for the Last 24 hrs​

Objective: Retrieve the "Weight to Weight" average data for each Bottom Hole Assembly (BHA) during the last 24 hours.

  • Dataset: corva#metrics

  • Query:

https://data.corva.ai/api/v1/data/corva/metrics/aggregate/?match={"company_id":{company_id}, "data.key":"weight_to_weight_average","data.type":"bha","data.value": {"$gt": 0},"timestamp": {"$gte": {current_unix_timestamp_minus_86400_seconds}}}&limit=10000&sort={"timestamp": -1}&project={"_id": 0, "data.asset_id": 1, "data.key": 1, "data.type": 1, "data.value": 1, "data.bha_id": 1, "data.well_sections": 1}

  • Query Requirements:

  • company_id: Your company's unique identifier.

  • current_unix_timestamp_minus_86400_seconds: The Unix timestamp representing 24 hours ago from the current time, used to fetch data for the last 24 hours.

  • Desired Data:

    • data.weight_to_weight_average: The average "Weight to Weight" data for each Bottom Hole Assembly (BHA), which can be averaged in Power BI.

    • data.bha_id: The BHA identifier to distinguish between different assemblies.

    • data.well_sections: Information on the well sections where the "Weight to Weight" data was collected.

Power BI Query Implementation for Weight to Weight by BHA for the Last 24 hrs​

In Power BI, the script should be adjusted to query for the "Weight to Weight" data by BHA based on the above query and generate visualizations. Here’s the process:

  1. Modify Query Parameters:

    • Replace {company_id} and {current_unix_timestamp_minus_86400_seconds} with the appropriate dynamic values in Power BI to calculate and fetch the correct data.

  2. Use the "Json.Document" function:

    • After setting up the correct API query, use the Json.Document function in Power BI to retrieve the data.

  3. Process the Data:

    • In Power BI, aggregate the data.value field (Weight to Weight average) over the last 24 hours, grouping by BHA or asset as needed, to visualize the data.

Power BI Script Example for Weight to Weight by BHA for the Last 24 hrs​

let
    // Step 1: Company ID and Unix timestamp (24 hours ago)
    companyId = your_company_id_here,
    currentTime = DateTimeZone.UtcNow(),
    unixNow = Duration.TotalSeconds(currentTime - DateTimeZone.From(#datetime(1970,1,1,0,0,0))),
    unix24hrsAgo = Number.RoundDown(unixNow - 86400),  // subtract 24 hours

    // Step 2: Corva API URL
    url = "https://data.corva.ai/api/v1/data/corva/metrics/aggregate/",

    // Step 3: API query parameters
    query = [
        match = "{
            ""company_id"": " & Text.From(companyId) & ",
            ""data.key"": ""weight_to_weight_average"",
            ""data.type"": ""bha"",
            ""data.value"": {""$gt"": 0},
            ""timestamp"": {""$gte"": " & Text.From(unix24hrsAgo) & "}
        }",
        limit = "10000",
        sort = "{""timestamp"": -1}",
        project = "{
            ""_id"": 0,
            ""data.asset_id"": 1,
            ""data.key"": 1,
            ""data.type"": 1,
            ""data.value"": 1,
            ""data.bha_id"": 1,
            ""data.well_sections"": 1
        }"
    ],

    // Step 4: API call with Authorization
    source = Json.Document(
        Web.Contents(
            url,
            [
                Headers = [
                    #"Authorization" = "API YOUR_API_KEY_HERE"
                ],
                Query = query
            ]
        )
    ),

    // Step 5: Return API result
    results = source
in
    results

6. Get Weight to Weight by Hole Section for the Last 24 hrs​

Objective: Retrieve the "Weight to Weight" average data for each hole section during the last 24 hours.

  • Dataset: corva#metrics

  • Query:

https://data.corva.ai/api/v1/data/corva/metrics/aggregate/?match={"company_id":{company_id}, "data.key":"weight_to_weight_average","data.type":"well_section","data.value": {"$gt": 0},"timestamp": {"$gte": {current_unix_timestamp_minus_86400_seconds}}}&limit=10000&sort={"timestamp": -1}&project={"_id": 0, "data.asset_id": 1, "data.key": 1, "data.type": 1, "data.value": 1, "data.well_section": 1}

  • Query Requirements:

  • company_id: Your company's unique identifier.

  • current_unix_timestamp_minus_86400_seconds: The Unix timestamp representing 24 hours ago from the current time, used to fetch data for the last 24 hours.

  • Desired Data:

    • data.weight_to_weight_average: The average "Weight to Weight" data for each hole section, which can be averaged in Power BI.

    • data.well_section: The well section identifier to distinguish between different sections within the same well.

Power BI Query Implementation for Weight to Weight by Hole Section for the Last 24 hrs​

In Power BI, the script should be adjusted to query for the "Weight to Weight" data by hole section based on the above query and generate visualizations. Here’s the process:

  1. Modify Query Parameters:

    • Replace {company_id} and {current_unix_timestamp_minus_86400_seconds} with the appropriate dynamic values in Power BI to calculate and fetch the correct data.

  2. Use the "Json.Document" function:

    • After setting up the correct API query, use the Json.Document function in Power BI to retrieve the data.

  3. Process the Data:

    • In Power BI, aggregate the data.value field (Weight to Weight average) over the last 24 hours, grouping by hole section or asset as needed, to visualize the data.

Power BI Script Example for Weight to Weight by Hole Section for the Last 24 hrs​

let
    // Step 1: Static company ID and timestamp logic
    companyId = your_company_id_here,
    currentTime = DateTimeZone.UtcNow(),
    unixNow = Duration.TotalSeconds(currentTime - DateTimeZone.From(#datetime(1970,1,1,0,0,0))),
    unix24hrsAgo = Number.RoundDown(unixNow - 86400),  // 24 hours ago

    // Step 2: Corva API URL
    url = "https://data.corva.ai/api/v1/data/corva/metrics/aggregate/",

    // Step 3: API query parameters
    query = [
        match = "{
            ""company_id"": " & Text.From(companyId) & ",
            ""data.key"": ""weight_to_weight_average"",
            ""data.type"": ""well_section"",
            ""data.value"": {""$gt"": 0},
            ""timestamp"": {""$gte"": " & Text.From(unix24hrsAgo) & "}
        }",
        limit = "10000",
        sort = "{""timestamp"": -1}",
        project = "{
            ""_id"": 0,
            ""data.asset_id"": 1,
            ""data.key"": 1,
            ""data.type"": 1,
            ""data.value"": 1,
            ""data.well_section"": 1
        }"
    ],

    // Step 4: Web request with API Key
    source = Json.Document(
        Web.Contents(
            url,
            [
                Headers = [
                    #"Authorization" = "API YOUR_API_KEY_HERE"
                ],
                Query = query
            ]
        )
    ),

    // Step 5: Return data
    results = source
in
    results

7. Get Tripping Speed Last 24 hrs for the Last 24 hrs​

Objective: Retrieve the "Tripping Speed" data for the last 24 hours.

  • Dataset: corva#metrics

  • Query:

https://data.corva.ai/api/v1/data/corva/metrics/aggregate/?match={"company_id":{company_id}, "data.key":"tripping_speed","data.type":"last24h","data.value": {"$gt": 0},"data.year": {current_year}, "data.month": {previous_month_index}, "data.day": {current_date}, "data.hour": {current_hour}}&limit=10000&sort={"timestamp": -1}&project={"_id": 0, "data.asset_id": 1, "data.key": 1, "data.type": 1, "data.value": 1}

  • Query Requirements:

  • company_id: Your company's unique identifier.

  • current_year: The current year for the query.

  • previous_month_index: The index of the previous month, used to fetch the correct data range. Months are zero-indexed, meaning:

    • January \= 0

    • February \= 1

    • March \= 2

    • April \= 3

    • May \= 4

    • June \= 5

    • and so on.

  • For example:

    • If the current month is May (calendar month 5), then previous_month_index will be 4 (for April).

    • If the current month is July (calendar month 7), then previous_month_index will be 6 (for June).

  • current_date: The current day for the query range.

  • current_hour: The current hour for querying the last 24 hours of data.

  • Desired Data:

    • data.tripping_speed: The tripping speed data for the last 24 hours, which can be averaged in Power BI.

    • data.asset_id: The asset ID associated with the retrieved tripping speed data.

Power BI Query Implementation for Tripping Speed for the Last 24 hrs​

In Power BI, the script should be adjusted to query for the "Tripping Speed" data based on the above query and use it for visualizations. Here’s the process:

  1. Modify Query Parameters:

    • Replace {company_id}, {current_year}, {previous_month_index}, {current_date}, and {current_hour} with appropriate dynamic values in Power BI to calculate and fetch the correct data.

  2. Use the "Json.Document" function:

    • After setting up the correct API query, use the Json.Document function in Power BI to retrieve the data.

  3. Process the Data:

    • In Power BI, aggregate the data.value field (Tripping Speed) over the last 24 hours for each asset.

Power BI Script Example for Tripping Speed for the Last 24 hrs​

let
    // Step 1: Calculate Unix timestamp 24 hours ago
    companyId = your_company_id_here,
    currentTime = DateTimeZone.UtcNow(),
    unixNow = Duration.TotalSeconds(currentTime - DateTimeZone.From(#datetime(1970,1,1,0,0,0))),
    unix24hrsAgo = Number.RoundDown(unixNow - 86400),

    // Step 2: API endpoint
    url = "https://data.corva.ai/api/v1/data/corva/metrics/aggregate/",

    // Step 3: Build timestamp-based query
    query = [
        match = "{
            ""company_id"": " & Text.From(companyId) & ",
            ""data.key"": ""tripping_speed"",
            ""data.value"": {""$gt"": 0},
            ""timestamp"": {""$gte"": " & Text.From(unix24hrsAgo) & "}
        }",
        limit = "10000",
        sort = "{""timestamp"": -1}",
        project = "{
            ""_id"": 0,
            ""data.asset_id"": 1,
            ""data.key"": 1,
            ""data.type"": 1,
            ""data.value"": 1
        }"
    ],

    // Step 4: Execute API request
    source = Json.Document(
        Web.Contents(
            url,
            [
                Headers = [
                    #"Authorization" = "API YOUR_API_KEY_HERE"
                ],
                Query = query
            ]
        )
    ),

    // Step 5: Output results
    results = source
in
    results

8. Get Tripping Speed by BHA for the Last 24 hrs​

Objective: Retrieve the "Tripping Speed" data for each Bottom Hole Assembly (BHA) during the last 24 hours.

  • Dataset: corva#metrics

  • Query:

https://data.corva.ai/api/v1/data/corva/metrics/aggregate/?match={"company_id":{company_id}, "data.key":"tripping_speed","data.type":"bha","data.value": {"$gt": 0},"timestamp": {"$gte": {current_unix_timestamp_minus_86400_seconds}}}&limit=10000&sort={"timestamp": -1}&project={"_id": 0, "data.asset_id": 1, "data.key": 1, "data.type": 1, "data.value": 1, "data.bha_id": 1, "data.well_sections": 1}

  • Query Requirements:

  • company_id: Your company's unique identifier.

  • current_unix_timestamp_minus_86400_seconds: The Unix timestamp representing 24 hours ago from the current time, used to fetch data for the last 24 hours.

  • Desired Data:

    • data.tripping_speed: The tripping speed data for the last 24 hours for each BHA, which can be averaged in Power BI.

    • data.bha_id: The BHA identifier to distinguish between different assemblies.

    • data.well_sections: Information on the well sections where the tripping speed data was collected.

Power BI Query Implementation for Tripping Speed by BHA for the Last 24 hrs​

In Power BI, the script should be adjusted to query for the "Tripping Speed" data by BHA based on the above query and generate visualizations. Here’s the process:

  1. Modify Query Parameters:

    • Replace {company_id} and {current_unix_timestamp_minus_86400_seconds} with the appropriate dynamic values in Power BI to calculate and fetch the correct data.

  2. Use the "Json.Document" function:

    • After setting up the correct API query, use the Json.Document function in Power BI to retrieve the data.

  3. Process the Data:

    • In Power BI, aggregate the data.value field (Tripping Speed) over the last 24 hours for each BHA.

Power BI Script Example for Tripping Speed by BHA for the Last 24 hrs​

let
    // Step 1: Company ID and Unix timestamp (24 hours ago)
    companyId = your_company_id_here,
    currentTime = DateTimeZone.UtcNow(),
    unixNow = Duration.TotalSeconds(currentTime - DateTimeZone.From(#datetime(1970,1,1,0,0,0))),
    unix24hrsAgo = Number.RoundDown(unixNow - 86400),  // subtract 24 hours

    // Step 2: Corva API URL
    url = "https://data.corva.ai/api/v1/data/corva/metrics/aggregate/",

    // Step 3: API query parameters
    query = [
        match = "{
            ""company_id"": " & Text.From(companyId) & ",
            ""data.key"": ""tripping_speed"",
            ""data.type"": ""bha"",
            ""data.value"": {""$gt"": 0},
            ""timestamp"": {""$gte"": " & Text.From(unix24hrsAgo) & "}
        }",
        limit = "10000",
        sort = "{""timestamp"": -1}",
        project = "{
            ""_id"": 0,
            ""data.asset_id"": 1,
            ""data.key"": 1,
            ""data.type"": 1,
            ""data.value"": 1,
            ""data.bha_id"": 1,
            ""data.well_sections"": 1
        }"
    ],

    // Step 4: API call
    source = Json.Document(
        Web.Contents(
            url,
            [
                Headers = [ #"Authorization" = "API YOUR_API_KEY_HERE" ],
                Query = query
            ]
        )
    ),

    // Step 5: Output
    results = source
in
    results


9. Get Tripping Speed by Hole Section for the Last 24 hrs​

Objective: Retrieve the "Tripping Speed" data for each hole section during the last 24 hours.

  • Dataset: corva#metrics

  • Query:

https://data.corva.ai/api/v1/data/corva/metrics/aggregate/?match={"company_id":{company_id}, "data.key":"tripping_speed","data.type":"well_section","data.value": {"$gt": 0},"timestamp": {"$gte": {current_unix_timestamp_minus_86400_seconds}}}&limit=10000&sort={"timestamp": -1}&project={"_id": 0, "data.asset_id": 1, "data.key": 1, "data.type": 1, "data.value": 1, "data.well_section": 1}

  • Query Requirements:

  • company_id: Your company's unique identifier.

  • current_unix_timestamp_minus_86400_seconds: The Unix timestamp representing 24 hours ago from the current time, used to fetch data for the last 24 hours.

  • Desired Data:

    • data.tripping_speed: The tripping speed data for the last 24 hours for each hole section, which can be averaged in Power BI.

    • data.well_section: The well section identifier to distinguish between different sections within the same well.

Power BI Query Implementation for Tripping Speed by Hole Section for the Last 24 hrs​

In Power BI, the script should be adjusted to query for the "Tripping Speed" data by hole section based on the above query and generate visualizations. Here’s the process:

  1. Modify Query Parameters:

    • Replace {company_id} and {current_unix_timestamp_minus_86400_seconds} with the appropriate dynamic values in Power BI to calculate and fetch the correct data.

  2. Use the "Json.Document" function:

    • After setting up the correct API query, use the Json.Document function in Power BI to retrieve the data.

  3. Process the Data:

    • In Power BI, aggregate the data.value field (Tripping Speed) over the last 24 hours for each hole section.

Power BI Script Example for Tripping Speed by Hole Section for the Last 24 hrs​

let
    // Step 1: Static company ID and timestamp logic
    companyId = your_company_id_here,
    currentTime = DateTimeZone.UtcNow(),
    unixNow = Duration.TotalSeconds(currentTime - DateTimeZone.From(#datetime(1970,1,1,0,0,0))),
    unix24hrsAgo = Number.RoundDown(unixNow - 86400),  // 24 hours ago

    // Step 2: API URL
    url = "https://data.corva.ai/api/v1/data/corva/metrics/aggregate/",

    // Step 3: Query structure
    query = [
        match = "{
            ""company_id"": " & Text.From(companyId) & ",
            ""data.key"": ""tripping_speed"",
            ""data.type"": ""well_section"",
            ""data.value"": {""$gt"": 0},
            ""timestamp"": {""$gte"": " & Text.From(unix24hrsAgo) & "}
        }",
        limit = "10000",
        sort = "{""timestamp"": -1}",
        project = "{
            ""_id"": 0,
            ""data.asset_id"": 1,
            ""data.key"": 1,
            ""data.type"": 1,
            ""data.value"": 1,
            ""data.well_section"": 1
        }"
    ],

    // Step 4: Execute API call
    source = Json.Document(
        Web.Contents(
            url,
            [
                Headers = [ #"Authorization" = "API YOUR_API_KEY_HERE" ],
                Query = query
            ]
        )
    ),

    // Step 5: Output results
    results = source
in
    results

10. Get Cased Hole Trip Out Speed for the Last 24 hrs​

Objective: Retrieve the "Cased Hole Trip Out Speed" data for the last 24 hours.

  • Dataset: corva#metrics

  • Query:

https://data.corva.ai/api/v1/data/corva/metrics/aggregate/?match={"company_id":{company_id}, "data.key":"tripping_out_speed_cased","data.type":"last24h","data.value": {"$gt": 0},"data.year": {current_year}, "data.month": {previous_month_index}, "data.day": {current_date}, "data.hour": {current_hour}}&limit=10000&sort={"timestamp": -1}&project={"_id": 0, "data.asset_id": 1, "data.key": 1, "data.type": 1, "data.value": 1}

  • Query Requirements:

  • company_id: Your company's unique identifier.

  • current_year: The current year for the query.

  • previous_month_index: The index of the previous month, used to fetch the correct data range. For example:

    • If the current month is May (calendar month 5), then previous_month_index will be 4 (for April).

    • If the current month is July (calendar month 7), then previous_month_index will be 6 (for June).

  • current_date: The current day for the query range.

  • current_hour: The current hour for querying the last 24 hours of data.

  • Desired Data:

    • data.tripping_out_speed_cased: The cased hole tripping speed data for the last 24 hours, which can be averaged in Power BI.

    • data.asset_id: The asset ID associated with the retrieved cased hole tripping speed data.

Power BI Query Implementation for Cased Hole Trip Out Speed for the Last 24 hrs​

In Power BI, the script should be adjusted to query for the "Cased Hole Trip Out Speed" data based on the above query and use it for visualizations. Here’s the process:

  1. Modify Query Parameters:

    • Replace {company_id}, {current_year}, {previous_month_index}, {current_date}, and {current_hour} with appropriate dynamic values in Power BI to calculate and fetch the correct data.

  2. Use the "Json.Document" function:

    • After setting up the correct API query, use the Json.Document function in Power BI to retrieve the data.

  3. Process the Data:

    • In Power BI, aggregate the data.value field (Cased Hole Trip Out Speed) over the last 24 hours for each asset.

Power BI Script Example for Cased Hole Trip Out Speed for the Last 24 hrs​

let
    // Step 1: Parameters
    companyId = your_company_id_here,
    currentTime = DateTimeZone.UtcNow(),
    unixEpoch = DateTimeZone.From(#datetime(1970, 1, 1, 0, 0, 0)),
    unixNow = Duration.TotalSeconds(currentTime - unixEpoch),
    unix24hrsAgo = Number.RoundDown(unixNow - 86400),

    // Step 2: API URL
    url = "https://data.corva.ai/api/v1/data/corva/metrics/aggregate/",

    // Step 3: API Query
    query = [
        match = "{
            ""company_id"": " & Text.From(companyId) & ",
            ""data.key"": ""tripping_out_speed_cased"",
            ""data.type"": ""last24h"",
            ""data.value"": {""$gt"": 0},
            ""timestamp"": {""$gte"": " & Text.From(unix24hrsAgo) & "}
        }",
        limit = "10000",
        sort = "{""timestamp"": -1}",
        project = "{
            ""_id"": 0,
            ""data.asset_id"": 1,
            ""data.key"": 1,
            ""data.type"": 1,
            ""data.value"": 1
        }"
    ],

    // Step 4: API Call
    source = Json.Document(
        Web.Contents(
            url,
            [
                Headers = [ #"Authorization" = "API YOUR_API_KEY_HERE" ],
                Query = query
            ]
        )
    ),

    // Step 5: Output
    results = source
in
    results

11. Get Open Hole Trip Out Speed for the Last 24 hrs​

Objective: Retrieve the "Open Hole Trip Out Speed" data for the last 24 hours.

  • Dataset: corva#metrics

  • Query:

https://data.corva.ai/api/v1/data/corva/metrics/aggregate/?match={"company_id":{company_id}, "data.key":"tripping_out_speed_open","data.type":"last24h","data.value": {"$gt": 0},"data.year": {current_year}, "data.month": {previous_month_index}, "data.day": {current_date}, "data.hour": {current_hour}}&limit=10000&sort={"timestamp": -1}&project={"_id": 0, "data.asset_id": 1, "data.key": 1, "data.type": 1, "data.value": 1}

  • Query Requirements:

  • company_id: Your company's unique identifier.

  • current_year: The current year for the query.

  • previous_month_index: The index of the previous month, used to fetch the correct data range. For example:

    • If the current month is May (calendar month 5), then previous_month_index will be 4 (for April).

    • If the current month is July (calendar month 7), then previous_month_index will be 6 (for June).

  • current_date: The current day for the query range.

  • current_hour: The current hour for querying the last 24 hours of data.

  • Desired Data:

    • data.tripping_out_speed_open: The open hole trip out speed data for the last 24 hours, which can be averaged in Power BI.

    • data.asset_id: The asset ID associated with the retrieved open hole trip out speed data.

Power BI Query Implementation for Open Hole Trip Out Speed for the Last 24 hrs​

In Power BI, the script should be adjusted to query for the "Open Hole Trip Out Speed" data based on the above query and use it for visualizations. Here’s the process:

  1. Modify Query Parameters:

    • Replace {company_id}, {current_year}, {previous_month_index}, {current_date}, and {current_hour} with appropriate dynamic values in Power BI to calculate and fetch the correct data.

  2. Use the "Json.Document" function:

    • After setting up the correct API query, use the Json.Document function in Power BI to retrieve the data.

  3. Process the Data:

    • In Power BI, aggregate the data.value field (Open Hole Trip Out Speed) over the last 24 hours for each asset.

Power BI Script Example for Open Hole Trip Out Speed for the Last 24 hrs​

let
    // Step 1: Parameters and Timestamp Conversion
    companyId = your_company_id_here,
    currentTime = DateTimeZone.UtcNow(),
    unixEpoch = DateTimeZone.From(#datetime(1970, 1, 1, 0, 0, 0)),
    unixNow = Duration.TotalSeconds(currentTime - unixEpoch),
    unix24hrsAgo = Number.RoundDown(unixNow - 86400),  // 24 hours ago

    // Step 2: API URL
    url = "https://data.corva.ai/api/v1/data/corva/metrics/aggregate/",

    // Step 3: API Query
    query = [
        match = "{
            ""company_id"": " & Text.From(companyId) & ",
            ""data.key"": ""tripping_out_speed_open"",
            ""data.type"": ""last24h"",
            ""data.value"": {""$gt"": 0},
            ""timestamp"": {""$gte"": " & Text.From(unix24hrsAgo) & "}
        }",
        limit = "10000",
        sort = "{""timestamp"": -1}",
        project = "{
            ""_id"": 0,
            ""data.asset_id"": 1,
            ""data.key"": 1,
            ""data.type"": 1,
            ""data.value"": 1
        }"
    ],

    // Step 4: Execute API Request
    source = Json.Document(
        Web.Contents(
            url,
            [
                Headers = [ #"Authorization" = "API YOUR_API_KEY_HERE" ],
                Query = query
            ]
        )
    ),

    // Step 5: Return the Data
    results = source
in
    results

12. Get Cased Hole Trip In Speed for the Last 24 hrs​

Objective: Retrieve the "Cased Hole Trip In Speed" data for the last 24 hours.

  • Dataset: corva#metrics

  • Query:

https://data.corva.ai/api/v1/data/corva/metrics/aggregate/?match={"company_id":{company_id}, "data.key":"tripping_in_speed_cased","data.type":"last24h","data.value": {"$gt": 0},"data.year": {current_year}, "data.month": {previous_month_index}, "data.day": {current_date}, "data.hour": {current_hour}}&limit=10000&sort={"timestamp": -1}&project={"_id": 0, "data.asset_id": 1, "data.key": 1, "data.type": 1, "data.value": 1}

  • Query Requirements:

  • company_id: Your company's unique identifier.

  • current_year: The current year for the query.

  • previous_month_index: The index of the previous month, used to fetch the correct data range. For example:

    • If the current month is May (calendar month 5), then previous_month_index will be 4 (for April).

    • If the current month is July (calendar month 7), then previous_month_index will be 6 (for June).

  • current_date: The current day for the query range.

  • current_hour: The current hour for querying the last 24 hours of data.

  • Desired Data:

    • data.tripping_in_speed_cased: The cased hole trip in speed data for the last 24 hours, which can be averaged in Power BI.

    • data.asset_id: The asset ID associated with the retrieved cased hole trip in speed data.

Power BI Query Implementation for Cased Hole Trip In Speed for the Last 24 hrs​

In Power BI, the script should be adjusted to query for the "Cased Hole Trip In Speed" data based on the above query and use it for visualizations. Here’s the process:

  1. Modify Query Parameters:

    • Replace {company_id}, {current_year}, {current_month}, {current_date}, and {current_hour} with appropriate dynamic values in Power BI to calculate and fetch the correct data.

  2. Use the "Json.Document" function:

    • After setting up the correct API query, use the Json.Document function in Power BI to retrieve the data.

  3. Process the Data:

    • In Power BI, aggregate the data.value field (Cased Hole Trip In Speed) over the last 24 hours for each asset.

Power BI Script Example for Cased Hole Trip In Speed for the Last 24 hrss​

let
    // Step 1: Define company ID and compute time range
    companyId = your_company_id_here,
    currentTime = DateTimeZone.UtcNow(),
    unixEpoch = DateTimeZone.From(#datetime(1970, 1, 1, 0, 0, 0)),
    unixNow = Duration.TotalSeconds(currentTime - unixEpoch),
    unix24hrsAgo = Number.RoundDown(unixNow - 86400),  // 24 hours ago

    // Step 2: Define Corva API endpoint
    url = "https://data.corva.ai/api/v1/data/corva/metrics/aggregate/",

    // Step 3: Define query parameters
    query = [
        match = "{
            ""company_id"": " & Text.From(companyId) & ",
            ""data.key"": ""tripping_in_speed_cased"",
            ""data.type"": ""last24h"",
            ""data.value"": {""$gt"": 0},
            ""timestamp"": {""$gte"": " & Text.From(unix24hrsAgo) & "}
        }",
        limit = "10000",
        sort = "{""timestamp"": -1}",
        project = "{
            ""_id"": 0,
            ""data.asset_id"": 1,
            ""data.key"": 1,
            ""data.type"": 1,
            ""data.value"": 1
        }"
    ],

    // Step 4: Fetch data
    source = Json.Document(
        Web.Contents(
            url,
            [
                Headers = [ #"Authorization" = "API YOUR_API_KEY_HERE" ],
                Query = query
            ]
        )
    ),

    // Step 5: Return results
    results = source
in
    results

13. Get Open Hole Trip In Speed for the Last 24 hrs​

Objective: Retrieve the "Open Hole Trip In Speed" data for the last 24 hours.

  • Dataset: corva#metrics

  • Query:

https://data.corva.ai/api/v1/data/corva/metrics/aggregate/?match={"company_id":{company_id}, "data.key":"tripping_in_speed_open","data.type":"last24h","data.value": {"$gt": 0},"data.year": {current_year}, "data.month": {previous_month_index}, "data.day": {current_date}, "data.hour": {current_hour}}&limit=10000&sort={"timestamp": -1}&project={"_id": 0, "data.asset_id": 1, "data.key": 1, "data.type": 1, "data.value": 1}

  • Query Requirements:

  • company_id: Your company's unique identifier.

  • current_year: The current year for the query.

  • previous_month_index: The index of the previous month, used to fetch the correct data range. For example:

    • If the current month is May (calendar month 5), then previous_month_index will be 4 (for April).

    • If the current month is July (calendar month 7), then previous_month_index will be 6 (for June).

  • current_date: The current day for the query range.

  • current_hour: The current hour for querying the last 24 hours of data.

  • Desired Data:

    • data.tripping_in_speed_open: The open hole trip in speed data for the last 24 hours, which can be averaged in Power BI.

    • data.asset_id: The asset ID associated with the retrieved open hole trip in speed data.

Power BI Query Implementation for Open Hole Trip In Speed for the Last 24 hrs​

In Power BI, the script should be adjusted to query for the "Open Hole Trip In Speed" data based on the above query and use it for visualizations. Here’s the process:

  1. Modify Query Parameters:

    • Replace {company_id}, {current_year}, {previous_month_index}, {current_date}, and {current_hour} with appropriate dynamic values in Power BI to calculate and fetch the correct data.

  2. Use the "Json.Document" function:

    • After setting up the correct API query, use the Json.Document function in Power BI to retrieve the data.

  3. Process the Data:

    • In Power BI, aggregate the data.value field (Open Hole Trip Out Speed) over the last 24 hours for each asset.

Power BI Script Example for Open Hole Trip Out Speed for the Last 24 hrs​

let
    // Step 1: Define company ID and compute time range
    companyId = your_company_id_here,
    currentTime = DateTimeZone.UtcNow(),
    unixEpoch = DateTimeZone.From(#datetime(1970, 1, 1, 0, 0, 0)),
    unixNow = Duration.TotalSeconds(currentTime - unixEpoch),
    unix24hrsAgo = Number.RoundDown(unixNow - 86400),  // 24 hours ago

    // Step 2: Define Corva API endpoint
    url = "https://data.corva.ai/api/v1/data/corva/metrics/aggregate/",

    // Step 3: Define query parameters
    query = [
        match = "{
            ""company_id"": " & Text.From(companyId) & ",
            ""data.key"": ""tripping_in_speed_cased"",
            ""data.type"": ""last24h"",
            ""data.value"": {""$gt"": 0},
            ""timestamp"": {""$gte"": " & Text.From(unix24hrsAgo) & "}
        }",
        limit = "10000",
        sort = "{""timestamp"": -1}",
        project = "{
            ""_id"": 0,
            ""data.asset_id"": 1,
            ""data.key"": 1,
            ""data.type"": 1,
            ""data.value"": 1
        }"
    ],

    // Step 4: Fetch data
    source = Json.Document(
        Web.Contents(
            url,
            [
                Headers = [ #"Authorization" = "API YOUR_API_KEY_HERE" ],
                Query = query
            ]
        )
    ),

    // Step 5: Return results
    results = source
in
    results

If you have additional questions please submit a Support Request here.