Power BI incremental refresh

AllRows = UNION ( HistoricalRows, RecentRows )

LatestItems =
FILTER (
    Items,
    Items[lastUpdatedTime]
        = CALCULATE (
            MAX ( Items[lastUpdatedTime] ),
            ALLEXCEPT ( Items, Items[space], Items[externalId] )
        )
)

LatestItems =
FILTER (
    Items,
    Items[lastUpdatedTime]
        = CALCULATE (
            MAX ( Items[lastUpdatedTime] ),
            ALLEXCEPT ( Items, Items[space], Items[externalId] )
        )
        && Items[status] <> "deleted"
)

(dt as nullable datetime) as nullable number =>
if dt = null then null else
let
    // Define the Unix epoch start in UTC
    UnixEpochStart = #datetime(1970, 1, 1, 0, 0, 0),
    // Calculate the duration between the input datetime and Unix epoch start
    Delta = dt - UnixEpochStart,
    // Round to millisecond precision to avoid floating-point values such as
    // 1000.999999999 losing one millisecond when rounded down.
    TotalMilliseconds = Number.Round(Duration.TotalSeconds(Delta) * 1000)
in
    TotalMilliseconds

(dt as nullable datetime) as nullable text =>
if dt = null then null else
let
    // Mark the input as UTC without shifting the clock
    UTCDateTimeZone = DateTime.AddZone(dt, 0),
    // Use DateTimeZone.ToText with ISO 8601 format
    Result = DateTimeZone.ToText(UTCDateTimeZone, [Format="yyyy-MM-ddTHH:mm:ss.fffzzz", Culture="en-US"])
in
    Result

(dt as nullable datetime) as nullable datetimezone =>
if dt = null then null else
let
    // Mark the input as UTC without shifting the clock
    UTCDateTimeZone = DateTime.AddZone(dt, 0)
in
    UTCDateTimeZone

(InputTable as table, Columns as list) as table =>
    Table.SelectColumns(InputTable, Columns, MissingField.UseNull)

(InputTable as table) as text =>
let
    // Get the column names
    columnNames = Table.ColumnNames(InputTable),
    // Escape M escape sequences and quotes before wrapping each name in quotes
    quotedColumnNames = List.Transform(columnNames, each
        """" & Text.Replace(Text.Replace(_, "#", "#(#)(#)"), """", """""") & """"),
    // Combine them into a single text with comma separation
    combinedText = Text.Combine(quotedColumnNames, ", "),
    // Add curly braces around the combined text
    ColumnNamesAsText = "{ " & combinedText & " }"
in
    ColumnNamesAsText

let
    // Convert parameters to milliseconds since epoch
    RangeStartMs = ConvertDateTimeToMs(RangeStart),
    RangeEndMs = ConvertDateTimeToMs(RangeEnd),
    // Create the request body with timestamp filters
    Body = [
        sources = {
            [
                source = [
                    type = "view",
                    space = "cdf_cdm",
                    externalId = "CogniteActivity",
                    version = "v1"
                ]
            ]
        },
        filter = [
            range = [
                property = { "node", "lastUpdatedTime" },
                gte = RangeStartMs,
                lt = RangeEndMs
            ]
        ],
        limit = 1000
    ],
    // Convert the body to JSON text
    BodyText = Text.FromBinary(Json.FromValue(Body)),
    // Fetch data using PostCDF
    NodesTable = PostCDF("/models/instances/list", BodyText),
    // Remove null rows
    CleanedTable = Table.SelectRows(
        NodesTable,
        each List.NonNullCount(Record.ToList(_)) > 0
    ),
    // Define the expected schema
    Columns = {
        "instanceType",
        "version",
        "space",
        "externalId",
        "createdTime",
        "lastUpdatedTime",
        "properties"
    },
    // Enforce the schema to ensure a consistent table even if no data is returned
    FinalTable = EnforceSchema(CleanedTable, Columns),
    WithDates = Table.TransformColumns(FinalTable, {
        {"createdTime", ConvertMsToUtcDateTime, type nullable datetime},
        {"lastUpdatedTime", ConvertMsToUtcDateTime, type nullable datetime}
    }),
    Typed = Table.TransformColumnTypes(WithDates, {
        {"instanceType", type text}, {"version", Int64.Type}, {"space", type text},
        {"externalId", type text}
    }),
    WithNestedTypes = Table.TransformColumns(Typed, {{"properties",
        each if _ = null then null else Text.FromBinary(Json.FromValue(_)), type nullable text}}),
    Partition = Table.SelectRows(WithNestedTypes, each [lastUpdatedTime] >= RangeStart and [lastUpdatedTime] < RangeEnd)
in
    Partition

let
    // Convert parameters to ISO 8601 format
    StartIso = ConvertDateTimeToIso(RangeStart),
    EndIso = ConvertDateTimeToIso(RangeEnd),
    // Create the GraphQL query with timestamp filters
    QueryText = "query MyQuery(
        $cursor: String,
        $rangeStart: Timestamp,
        $rangeEnd: Timestamp
        ) {
        listCogniteActivity(
            first: 1000,
            filter: {
            and: [
                { lastUpdatedTime: { gte: $rangeStart } },
                { lastUpdatedTime: { lt: $rangeEnd } }
            ]
            },
            after: $cursor
        ) {
            items {
            space
            externalId
            name
            description
            startTime
            endTime
            createdTime
            lastUpdatedTime
            }
            pageInfo { hasNextPage endCursor }
        }
    }",
    // Define the variables for the GraphQL query
    QueryVariables = [
        rangeStart = StartIso,
        rangeEnd = EndIso
    ],
    // Convert the variables to JSON text
    QueryVariablesText = Text.FromBinary(Json.FromValue(QueryVariables)),
    // Fetch data using GraphQL
    NodesTable = GraphQL("cdf_cdm", "CogniteCore", "v1", QueryText, QueryVariablesText),
    // Define the expected schema
    Columns = {
        "space",
        "externalId",
        "name",
        "description",
        "startTime",
        "endTime",
        "createdTime",
        "lastUpdatedTime"
    },
    // Enforce the schema to ensure a consistent table even if no data is returned
    FinalTable = EnforceSchema(NodesTable, Columns),
    WithDates = Table.TransformColumns(FinalTable, List.Transform(
        {"startTime", "endTime", "createdTime", "lastUpdatedTime"}, each {_,
            (value) => if value = null then null else
                DateTimeZone.RemoveZone(DateTimeZone.SwitchZone(DateTimeZone.FromText(value), 0)),
            type nullable datetime})),
    Typed = Table.TransformColumnTypes(WithDates, {
        {"space", type text}, {"externalId", type text}, {"name", type text}, {"description", type text}
    }),
    Partition = Table.SelectRows(Typed, each [lastUpdatedTime] >= RangeStart and [lastUpdatedTime] < RangeEnd)
in
    Partition

let
    // Convert parameters to datetimezone format
    RangeStartUTC = ConvertDateTimeToDateTimeZone(RangeStart),
    RangeEndUTC = ConvertDateTimeToDateTimeZone(RangeEnd),
    // Access the data modeling OData service
    Source = ODataDataModel("cdf_cdm", "CogniteCore", "v1"),
    // Access the CogniteActivity table
    ActivitiesTable = Source{[Name="CogniteActivity",Signature="table"]}[Data],
    // Filter the table using RangeStart and RangeEnd parameters
    // This filter will be pushed down to the OData service
    ActivitiesWithRangeFilter = Table.SelectRows(
        ActivitiesTable,
        each [startTime] >= RangeStartUTC and [startTime] < RangeEndUTC
    )
in
    ActivitiesWithRangeFilter