Dataflow Gen2 Max Effective Date as Parameter

gmoore7 — Wed, 27 Dec 2023 15:53:00 GMT

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")

#"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)

#"Filtered rows"

Re: Dataflow Gen2 Max Effective Date as Parameter

miguel — Wed, 27 Dec 2023 17:20:10 GMT

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.

Re: Dataflow Gen2 Max Effective Date as Parameter

gmoore7 — Wed, 27 Dec 2023 17:36:03 GMT

Thanks! That did the trick!

Re: Dataflow Gen2 Max Effective Date as Parameter

Anonymous — Wed, 27 Dec 2023 17:55:17 GMT

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

topic Re: Dataflow Gen2 Max Effective Date as Parameter in Dataflow

Dataflow Gen2 Max Effective Date as Parameter

Re: Dataflow Gen2 Max Effective Date as Parameter

Re: Dataflow Gen2 Max Effective Date as Parameter

Re: Dataflow Gen2 Max Effective Date as Parameter