Home

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

Frequent Contributor

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.

Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
202 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies