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)

  Open

  

• $top - requests the number of items in the queried collection to be included in the result 

  Open

  

• $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:

   

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:

1. only with whole words, no half words supported

2. no chars, (no numbers, boolean, referenced fields, special symbols)

3. 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.