User-defined Parameters with Dataflows (Direct Query)

[purple_banana]

I have a DQ connection to a dataflow, and am successfully able to use parameters to limit the returned rows. However when I bind a field to that parameter (link), the query fails. I have verified that the field, parameter and dataflow field all share the same datatype (Date) and that the values I am using are valid.

I noticed in the Advanced Editor, the line referring to the parameter looks like this:

#"Filtered Rows1" = Table.SelectRows(#"Removed Columns1", each [_Date] >= LoadOnOrAfter)

But when I hard-code a date, it looks like this:

#"Filtered Rows2" = Table.SelectRows(#"Removed Columns1", each ([_Date] = #date(2022, 4, 11)))

I have also attempted to modify that hard-coded example and pass in three integer parameters, but this does not work either:

#"Filtered Rows2" = Table.SelectRows(#"Removed Columns1", each ([_Date] = #date(Year, Month, Day)))

Please can anyone advise what I might be doing wrong?

TIA

[Daryl-Lynch-Bzy]

Hi @purple_banana - I think you need to check that value passed from Slicer (i.e. LoadOnOrAfter) is a Date.  It might be text or datetime.  You may need to convert the value using a Date.From() function.  Also double-check that the [_Date] is Date.Type, it might be expecting a DateTime.Type.   For the 3 formula, could you please provide the steps where the Date was converted to Year, Month and Day.

[purple_banana]

Thanks for your reply, Daryl.

I have verified that the datatype for all relavent fields & parameters are the same - 'Date'. I even tried changing the parameter to DateTime which resulted in a failure due to the mismatched datatypes (Date vs DateTime).

I tried converting the parameter using Date.From(LoadOnOrAfter), that didn't make any difference.

There aren't any other steps for my attempt to pass in the Year, Month & Day parameters, they are just configured as three numeric parameters.

I think the issue is to do with the binding of fields to the parameters. If I unbind them and set the values via Manage Parameters, the queries work perfectly. It is only when I bind them to fields in my Calendar table that the queries fail.