Search code examples
powerbipowerquerym

Use date parameters with a PowerBI "direct" query

I created a 'direct' query in PowerBI, that I would like to modify to support two date parameters: StartingDate and EndingDate:

enter image description here

I modified the query to reference the two parameters:

= Sql.Database(
  "10.0.0.4", 
  "PHCS_UAT", 
  [Query="
    SELECT  *#(lf)
    FROM    TABLE#(lf)
    WHERE   StartTime BETWEEN '" & StartingDate & "' AND '" & EndingDate & "'", CreateNavigationProperties=false]
)

This generates an error: We cannot apply operator & to types Text and Date..

What's the preferred way to convert a date to a string?

I would to add 23h 59m 59s to the EndingDate parameter. I could do this in the SQL (select dateadd(second,-1,cast( dateadd(day,1,cast(getdate() as date)) as datetime))), but it might be cleaner using a PowerBI function. Is there a way to do so?

Solution

  • How about this?

    StartTime BETWEEN '" & Text.From(StartingDate) & "' AND '"
                     & Text.From(EndingDate) & " 23:59:59'"