Dataflow Gen2 Max Effective Date as Parameter

[gmoore7]

Hi,

I have a Dataflow Gen2 where I am first querying the Max Effective Date from a table and then trying to use that as a parameter in a query later on in the flow (see screenshot).

I am running a query (that only returns one row with one column) and getting the date value from that row/column using an aggregation. I am then referencing that in the next query as a "reference query" value.

The preview in the 2nd step actually works and I can see my SQL Server receiving the date parameter correctly... and the data preview even populates on the 2nd step with only the data I need, but I keep getting the following mashup error when I publish and run:

We cannot apply operator #{0} to types #{1} and #{2}., Underlying error: We cannot apply operator < to types Table and Date. Details: Reason = Expression.Error;Message = We cannot apply operator < to types Table and Date.;Detail = [Operator = "<", Left = Table.FromRecords({}), Right = #date(2008, 10, 31)];Message.Format = We cannot apply operator #{0} to types #{1} and #{2}.;Message.Parameters = {"<", "Table", "Date"}

Is what I am doing not supported or did I do something wrong?

Here is the advanced editor text for first and 2nd steps:

let

  Source = Sql.Database("Link to Lakehouse", "Lakehouse"),

  #"Navigation 1" = Source{[Schema = "dbo", Item = "view1"]}[Data],

  #"Changed column type" = Table.TransformColumnTypes(#"Navigation 1", {{"maxeffdate", type date}}),

  #"Drill down" = #"Changed column type"[maxeffdate],

  #"Calculated maximum" = List.Max(#"Drill down")

in

  #"Calculated maximum"

...and the 2nd step:

let

  Source = Sql.Database("SQL Server", "Database"),

  #"Navigation 1" = Source{[Schema = "dbo", Item = "table"]}[Data],

  #"Changed column type" = Table.TransformColumnTypes(#"Navigation 1", {{"effdate", type date}}),

  #"Filtered rows" = Table.SelectRows(#"Changed column type", each [effdate] > MaxEffDate)

in

  #"Filtered rows"

[gmoore7]

Thanks! That did the trick!

[Anonymous]

Hi @gmoore7 
Glad that your query got resolved. Please continue using Fabric Community for any help regarding your queries.

[miguel · Accepted solution]

Hi!

Is your first query being staged or not? If it set to be staged then that might be causing an issue as only tables can be staged and scalar values (like the one from a List.Max) will yield an error.

You can either set your first query to not be staged or change some components of your query so that the aggregation is perhaps done with a Group by and then you do a drill down in the second query to the Max value that you're looking for from the field that you require.