Help with Datamart/Dataflow: "Column1 not found"

[XadrB]

Hello everyone,

We are facing two errors when using Incremental Refresh in a Datamart (the same issues occurs in a Dataflow, so I'll refrain from mentioning both).

let
RangeStartText = DateTime.ToText(RangeStart, [Format="yyyy-MM-dd"]),
RangeEndText = DateTime.ToText(RangeEnd, [Format="yyyy-MM-dd"]),


Kilde = Json.Document(Web.Contents("https://api-link-redacted//",
[
Query = [timestampGt = RangeStartText, timestampLt = RangeEndText]
]
)),

Navigering = Kilde[orders],

1) We are getting data from an API. Incremental Refresh in the datamart inputs the dates itself, which causes an error. Our API has no data from the date 1.1.1 to 31.12.9999. In the desktop version, you're able to select the start range yourself. This leaves me with no data in the preview-window, which does not allow me to save. 

I change the parameters by adding the following:

let
    // Sjekk og juster RangeStart basert på betingelsen
    AdjustedRangeStart = if DateTime.Date(RangeStart) < #date(2023, 9, 1) then #date(2023, 9, 1) else DateTime.Date(RangeStart),
    AdjustedRangeEnd = if DateTime.Date(RangeEnd) > #date(2026, 1, 1) then #date(2026, 1, 1) else DateTime.Date(RangeEnd),

    // Konverter justerte datoer til tekst i ønsket format (yyyy-MM-dd)
    RangeStartText = Date.ToText(AdjustedRangeStart, "yyyy-MM-dd"),
    RangeEndText = Date.ToText(AdjustedRangeEnd, "yyyy-MM-dd"),
    
    // Hent data med de oppdaterte spørringsparametrene
    Kilde = Json.Document(Web.Contents("https://api-link-redacted//",
        [
            Query = [timestampGt = RangeStartText, timestampLt = RangeEndText]
        ]
    )),

    Navigering = Kilde[orders],

This works in the preview window. 

2) Upon saving, I get the error that "Column1" was not found. This is the following code in which it refers to this column:

    #"Konvertert til tabell" = Table.FromList(Navigering, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Utvidet Column1" = Table.ExpandRecordColumn(#"Konvertert til tabell", "Column1", {"soid", "id", "customerId", "terminalId", "soguid", "receiptNum", "state", "opened", "closed", "closedByUserId", "name", "type", "refundOfSoguid", "refundComment", "refundUsername", "refundUserId", "refundApprovedUserId", "corrCount", "corrAmount", "discountAmount", "discountReason", "discountReasonId", "discountComment", "cdOpenCount", "customerClientGuid", "closedBusinessDate", "receiptType", "cancelReasonId", "cancelComment", "guestCountRegistered", "guestCountCalculated", "tableId", "revenueUnitId", "revenueUnitName", "orderSourceName", "webshopStoreName", "lines", "payments", "readyTime", "externalPaymentRef", "orderTypeName", "guestCountSubmitted", "deliveryType", "deliveryTime", "deliveryComment", "deliveryZoneName", "deliveryRecipientName", "deliveryRecipientPhone", "deliveryAddressLine1", "deliveryAddressLine2", "deliveryAddressPostalCode", "deliveryAddressCity", "deliveryAddressRemarks", "deliveryAddressCoordinates"}, {"soid", "id", "customerId", "terminalId", "soguid", "receiptNum", "state", "opened", "closed", "closedByUserId", "name", "type", "refundOfSoguid", "refundComment", "refundUsername", "refundUserId", "refundApprovedUserId", "corrCount", "corrAmount", "discountAmount", "discountReason", "discountReasonId", "discountComment", "cdOpenCount", "customerClientGuid", "closedBusinessDate", "receiptType", "cancelReasonId", "cancelComment", "guestCountRegistered", "guestCountCalculated", "tableId", "revenueUnitId", "revenueUnitName", "orderSourceName", "webshopStoreName", "lines", "payments", "readyTime", "externalPaymentRef", "orderTypeName", "guestCountSubmitted", "deliveryType", "deliveryTime", "deliveryComment", "deliveryZoneName", "deliveryRecipientName", "deliveryRecipientPhone", "deliveryAddressLine1", "deliveryAddressLine2", "deliveryAddressPostalCode", "deliveryAddressCity", "deliveryAddressRemarks", "deliveryAddressCoordinates"}),

Column1 does exist as far as I am concerned. This is what was auto-generated when I converted the json to a table.

I have tried everything at this point, and I'm yet to find a solution. Any help is appreciated!

Note: This is only with Incremental Refresh enabled. It does not occur in the desktop version of PBI.

Thanks in advance,

Jonathan

[lbendlin]

Column1 will not exist if the list is empty.

Note that RangeStart must be inclusive and RangeEnd must be exclusive.

 Incremental Refresh in the datamart inputs the dates itself, which causes an error. Our API has no data from the date 1.1.1 to 31.12.9999

Not sure where you get that date range from, but it would be catastrophic.

What are your incremental refresh partition rules?