Power Query giving error while importing certain OData feeds from azure devops

[ArundhatiD]

Hi Team,

I need urgent help.I want to build team velocity chart in power bi from azure devops data.Planned workitems verses Completed workitems. I am using odata feed for getting workitemrevision data.

https://analytics.dev.azure.com/{organization}/{project}/_odata/v3.0-preview/WorkItemRevisions?

The URL is working fine and i could see data but when i connect to power bi, and i try to expand Area path and Iteration path in power query, it gives error and goes in infinite path.The error is as below:

DataFormat.Error: We expected a property 'IterationPath', but the OData service omitted it from the response data. This can occur for a number of reasons and does not necessarily imply that the data does not exist or is of a default value.
Details:    Property=IterationPath.

The immidiate response is appreciated.

Thanks and Regards,

ArundhatiD

[v-yanjiang-msft]

Hi @ArundhatiD ,

Is your problem solved? If so, would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirment will find the solution quickly and benefit here, thank you!

Best Regards,
Community Support Team _ kalyj

[v-yanjiang-msft]

Hi @ArundhatiD ,

I suggest you try the solution in this solved thread:

---

Remove OmitValues = ODataOmitValues.Nulls from the query. 

It appears that the sample query assumes there are only non-null values, and will therefore omit null values. Removing this part of the query ensures that null values are considered valid, and therefore removes the error.

---

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ArundhatiD]

Hi v-Kalyj,

I have removed the OmitValues = ODataOmitValues.Nulls.  And query looks like below:

= OData.Feed("https://analytics.dev.azure.com/Organization/project/_odata/v3.0-preview/WorkItemRevisions?$filter= WorkItemType in ('User Story')
&$select=WorkItemId,Title,WorkItemType,Revision,RevisedDate,IsCurrent,IsLastRevisionOfDay,State,StateCategory,StoryPoints,Priority,Severity,TagNames,AreaSK
,CycleTimeDays,LeadTimeDays,InProgressDate,CompletedDateSK,CompletedDate,StartDate,NT_Test_DefectType
&$expand=AssignedTo($select=UserName),Area($select=AreaPath),Iteration($select=IterationPath),Project($select=ProjectName),
)
",
null, [Implementation="2.0"])

But after that i am getting error as 

DataSource.Error: OData: A null value was found for the property named 'AreaPath', which has the expected type 'Edm.String[Nullable=False]'. The expected type 'Edm.String[Nullable=False]' does not allow null values.
Details:
DataSourceKind=OData
DataSourcePath=https://analytics.dev.azure.com/Organization/project/_odata/v3.0-preview/WorkItemRevisions

Thanks,

[v-yanjiang-msft]

Hi @ArundhatiD ,

As the error suggests, it must have returned a null value in a column which claimed does not allow nulls. Can you change the "Edm.String[Nullable=False" to Ture, or remove the column, or put a filter on the query not to retrieve null values?

Or refer to this document to have a try: How to handle ‘A null value was found for the property’ exception

Best Regards,
Community Support Team _ kalyj