Round Up Numbers from Power Automate to SharePoint

 

This problem came up for me when I was using Power Automate to calculate an average of 3 numbers that had been entered into an MS Form.

The requirement was for the average to be shown as a whole number in a SharePoint list column, but with rounding down for decimals less than 0.5 and rounding up for decimals of 0.5 or higher as per normal mathematical conventions. 

This seems simple enough but when you set a Number column in SharePoint to show 0 decimal places it doesn't round numbers or down. Instead it just truncates the decimal values. So whether you have 3.1 or 3.7, if you show those figures to 0 decimal places SharePoint will show both of them as 3. That's fine for decimals of .4 or lower but what about decimals of 0.5 or above you need to round up?

Here's how to do it in Power Automate:

1. (I was calculating my number so I'll show that step but you can skip this if you've already got your number through some other means.) Calculate your average using an expression, for example:
   
   div(add(add(outputs('First Number'), outputs('Second Number')), outputs('Third Number')), 3)
   
   
2. Convert it to a decimal number with 1 decimal place using Format Number with a custom value of 0.0 for the Format:
   
   
   

   
   (if you have a whole number like 3, this will convert it to 3.0 and the below technique will still work perfectly)
   
   
3. This is where the magic happens: Use an expression to add 0.5 to the output of the Format step, if required:

if(greaterOrEquals(int(substring(body('Format'), add(length(body('Format')), -1), 1)), 5), add(float(body('Format')), 0.5), body('Format')) 

What is this expression doing? This part of the expression:

 int(substring(body('Format'), add(length(body('Format')), -1), 1)

is getting the last digit of your number.  Let's assume your number is 3.7, which means this part of the expression will return the number 7.

If we swap in the number 7, we get this expression: 

if(greaterOrEquals(7, 5), add(float(body('Format')), 0.5), body('Format'))

greaterOrEquals takes 2 arguments: The number to check, and the number to check against. 7 is greater than 5 so this will return true. 

If we swap in true we get this expression: 

if(true, add(float(body('Format')), 0.5), body('Format'))

If takes 3 inputs: A logical argument, the thing to do if the argument is true, and the thing to do if the argument is false. In this case if the argument is true we add 0.5 to the average and return that value, and if it false we just return the average. 

Because 7 is greater than 5 the If statement returns true, so it adds 0.5 to the average and returns 3.7 + 0.5 = 4.2.  If the decimal was less than 5 , such as 3.1, then the If statement would return false and just return the average.

Remember, SharePoint doesn't round up so when SharePoint formats your number to 0 decimal places both 3.1 and 3.7 will be shown as 3. 

So by adding 0.5 to any number where the last digit is 5 or greater we'll end up with the next whole number. In this case, that will be 4.2, so when SharePoint truncates it to 0 decimal places, you get 4. 

This meets the mathematical convention of "rounding up" 0.5 and above to the next integer even if we're not technical rounding up, we're just amending the number to fool SharePoint into looking like its rounded up.

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

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']

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.

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.