Power query. User cases and implementation specific
In Power BI Connector the most of OData (Power query) operators are supported: $top, $skip, $select, $count, $search, $orderby, $filter.
To get more information about operators, structure, usage and API examples, please navigate for the official documentation to get more details:
Overview: https://docs.microsoft.com/en-us/odata/concepts/queryoptions-overview
Usage: https://docs.microsoft.com/en-us/odata/concepts/queryoptions-usage
OData Version 4.0. URL Conventions Plus
http://docs.oasis-open.org/odata/odata/v4.01/odata-v4.01-part2-url-conventions.html#sec_QueryOptions
After table is exported in Power BI and data are shown correctly, Transform Data functionality can be used to filter collection and work with desired items even after Data Source is refreshed.
There are some explanations with examples of how operators work in the scope of Power BI Connector + Power BI Transform Data:
$select - allows users to request a specific set of properties for each entity (fields that were added in the export config are available for selection)
$top - requests the number of items in the queried collection to be included in the result
$skip+$top(as a Keep Range of Rows) - allows users to set the range of items, skip top and limit the number of requested items:
$orderby - allows users to request resources in a particular order (fields that were added in the export config are available for selection). Multiple ordering is supported, in this case, the next $orderby will be applied to the previous order results:
$filter - allows users to return a collection of items filtered by logical parameters for Text type ('eq'
'and','or','gt','ge','lt','le',''contains','startswith' etc.) and Date type ('after', ‘before', 'today’ etc.) for each column:and Date fields:
Starting with version 1.6.15 of Power BI Connector for ServiceNow, $filter with the IN operator supports multiple single strings and does not support the following field types:
Date
DateTime
Boolean
Duration (in any view: seconds, OData, string)
ID (use sys_id to filter by item ID)
The Use display value option is not supported for the fields above.
Examples
How to run for sc_req_item table with Direct fields:
$filter=number IN RITM0000001, RITM0000002, RITM0000003
$filter=sys_mod_count IN 1,2,3
$filter=cmdb_ci IN <sys_id>, <sys_id>,<sys_id>