Forum Discussion
project online custom fields
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.
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!
echristoph wrote: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!
echristoph How did you connect the tasks site to XML? any pointers is appreciated.
Where you able to solve this problem? I am having the same situation.
The answer is in my earlier reply. You have to use the Web Reader connector, not the OData connector. Look at the Source line in the advanced query editor example I posted.
