OData - We cannot convert the value null to type Text

[FlowViz]

Hi guys,

I have a .pbit that connects to Azure DevOps/Azure DevOps Server, which asks for the following details:

I want to have Project field as optional, so if users leave it blank they can get data for all the projects in the organization, however when leaving it blank they get the following error.

The query is:

= OData.Feed(#"Http/Https" & "://" & #"Analytics URL/Azure DevOps Server" & "/" & Organization & "/" & Project & "/_odata/v3.0-preview/WorkItems?%20$filter=WorkItemType%20ne%20%27Issue%27%20and%20WorkItemType%20ne%20%27Task%27%20and%20WorkItemType%20ne%20%27Test%20Case%27%20and%20WorkItemType%20ne%20%27Test%20Plan%27%20and%20WorkItemType%20ne%20%27Shared%20Parameter%27%20and%20WorkItemType%20ne%20%27Shared%20Steps%27%20and%20WorkItemType%20ne%20%27Test%20Suite%27%20and%20WorkItemType%20ne%20%27Impediment%27%20and%20State%20ne%20%27Removed%27%20&$select=AreaSK,CompletedDate,CompletedDateSK,CycleTimeDays,IterationSK,LeadTimeDays,ParentWorkItemID,State,StateCategory,TagNames,Title,WorkItemID,WorkItemType&$expand=Iteration($select=IterationName,IterationPath,StartDate,EndDate)", null, [Implementation="2.0"])

Is there any way around this to get it working?

[mahoneypat · Accepted solution]

Two potential approaches

1.  You start with a table of all Project names and then make the web call on each row, using the Project value on that row.  Prior to that step, you could have a Filter step that filters to the selected project (or null if not selected and no projects are filtered out. You could then expand the resulting tables to have your combined project data.

2.  If you can make a web call for all projects, you could have an if Project = null then OData.Feed( ) else OData.Feed()  with two different Odata calls.

Pat

[v-yingjl · Accepted solution]

Hi @FlowViz ,

Based on the error message, there have 'null' values in your data source which could not be converted to text type.

'null' value and blank value are different, refer: https://www.excelguru.ca/blog/2018/10/04/nuthin-aint-nuthin-in-power-query/ 

Please check your data type which includes 'null' value and replace values before you convert it to text type.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mahoneypat · Accepted solution]

Two potential approaches

1.  You start with a table of all Project names and then make the web call on each row, using the Project value on that row.  Prior to that step, you could have a Filter step that filters to the selected project (or null if not selected and no projects are filtered out. You could then expand the resulting tables to have your combined project data.

2.  If you can make a web call for all projects, you could have an if Project = null then OData.Feed( ) else OData.Feed()  with two different Odata calls.

Pat