Power query. User cases and implementation specific
- Kateryna Bukhtiiarova ADMIN (Unlicensed)
- Liliia Ostrovska
- Inna Kashuba
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>
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.