dynamic/now() date for project online odata report via powerquery/bi

[Ian Bruckner]

If I remember correctly, project online uses oData version 3 which doesn't support now() for dates.... enter powerquery/BI. Took me a long time to come around to trying this... but you can craft the URL string using M code / power query and then make the odata call.

Here's an example of grabbing base capacity from the odata filtered ResourceTimephasedDataSet

let
    Today = Date.ToText(Date.From(DateTime.LocalNow() as datetime),"yyy-MM-dd"),
    baseURL = "https://yourSiteHere.sharepoint.com/sites/pwa/_api/ProjectData/ResourceTimephasedDataSet?$select=Res... gt datetime'",
    URL = Text.Combine({baseURL,Today,"'"},""),
    baseCapacity = OData.Feed(URL)
   
in
    baseCapacity

Key is in correct formatting of the date and the single quotes that wrap it.