Fix Azure DevOps OData Filters That Exclude Null Values
Question details
The user needs to correct an Azure DevOps OData query that unexpectedly filters out records with null values when using a negative Contains condition.
- Product
- Azure DevOps OData
- Device & OS
- not provided
- Scenario
- Filtering work items via OData queries for reporting or data extraction.
- Observed behavior
- Work items with a null ResolvedReason are excluded from the dataset when a 'not Contains' filter is applied, despite different behavior occurring natively within Power BI.
Verify the exact field name in your Azure DevOps environment and ensure you have permissions to test and modify the OData endpoint queries.
Explicitly Include Null Values in the OData Query
Modify your OData query URL to explicitly check for null values alongside your existing negative filter condition.
In OData, functions evaluated against null fields do not return standard boolean values that a 'not' operator can easily invert. To ensure unresolved work items or empty fields are captured, you must manually instruct the query to include null records.
Find the OData query string where the negative filter is applied. For example, your query might currently contain: filter=not Contains(ResolvedReason,'As Designed').
Append an explicit check for null values on the targeted field using the 'eq' operator. For example: ResolvedReason eq null.
Use the 'or' operator to combine both conditions and wrap them in parentheses to prevent logical conflicts with other filters. The updated string should look like: filter=(ResolvedReason eq null or not Contains(ResolvedReason,'As Designed')).
Run the updated OData query. Check your data output to ensure that records with empty or null ResolvedReason fields are now properly included in the returned dataset.
Analyze Your Exported Query Data Seamlessly with WPS Office
If you are exporting Azure DevOps query results to CSV or Excel formats for reporting, WPS Office provides a free, lightweight, and highly compatible alternative to Microsoft Office for your data analysis needs.
- 1. Download and Install: Get WPS Office for free from the official website and install it quickly on your computer.
- 2. Open Exported Data: Launch WPS Spreadsheets and open your Azure DevOps CSV or Excel data exports.
- 3. Analyze and Report: Use familiar spreadsheet features like PivotTables and conditional formatting to analyze your work item data.
Frequently Asked Questions
Why does the 'not Contains' OData function exclude null values?
During OData evaluation, when a string function like 'Contains' encounters a null value, it often evaluates to null or undefined instead of false. Applying a 'not' operator to an undefined result does not make it true, causing the filtering engine to drop those records entirely.
Does Power BI filter null values the same way as Azure DevOps OData?
Not always. Power BI's internal data engine handles blank or null values differently and may gracefully include them when applying negative visual or page-level filters. This discrepancy is why an OData query might return different row counts compared to Power BI's native filtering.
Can I apply this explicit null fix to other Azure DevOps fields?
Yes. Anytime you use a negative filter (such as 'not eq' or 'not Contains') on a field that might be empty or unassigned, you should explicitly include an '[FieldName] eq null' check using an OR operator to guarantee accurate data retrieval.
