Forum Discussion
project online custom fields
Hi Friends,
I have created many custom fields in Project Online and using those into my enterprise project. I want to use those fields in Power BI but I neither see those fields in Power BI desktop version nor on online. Am I doing anything wrong? Urgent help needed.
Warm Regards
Hari
12 Replies
Hi Anonymous,
Based on test, default project connector can't access to custom fields.
Please use odata connector with project online rest api to get custom fields.
Reference link:
Regards,
Xiaoxin Sheng
Attempting to use the rest API when retrieving enterprise custom field for tasks gets the following error:
DataSource.Error: We couldn't parse OData response result. Error: A value without a type name was found and no expected type is available. When the model is specified, each value in the payload must have a type which can be either specified in the payload, explicitly by the caller or implicitly inferred from the parent value.
This is because the custom fields come back like this when you perform a REST query to Project Online:
<content type="application/xml"> <m:properties> <d:Id m:type="Edm.Guid">83e390a8-d357-e911-afb5-00155d0c6b26</d:Id> <d:OutlinePosition>2</d:OutlinePosition> <d:ActualFinish m:type="Edm.DateTime">0001-01-01T00:00:00</d:ActualFinish> <d:ActualStart m:type="Edm.DateTime">0001-01-01T00:00:00</d:ActualStart> <d:Cost m:type="Edm.Double">13591.68</d:Cost> <d:Duration>4d</d:Duration> <d:Finish m:type="Edm.DateTime">2019-04-10T17:00:00</d:Finish> <d:IsActive m:type="Edm.Boolean">true</d:IsActive> <d:Name>Develop Analysis of Alternatives (AoA) Study Guidance</d:Name> <d:OutlineLevel m:type="Edm.Int32">1</d:OutlineLevel> <d:PercentComplete m:type="Edm.Int32">0</d:PercentComplete> <d:PercentPhysicalWorkComplete m:type="Edm.Int32">0</d:PercentPhysicalWorkComplete> <d:RemainingDuration>4d</d:RemainingDuration> <d:Start m:type="Edm.DateTime">2019-04-05T08:00:00</d:Start> <d:Work>160h</d:Work> <d:Custom_x005f_90eaefb93561e911afb900155d0c3815 m:type="Collection(Edm.String)"> <d:element>Entry_da588e9e3561e911afb900155d0c3815</d:element> </d:Custom_x005f_90eaefb93561e911afb900155d0c3815> /m:properties> </content>Notice the <d:element> tag does not have an "m:type" attribute; I think this is confusing PowerBI.
You can try it yourself:
https://[yourtenant].sharepoint.com/sites/[yoursite]/_api/ProjectServer/Projects('[projectGUID]')/Tasks('[taskGUID]')
This will work in a browser URL (assuming you are logged in), but will error in Power BI.
Any ideas how to workaround? If I could retrieve the element value into PowerBI I can relate it to the Lookup Table.
P.S. I don't know how to escape the stupid emoticons in the code block, sorry.OK, I figured it out. You can't get them using the OData connector, but you CAN do it with the XML reader. Example:
Source = Xml.Tables(Web.Contents("https://[yourtenant].sharepoint.com/sites/[yoursite]/_api/ProjectServer/Projects?" & "$expand=Tasks&$select=Id" & ",Tasks/Id" & ",Tasks/OutlineLevel" & ",Tasks/OutlinePosition" & ",Tasks/Name" & ",Tasks/Start" & ",Tasks/Finish" & ",Tasks/ActualStart" & ",Tasks/ActualFinish" & ",Tasks/PercentComplete" & ",Tasks/PercentPhysicalWorkComplete" & ",Tasks/Duration" & ",Tasks/RemainingDuration" & ",Tasks/Work" & ",Tasks/Cost" & ",Tasks/IsActive" & ",Tasks/Custom_x005f_90eaefb93561e911afb900155d0c3815" &
",Tasks/Custom_x005f_1a9572776960e9119487283a4d07a952" &
",Tasks/Custom_x005f_351797c94968e911b07700155d10a106" &
",Tasks/Custom_x005f_c4f66ed64968e911b07700155d10a106" &
",Tasks/Custom_x005f_d3dfad7fa85fe911afb800155d10502b"))Notice the Tasks/Custom_x005f_xxxxxxxx. Those are enterprise custom field lookup entries associated with that task. You can pull their values from the Enterprise Lookup tables and relate them in Power BI. Solved!
Here is an article I ran across detailing how to include the Enterprise Custom Fields (ECF) inside Power BI via the pre-built Power BI to Project Online PBI file.
https://developer.microsoft.com/en-us/office/blogs/extending-the-powerbi-for-project-online/
Pay close attention to Tip #2, as this is where you include your custom fields inside PowerBI.
Hi,
I also have created some columns custom fields in Project Online I want to extract those columns in Power BI but I neither see those fields in Power BI desktop (I used Oflux data).
Also I browsed the content of the forum saying that we need to update the data from the WEB through XML language but how do we get there?
1/ Get data from another source : WEB
Address: https://xxxxx.sharepoint.com/sites/xxxxxxx/_api/ ?
or 2/ Get data from another source: Import XML Code
Source = Xml.Tables(Web.Contents("https://xxxxx.sharepoint.com/sites/xxxxx/_api/ProjectServer/Projects?" &
"$expand=Tasks&$select=Id" &
",Tasks/Id" & ",Tasks/OutlineLevel" &
",Tasks/OutlinePosition" &
",Tasks/Texte1" &
",Tasks/Texte2" &
",Tasks/Text3" &
",Tasks/Nomdelatâche" &
",Tasks/WPLeader" &
",Tasks/SupplierName" &
",Tasks/Fin" &
",Tasks/Finderéférence1" &
",Tasks/category" &
",Tasks/%achevé" &
",Tasks/Variance(calendardays)"))
?
Thank you for your help
No.. I created in microsoft project custom columns and I would like to import these custom columns in power BI except that the O FLUX DATA transaction does not extract them. By browsing the exchanges on this forum I saw that the extraction of these custom columns was possible through the generation of an XML code and I would like to know the step by step of this solution
