Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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 deskt...
Need Help
Anonymous's avatar
Anonymous
Not applicable
8 years ago

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:

Server Reporting in PWA

 

Regards,

Xiaoxin Sheng

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Bringing this post back from the dead. 

     

    Did anyone figure out how to use Project custom fields in power bi?

  • echristoph's avatar
    echristoph
    Helper I
    7 years ago

    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.
    • echristoph's avatar
      echristoph
      Helper I
      7 years ago

      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!

      • chowdarygm's avatar
        chowdarygm
        Frequent Visitor
        6 years ago
        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.