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
Reporting on Project Online (PWA) Timesheets
Andy Dennis in Project on
3 Replies
Add, Edit, Delete Project Online data using Excel
MonteStJohns in Project on
5 Replies
Migrate from Project Server 2013 to Project Online
Adullah_Ismail in Project on
1 Replies
Project Online %work and actual cost ERROR
JeanLouis in Project on
2 Replies