Wednesday 15 March 2023

Power Automate: Use FIRST Instead of "Apply to each" When Pulling Out a Single Value

Credit: Patrick Tomasso

If you're returning rows from a spreadsheet or a SharePoint list, you normally use an "Apply to each" action to pull out the data.

But when you use "Apply to each" the entire row is pulled into memory and this can be slow, especially if you've got a lot of columns. 

If there is only one row this isn't necessary; instead you can use the "First" expression, which is much quicker as it just loads a specific column value rather than the whole row.

Let's say you're filtering on a unique refeerence number column called "URN" to return a specific row using an OData Filter Query, such as urn eq 'example', and you want to get the value of a column from that row called "Start Date".

A screenshot of a "List rows present in a table" action showing a filter query

Rather than using "Apply to each" to pull the value out, use the following expression in a Compose or Set Variable action:

First(outputs('List_rows_present_in_a_table')?['body']?['value'])?['Start Date']

An "Initialize variable" action showing the FIRST expression

And now you've got the value you need without having to load all the rows - it's quicker, uses one less step, and is easy to implement.

Saturday 8 September 2018

How To Bulk Publish in SharePoint (without using Site Content and Structure)

Photo by Bank Phrom on Unsplash

Back in June 2018, Microsoft announced that they were removing SharePoint Online's Site Content and Structure page in October 2018.  This had some useful features in it, some of which are being replaced, some of which are not.  

One of Site Content and Structure's popular features is the ability to bulk publish all of the documents in a library to a major version.  This is particularly useful if you only allow people with edit rights to see minor versions, and you want to publish a large number of documents at the same time, for example on the launch date of something.

How can this be done once Site Content and Structure is no longer available?

Let's go through the steps.

  1. Create a view that filters items based on "Version contains .1" OR Version contains .2 OR [all the way up to] "Version contains .9":
  2. Open the list, select this view and click Return to classic experience.
  3. Select all of the items in the list, open the Files tab and click Check Out.
  4. Select all of the items in the list, open the Files tab and click Check in.
  5. In the Check in panel that opens, select the Major version (publish) radio button and click OK
Once you've created this view you can use it whenever it's needed.  

You might think it would be easier to just create a view that says "Version does not contain .0", and you'd be right.  Unfortunately, SharePoint views don't support "does not contain", so you have to create this filter the long way round, so to speak.   If this is a common requirement for your users you can add this view to the libraries in your site template so you don't have to keep reproducing it by hand every time whenever you create a new site.

Tuesday 28 August 2018

Some Word Articles for Those Hot Sunny Evenings

Thanks to the World Cup (turned out it wasn't coming home, but it was brilliant nonetheless) and a summer heatwave, my output on AgileDocumentation from June through to August hasn't been high.  But I've still been writing, just not here.  

The fine folks over at How-To-Geek asked me to write some articles on Office 365 for them and I'm only too happy to oblige.  They've got a much bigger readership than this little old blog and long time readers will know I like to spread the O365 love, especially about Word.  I've got a few SharePoint articles planned for the coming weeks, but in the meantime here's some articles from How-To-Geek to keep your whistles whetted: