In Power BI Connector the most of OData (Power query) operators are supported: $top, $skip, $select, $count, $search, $orderby, $filter.
...
$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)
| Info |
|---|
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>
with Referenced fields:
$filter=cmdb_ci.sys_class_name IN cmdb_ci_computer, laptop
$filter=cmdb_ci.sys_mod_count IN 1,2,3
$filter=cmdb_ci.location IN <sys_id>, <sys_id>,<sys_id>
The next 3 OData operators are easy to check directly from browser, write correct HTTP request with parameters. Please see the example below:
$skip - allows users to skip the number of not necessary items and show items collections without skipped ones. To check $skip parameter enter in your browser URL:
https://[instance name]/api/x_716979_powerbi_c/v1/powerbi_connector/[datasource_id]/[table_name]?$skip={N}, where N -is number of skipped rows
$count - return numbers of all items per table. To check $count parameter enter in your browser URL: https://[instance_name]/api/x_716979_powerbi_c/v1/powerbi_connector/[datasource_id]/[table_name]?$count=true
$search - allows users to return collection of items, searched by simple text:
only with whole words, no half words supported
no chars, (no numbers, boolean, referenced fields, special symbols)
no OR/AND operator supported
To check $search parameter enter in your browser URL:
https://ven05068.service-now.com/api/x_716979_powerbi_c/v1/powerbi_connector/[datasource_id]/[table_name]?$search={T},
where T - is Text or Numbers types, lower/upper cases are supported
...
NOTE: $expand operator is not supported.