PDA

View Full Version : Using a parameter table to filter dates column



kerygma
2016-07-12, 03:18 AM
I'm having the hardest time, using Ken Puls's Parameter table function (fnGetParameter) to work ... I'm getting different errors... the following error:

"Expression.Error: We cannot apply operator < to types DateTime and Date.Details:
Operator=<
Left=06/29/2016 12:00:00 AM
Right=06/30/2016"

I have the following in my query:
let
date1 = fnGetParameters("date1"),
date2 = fnGetParameters("date2"),
........
#"Filtered Rows" = Table.SelectRows(#"Filtered Rows1", each [Date of Service] >= (date1) and [Date of Service] <= (date2))
in
#"Filtered Rows"

Can't figure out what exactly is wrong, can't figure out how to format. Any help is much appreciated! I've tried #date(date1)... that doesn't work either.
Thanks again!

Ken Puls
2016-07-12, 04:51 AM
Try this:


let
date1 = Date.From(fnGetParameters("date1")),
date2 = Date.From(fnGetParameters("date2")),
........
#"Filtered Rows" = Table.SelectRows(#"Filtered Rows1", each [Date of Service] >= (date1) and [Date of Service] <= (date2))
in
#"Filtered Rows"

Hope that helps!

kerygma
2016-07-12, 04:12 PM
Worked like a charm. Thank you! I was looking all over the MSDN website for the correct M formula and couldn't find it, hard to tell which one is the correct one.

Thanks again!