david wyatt

Posted on May 13

Power Automate - Get Items Parameters

#powerautomate #rpa #lowcode #powerplatform

Get Items in SharePoint and List Items in Dataverse are fundamental to a lot of flows, allowing easy ingress of data. They both work on the OData standard, though still having slight variations, which makes it easy to learn both.

OData is a API standard not just for Power Automate, so its easy to find documentation about it. So in this blog Im going to talk about the API and then link it to Power Automate (that way is easier to expand your learning).

The key settings that impact our Get Items is the query parameters. This is when we add a ? to our GET url and add a kind of formula to change how the data is returned. OData has 6 standard formulas when used in the Power Platform, they are:

$filter
$expand
$select
$orderBy
$top
$skip/$skiptoken

$filter

Filter is probably the most useful, it allows you to return specific records.

At its basic it is:

$filter = field eq 'value'

eq represents = (equals)

Query	Represents	Description
eq	=	equals
ne	<> / !=	not equal to
gt	>	greater than
ge	>=	greater than or equal to
lt	<	less than
le	<=	less then or equal to
startswith	left	has same first characters
substringof	contains	has characters within string
day	dd	dateTime has this day of the month

there is also month,year,hour,minute,second that match the day structure

So if I wanted to return all records where the value was greater than 10 it would be:

$filter = value ge 10
note that there is now no ', as this is only for strings, not integers or booleans

You can also chain your formulas with and/or.

So if I wanted value equals 10 and name not David it would be:
$filter = value eq 10 and name ne 'David'

Or if I wanted value equals 10 and name is David or Laura it would be:
$filter = value eq 10 and (name eq 'David' or name eq 'Laura')

To to use in Power Automate, the Get Items adds a drop down but you can flip to raw import and build out your more complex formulas here:

For Dataverse at the moment it's just the formula:

$expand
Expand allows you to bring in any lookup values. So let's say you have 2 tables one with sales, one with item details. The item field in the sales table might be a lookup of the items in the item table. If we wanted to see all of the fields in the item table we can expand the item field.

Without you would get:

value:{
  "date":"10/01/24",
  "item":1,
  "advisor":2
}

But with expand you get:

value:{
  "date":"10/01/24",
  "item":{
    "id":1,
    "description":"pen",
    "cost":4
    },
    "advisor":2
}

To use it we just add the field name, if we want more then one we add a comma:

$expand = item, advisor

This is how it pretty much works directly in the Dataverse action (though be sure to get the field name not display name), the lookups are then returned as a flat response (ie they are not nested under it).

value:{
  "date":"10/01/24",
  "item":1,
  "_item_value@description":"pen",
  "_item_value@cost":4,
  "advisor":2
}

But it's different in the SharePoint action (unless using the SharePoint HTTP action).There is no expand option, instead the expand is delegated to the list in the site. When creating a lookup column/field you have an option to expand what fields you want. These are then returned as there own object.

value:{
  "date":"10/01/24",
  "item":{
    "id":1,
    "value":1
    },
  "item_x003a__x0020_description":{
    "id":1,
    "value":"pen"
    },
  "item_x003a__x0020_cost":{
    "id":1,
    "value":4
    },
  "advisor":{
    id":2,
   "value":2
  }
}

Luckily this mess is hidden away in the dynamic content selector.

$select

Select allows you to return the fields you want. Its structured the same as $expand (comma separated list). If left blank it returns all, else just those fields (and defaulted metadata fields).

So:

$select = date

will return:

value:{
  "date":"10/01/24"
}

Again this is exactly how it is in Dataverse action, but not in SharePoint action.

In SharePoint again you delegate to the list site again, but this time by creating a list view. You then select that in the action and it will return the fields shown in that view.

JSON Response

value:{ 
  "@odata.etag":"4"'
  "ItemInternalId":"1"
  "ID":1
  "Title":"1"
  "date":"2023-08-05T07:00:00Z"
  "{Identifier}":"Lists%252fTest%2blist%252f1_.000"
  "{IsFolder}":false
  "{Thumbnail}":{…}
  "{Link}":"https://37wcqv.sharepoin…8C39A44BB584B51CE50B8A32"
  "{Name}":"1"
  "{FilenameWithExtension}:"1"
  "{Path}":"Lists/Test list/"
  "{FullPath}":"Lists/Test list/1_.000"
  "{HasAttachments}":false
  "{VersionNumber}":"4.0"
}

as you can see there is lots of metadata and the ID + Title columns always included

We can also use the / to select a field from an expanded field:

$select = item/cost

$orderBy

Order by is setting which field and direction to sort the returned data.

To use it simply add the field name, a space, and then if its ascending (asc) or descending (desc). Again you can add multiple by using a comma:

$orderBy = date asc, item desc

Finally SharePoint and Dataverse actions are the same.

Dataverse

SharePoint

$top

Top allows us to limit the number or rows of data, so if we only wanted the first 10 rows we would use:

$top = 10

There is no $bottom, but you can use the $orderBy and reverse the order to return the last rows.

Both Dataverse and SharePoint actions have top, so you just need to add a number (blank will return the maximum page size).

Important call out here, top returns the top n rows in that query, so it does not work with pagination on. Pagination will automatically send additional queries until it hits the threshold. This means if you turn on pagination and have a threshold of 100, even though you set the $top =10, you will still get 100 rows (because it will send 10 requests of 10).

If you want to know more about pagination check out this previous blog.

$skip/$skiptoken

Skip allows you to start your return from the middle of the data, so if we didn't want the first 10 rows but all of the rest we can skip them.

$skip= 10

Although part of OData, $skip is not supported by Dataverse or SharePoint, instead they use a $skiptoken (I do wish it was supported though as feels pretty useful).

A skiptoken is returned by the server and allows you to skip a block of rows. Its main use is in pagination, where it will use the skiptoken to get the next page.

The SharePoint action doesn't have $skiptoken, but Dataverse action does. Though with automatic pagination it use is very limited.

Additional Odata Formulas

There is also $apply, which allows you to aggregate/group the returned data, and $count, that returns a integer showing number of rows of data, but no data.

But $apply is not supported by SharePoint and not shown in the Dataverse action, and $count is not supported by either (again wish it was as I think it would be quite useful).

Additional Inputs

There are also a couple of additional inputs:

Partition ID (Dateverse)
This is used for elastic tables (tables which us Cosmos DB which is an file database instead of Azure SQL a relationship database).

I won't go into to much detail, just to say partitions are a key part of file based databases and help mitigate the slower performance of them vs relationship databases. You can read more here

Fetch Xml Query (Dataverse)
This allows you to build out queries in xml instead of OData (so it can do filters etc). XML is often thought of having been superseded by JSON, but it is what Dynamics (and therefore Dataverse) was originally built on and the Fetch Xml Query ensures backward compatibility. Also you can also build some kick ass queries if you know what you are doing.

Limit Entries to Folder (SharePoint)
Only for Libraries (yes libraries are lists under the hood so use the same API), allows you to select a specific folder.

Include Nested Items (SharePoint)
Again only for Libraries, this allows you to set the return to just be the library/folder, or every nested folder within that library/folder.

You can use the SharePoint HTTP action, or the HTTP with Microsoft Entra ID (preauthorized) Invoke HTTP action, to call the GET queries directly, and here you can use all the API documentation to learn even more functionality (If you want to know more about HTTP actions I have a blog on it here).

OData is also used in the Graph API (Microsoft uses for Outlook, Teams, etc), so a lot of above can be transferred across, with the usual small differences between each.