Filtering the Dataset for latest entity state

To filter your dataset so it only includes the latest state of each entity, start by using the API to load the data source as usual. Replace the variables with your specific details to connect to your dataset, as shown below:

let
    // Replace these variables with your own information
    dataset_name = "Observations",
    api_key = "your_api_key",
    organization_id = "your_organization_id",
    
    RetryRequest = (retries as number) =>
    let
        apiResponse = try Json.Document(Web.Contents("https://developer.finalcad.cloud/api/organizations/" & organization_id & "/data/config/dataseturl?name=" & dataset_name, 
            [Headers=[#"X-API-Key"= api_key, Authorization="token " & api_key]])),
        Source = if apiResponse[HasError] and retries > 0 then
            Function.InvokeAfter(() => @RetryRequest(retries - 1), #duration(0, 0, 0, 2))
        else Parquet.Document(Binary.Buffer(Web.Contents(apiResponse[Value][url])))
    in
        Source,

    Source = RetryRequest(3),

    // Sort the dataset by updated_at in descending order
    SortedRows = Table.Buffer(Table.Sort(Source,{{"updated_at", Order.Descending}})),

    // Filter the dataset to retain only the latest entry for each entity id
    DistinctRows = Table.Distinct(SortedRows, {"id"})

in
    DistinctRows

Explanation of the Filtering Steps

1. Sorting by updated_at: After the data is loaded, the dataset is sorted in descending order by the updated_at column. This ensures that the latest version of each entity appears at the top.

2. Keeping Only the Latest State of Each Entity: Using Table.Distinct on the sorted data keeps only the first occurrence of each unique id, which corresponds to the most recent state for that entity.

This approach results in a dataset that includes only the latest state for each entity, making it easier to analyze the current data without historical entries.