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

%3CLINGO-SUB%20id%3D%22lingo-sub-339975%22%20slang%3D%22en-US%22%3Edynamic%2Fnow()%20date%20for%20project%20online%20odata%20report%20via%20powerquery%2Fbi%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-339975%22%20slang%3D%22en-US%22%3E%3CP%3EIf%20I%20remember%20correctly%2C%20project%20online%20uses%20oData%20version%203%20which%20doesn't%20support%20now()%20for%20dates....%20enter%20powerquery%2FBI.%20Took%20me%20a%20long%20time%20to%20come%20around%20to%20trying%20this...%20but%20you%20can%20craft%20the%20URL%20string%20using%20M%20code%20%2F%20power%20query%20%3CEM%3Eand%20then%3C%2FEM%3E%20make%20the%20odata%20call.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20an%20example%20of%20grabbing%20base%20capacity%20from%20the%20odata%20filtered%20ResourceTimephasedDataSet%3C%2FP%3E%3CBLOCKQUOTE%3E%3CDIV%3E%3CFONT%3Elet%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Today%20%3D%20Date.ToText(Date.From(DateTime.LocalNow()%20as%20datetime)%2C%22yyy-MM-dd%22)%2C%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20baseURL%20%3D%20%22%3CA%20href%3D%22https%3A%2F%2FyourSiteHere.sharepoint.com%2Fsites%2Fpwa%2F_api%2FProjectData%2FResourceTimephasedDataSet%3F%24select%3DResourceId%2CTimeByDay%2CBaseCapacity%26amp%3B%24filter%3DTimeByDay%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Ehttps%3A%2F%2FyourSiteHere.sharepoint.com%2Fsites%2Fpwa%2F_api%2FProjectData%2FResourceTimephasedDataSet%3F%24select%3DResourceId%2CTimeByDay%2CBaseCapacity%26amp%3B%24filter%3DTimeByDay%3C%2FA%3E%20gt%20datetime'%22%2C%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20URL%20%3D%20Text.Combine(%7BbaseURL%2CToday%2C%22'%22%7D%2C%22%22)%2C%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20baseCapacity%20%3D%20OData.Feed(URL)%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%3CBR%20%2F%3Ein%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20baseCapacity%3C%2FFONT%3E%3C%2FDIV%3E%3C%2FBLOCKQUOTE%3E%3CP%3E%3CFONT%20color%3D%22%230b0117%22%3EKey%20is%20in%20correct%20formatting%20of%20the%20date%20and%20the%20single%20quotes%20that%20wrap%20it.%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
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.

0 Replies