Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Help with Power BI Template and Odata null values

Hi folks,

 

I have a problem I'm hoping the community can help with.

 

I have a Power BI template file for connecting to Azure DevOps data (using OData as source). With the template users enter their organization and project name, hit load and have charts auto populated for them. They can also select any custom fields they have added, like so:

 

 

So the whole query reads like so:
https://analytics.dev.azure.com/{Organization}/{Project}/_odata/v3.0/WorkItems?%20$filter=WorkItemType%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%20State%20ne%20%27Removed%27%20&$select=AreaSK,CompletedDate,CompletedDateSK,CycleTimeDays,IterationSK,LeadTimeDays,ResolvedDate,State,StateCategory,Title,ValueArea,WorkItemID,WorkItemType,{Custom Fields}

 

 

Problem is, if they don't have any custom fields I want them to be able to leave the field blank and the report still load, however when they do that I see the following message:

 

 

 

The full source in Power Query reads like so:
= OData.Feed("https://analytics.dev.azure.com/" & Organization & "/" & Project & "/_odata/v3.0/WorkItems?%20$filter=WorkItemType%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%20State%20ne%20%27Removed%27%20&$select=AreaSK,CompletedDate,CompletedDateSK,CycleTimeDays,IterationSK,LeadTimeDays,ResolvedDate,State,StateCategory,Title,ValueArea,WorkItemID,WorkItemType," & #"Custom Fields", null, [Implementation="2.0"])

 

Any tips for what I should be doing differently?

1 Reply

  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    Icon for Community Support rankCommunity Support

    Hi Anonymous ,

     

    Do you mean that you want to convert the column which contains null value to text type?

    What was the column’s type?

     

    Did the error occur when you closed and apply?

    And what happens when you convert format in data view?

     

    Best regards,

     

    Community Support Team _ zhenbw

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