project online custom fields

[Anonymous]

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

[mayaaa]

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

[echristoph]

Is this what you are asking for: https://youtu.be/zvl5UkqH494?t=126

?

[mayaaa]

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

[jsheriff]

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.

[v-shex-msft]

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

[echristoph]

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]

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]

---

@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.

[CCaceresGIS]

Where you able to solve this problem? I am having the same situation.

[echristoph]

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.

[Anonymous]

Bringing this post back from the dead. 

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

[Anonymous]

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