Issue with Dataflow Gen2 with date variable

[RamonNooijen]

I have a question, I want to ingest data from API's where I have to call multiple API's based on issue ID's to get the history from an issue and this history data is pretty long. To make my question/issue clear I don't want to give to many details about how I get the data but my setup is as this:

I want to create a Dataflow Gen 2 that gets the data based on a watermark (date) value that I get from a SQL table (like for example yesterday 12/2/2025). so I created a scalar date value that I sould be able to use in my query to get only te new record.

So in the query where I want to setup my table on the last step I add a filter to get all values from the WM date or after like this:

Table.SelectRows(

IssuesTyped,

each [updated_at] >= WM

)

In my powerquery I do get the results so I see for this example all history of the issues that are updated yesterday and today.

But when I want to ingest this table into my datawarehouse I get a error:

Error Code: Mashup Exception Expression Error, Error Details: Couldn't refresh the entity because of an issue with the mashup document MashupException.Error: Expression.Error: Failed to insert a table., InnerException: We cannot apply operator < to types Table and Date., Underlying error: We cannot apply operator < to types Table and Date. Details: Reason = Expression.Error;ErrorCode = Lakehouse036;Message = We cannot apply operator < to types Table and Date.;Detail = [Operator = "<", Left = error "Microsoft.Mashup.Engine1.Runtime.ValueException: [Expression.Error] Value was not specified.#(cr)#(lf) at Microsoft.Mashup.Engine1.Language.ValueCreator.CreateValueForThrow(IThrowExpression throwExpr)#(cr)#(lf) at Microsoft.Mashup.Engine1.Language.ValueCreator.<>c__DisplayClass23_0.<CreateValueForRecord>b__0(Int32 index)#(cr)#(lf) at Microsoft.Mashup.Engine1.Runtime.RecordValue.DemandRecordValue.get_Item(Int32 index)#(cr)#(lf) at Microsoft.Data.Mashup.ProviderCommon.MashupResource.TryGetValue(Func`1 getValue, IValue& value, String& errorMessage)#(cr)#(lf)Record", Right = #date(2025, 3, 5)];Message.Format = We cannot apply operator #{0} to types #{1} and #{2}.;Message.Parameters = {"<", "Table", "Date"};ErrorCode = 10051;Microsoft.Data.Mashup.Error.Context = User

Does anyone have an idea how I could solve this issue? btw when I just hardcode the date for example I add a step in front: WM = #date(2025, 12, 3) then it works...

[v-pnaroju-msft]

Hi RamonNooijen,

We are following up to see if what we shared solved your issue. If you need more support, please reach out to the Microsoft Fabric community.

Thank you.

[v-pnaroju-msft]

Thankyou, @tayloramy and @Ugk161610 for your responses.

Hi RamonNooijen,

We appreciate your inquiry through the Microsoft Fabric Community Forum.

We would like to inquire whether have you got the chance to check the solutions provided by @tayloramy and @Ugk161610  to resolve the issue. We hope the information provided helps to clear the query. Should you have any further queries, kindly feel free to contact the Microsoft Fabric community.

Thank you.

[Ugk161610 · Accepted solution]

Hi @RamonNooijen ,

The error message is actually giving the clue:

We cannot apply operator < to types Table and Date

That means WM is not a single date value at runtime – it’s still a table (or query result) when the dataflow engine tries to fold the query and push it to the Lakehouse / DW.

When you hard-code WM = #date(2025, 12, 3) it works, because WM is clearly a scalar date.
When you load it from SQL, your WM step is returning a table (for example one row, one column), not the cell value itself.

You need one extra step to turn that table into a single date before using it in Table.SelectRows.

A common pattern looks like this in Power Query (M):

// Query that gets the watermark table from SQL
WMTable = Sql.Database("server", "db"){[Name="WatermarkTable"]}[Data],

// Take the first row and the Watermark column as a scalar value
WM = Date.From( WMTable{0}[WatermarkDate] ),

Then in your filter step:

FilteredRows =
Table.SelectRows(
IssuesTyped,
each [updated_at] >= WM
)

Key idea:

• WMTable = table returned from SQL

• WM = single Date value extracted from that table

Right now you’re comparing [updated_at] (a date) to WM (a table), which is why you get the “Table and Date” error only when it actually runs in the service.

Once WM is a true date value, your dynamic filter will work the same way as the hard-coded example.

– Gopi Krishna

[tayloramy]

Hi @RamonNooijen, 

It looks like your  WM is returning a table, not an individual value. so you're trying to compare a table with a date, and Power Query has no idea how to determine if a table is less than a date as those are two completely different datatypes. 

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.