How to Filter Date/Time Columns in DirectQuery Mode Using M Query Parameters?

[AnupTandale]

I am working with a table in DirectQuery mode in Power BI, and I need to filter a column of type `date/time` using slicers. Currently, the parameters `startDate` and `endDate` are of type `date` and bound to slicers. My M query for changing type of parameter and adding default time looks like this:

let
KustoParameterDeclareQuery = Text.Combine(
{
"declare query_parameters(",
"startTime:datetime = datetime(", Date.ToText(startDate, "yyyy-MM-dd") & " 00:00", "), ",
"endTime:datetime = datetime(", Date.ToText(endDate, "yyyy-MM-dd") & " 23:59", ")); "
}
),

Since `startDate` and `endDate` are of type `date`, I can't directly use them to filter columns of type `date/time`. When I attempt to change their type to `date/time` to match the column type, I get the following error:

"This type of transformation is not possible in DirectQuery mode."

Is there an alternative method to filter `date/time` columns using slicers, while staying within the limitations of DirectQuery mode? I’d like to avoid switching to Import mode or making significant changes to the existing setup.

Any suggestions or guidance would be greatly appreciated!

Thank you.

[Anonymous]

Hi @AnupTandale ,

Thanks bhanu_gautam  for the quick reply and solution. I have some other ideas to add:
My test steps are as follows:

(1) You can change the parameter to be of type Date/Time. 

(2) Open the “Advancd Editor” reference code:

let
    sqlQuery = "SELECT * FROM Calendar WHERE DateField >= '" & DateTime.ToText(StartTime, "yyyy-MM-dd hh:mm:ss") & "' AND DateField <= '" & DateTime.ToText(EndTime, "yyyy-MM-dd hh:mm:ss") & "';",
    Source = Sql.Database("vm0", "DateDatabase", [Query=sqlQuery])
in
    Source

(3) Create two slicer tables and then bind the parameters.

Start = CALENDAR(DATE(2024,1,1),DATE(2024,1,31))

End = CALENDAR(DATE(2024,1,1),DATE(2024,1,31))

(4) Result:

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[bhanu_gautam · Accepted solution]

@AnupTandale , Try using

let
startDateText = Date.ToText(startDate, "yyyy-MM-dd"),
endDateText = Date.ToText(endDate, "yyyy-MM-dd"),
KustoParameterDeclareQuery = Text.Combine(
{
"declare query_parameters(",
"startTime:datetime = datetime(", startDateText, " 00:00:00), ",
"endTime:datetime = datetime(", endDateText, " 23:59:59)); "
})
in
KustoParameterDeclareQuery

The startDate and endDate parameters are kept as date type, and the datetime values are constructed within the query using Date.ToText to format the dates and append the time portion.