Rounding in Microsoft Flow and Azure Logic Apps

Update: This article is redundant now due to the existence of the formatNumber function, which was made available early 2021. You can use formatNumber in a stand alone action like a compose or email body etc, as well as in a Select or Create HTML Table action where the input is a property of item(). Everything below is now obsolete info.

It came as a surprise to me that there isn’t a native function to round a floating point number to x decimal places in Azure Logic Apps and Flow.

If you Google you find people have enquired about this on various forums and while the answers are valid, I didn’t think any of the ones I found were really appropriate for any floating point number and are prone to failure.

Flow and Logic Apps expressions are quite powerful when you get into more complex scenarios and even though there’s no native function, it’s not that hard to build your own once you understand the algorithm:

If my number is a decimal with more than x decimal places, then see if the most significant of the remaining (insignificant) decimal places is 5-9, and if so, increment the number by the lest significant digit, else do not increment. In either case, chop off the insignificant decimal places after x. If the number has x or fewer decimal places, do nothing.

I’ll break this down into two parts, the “inner” if() statement, which evaluates whether the number should be rounded up or not and chops off the insignificant decimals, and the “outer” if() statement that evaluates whether to run the number through the inner if() or just pass it through without modification.

Throughout this example, the floating point number will be called variables(‘var_float’) and the number of decimal places we’re interested in is 2. This is common when dealing with currency.

Let’s start with the inner if(). I’m starting there because once you understand how it works you will easily recognise why the outer if() exists, and maybe you won’t even need to continue reading.

To do this you need to use the expression builder, which is the other tab (besides dynamic content) in the popup window that appears when you click in the input box of an action. The action to do this is may be a Compose, or if you data is in a table you might use a Select to transform all the data in a column.

While you do have to use the expression builder to write the expression, it is truly awful once you get past a certain complexity. For this example I’ve added my own line breaks and tab characters to help clarify what’s going on. Thankfully the expression builder ignores these so you can just paste back in once you’ve formatted the code.

Here is the whole inner if(). I’ve coloured the 3 sections. The documentation for if() explains the structure. The red section is the expression, this expression must output a boolean. If true, the first section is run, else the blue. Breakdown below.

float(
     if(
	contains('56789',
		substring(
			last(
				split(
					string(
						variables('var_float')
						),'.'
					)
				),
				2,1
			)
		),
	concat(
		first(
			split(
				string(
					variables('var_float')
					),'.'
				)
			),
		'.',
		substring(
                         last(
                              split(
                                   string(
                                         add(
                                            variables('var_float'),
                                            0.01
                                            )
                                         ),
                                    '.'
                                     )
                                ),
                           0,2
                      )
	),
	concat(
             first(
                  split(
                      string(
                            add(
                               variables('var_float'),
                               0.01)
                             ),
                       '.'
                    )
               ),
             '.',
             substring(
                      last(
                          split(
                               string(
                                     add(
                                        variables('var_float'),
                                        0.01)
                                      ),
                               '.'
                             )
                       ),
                       0,2
                )
         )
    )
)

Let’s break that expression down a bit more:

contains('56789',
		substring(
			last(
				split(
					string(
						variables('var_float')
						),'.'
					)
				),
				2,1
			)
		)

This is the contains() function. It’ll return true if the string in purple contains the string in blue. We want to know whether we are rounding our floating point number up or not so the output that goes into the if() needs to be true (we’re rounding up) or false (we’re not rounding up)

The string in blue contains the guts of the operation. It’s the substring function. Note the word “string” here. We’re going to be working with strings a lot in this whole operation, converting between strings, integers and floats quite a bit to take advantage of functions that expect differing inputs.

Below is the substring function on its own. Substring expects three inputs, a string, a start index and a length. What we’re looking for is the most significant of the insignificant bits. so let’s take the floating point input and convert it to a string (blue), then we split on the point (pink), take the decimal places part of that with the last() function (green) and run it through substring(). Substring is going to take that as it’s first input then go 2 characters in (red) and read 1 character (orange). The start index begins at 0 so this gets us the 3rd most significant digit as a one-character string

substring(
	last(
		split(
			string(
				variables('var_float')
				),'.'
			)
		),
		2,1
	)

Now when you put that into the contains() function above with ‘56789’ as its first input you can see how that would provide the required input to the inner if(). Is the most significant of the insignificant bits a number between 5 and 9? Or to rephrase; does the string ‘56789’ contain a string we grabbed by looking 3 characters into the part of our number after the dot?

You might already understand why need to validate the input with the outer if() – the split and substring functions will fail if the input doesn’t have the required characteristics.

Now we’ve established whether our number needs rounding up or not, we go into the actual manipulation. I’ll start with the false value (it doesn’t need rounding) because it’s simpler.

concat(
	first(
		split(
			string(
				variables('var_float')
				),'.'
			)
		),
	'.',
	substring(
                 last(
                     split(
                          string(
                                variables('var_float')
                                ),'.'
                          )
                     ),
                      0,2
                 )      
)

In the expression part of the inner if() we used split() to grab the decimal places part of the number. We do the same here; split the input on the point, take the first(), whole numbers part (red), and concatenate that with a dot character (green) and then finally tack on the decimal places, chopped off where we want (pink). Concat() is very simple, it just takes an unlimited number of string inputs and puts them together.

The heavy lifting is done by the pink section:

substring(
	last(
		split(
			string(
				variables('var_float')
				),'.'
			)
		)
	),
	0,2
)

This is the substring() function again, but this time we take our input (blue), start at 0 (red) and go 2 characters in (orange). This constitutes the final string to be added at the end of the concat() function.

All up, this else value has taken the input floating point number, converted it to a string, split it on the decimal, taken the first two characters substring of the last part of that and combined it back into a string that resembles a floating point number with the concat() function.

Now the true value. This is almost identical to the false value except we have to do some rounding up:

concat(
	first(
             split(
                  string(
                        add(
                           variables('var_float'),
                           0.01)
                         ),
                  '.'
                )
             ),
	'.',
	substring(
		last(
			split(
				string(
					add(
						variables('var_float'),
					0.01
					)
				),
			'.'
			)
		),
		0,2
	)
)

As I previously mentioned, and I’m sure has been quite evident throughout this, we’re working with strings. You can’t concatenate a number to a string, so there’s some conversion going on.

String 1 and string 3 of the concat() function are very similar: The innermost add() function adds 0.01 to the original floating point number (green), then convert to a string, split on the dot (red). The difference is the first string takes the bit before the decimal with the first() function and the last string takes the bit after the decimal with the last() function and also runs it through substring to chop off insignificant digits.

The reason for doing this is because we can’t be sure the input isn’t going to be 99.998, which will round up to 100.008, so we have to perform the add() calculation twice and deal with the bit before and after the decimal separately, then recombine.

I would hazard a guess we could use the indexOf() function here to find the index of the decimal point to simplify the expression, but in this case we split and recombine. I will update this blog using indexOf() if I ever get around to it and it’s actually any simpler.

The rest of the expression is to turn that back into a string and combine it with the rest of the number using concat() just like we did with the false value, then wrap float() around the whole expression it to convert the string output of if() back into a floating point number.

Now the outer if(). A floating point number can be a plain integer, like 0 or 432, in which case no rounding is necessary, or it could already only be 1 or 2 decimal places in which case no rounding is necessary. In fact if you pass a number like this into the inner if() it’ll fail because split doesn’t like having nothing to split on and substring doesn’t like the start index or length being greater than the length of the starting string.

Here is the expression:

if(
	not(
		contains(
                     string(
			variables('var_float'),
			'.')
                         )
		),
	variables('var_float'),
	if(
		lessOrEquals(
			length(
				last(
					split(
						string(
							variables('var_float')
						),
						'.'
					)
				)
			),
			2
			),
		variables('var_float'),
		'inner if()'
	)
)

We need to know if the number contains a decimal, and if it does, the number of characters after the decimal is more than the number of decimals we’re rounding to. We have to nest the if() statements so the number never gets run through the split() function if it doesn’t contain a decimal point.

Black is the outer if() of the outer if(), it evaluates (pink) whether there’s a dot in the number (convert to string, look for a dot with contains()). I chose to wrap a not() around it so the false value is where we go on to count the number of decimal places, but it isn’t strictly necessary. The true value (if it doesn’t contain a dot), is the raw variable.

The inner if() of the outer if() then splits the floating point input and gets the length of the last part of it and returns true on lessOrEquals() to 2. So if there are two or fewer characters after the dot, then return true. Again the true value is the raw variable (this is why I used not() earlier).

The false value is ‘inner if()’, and it’s here that the entire inner if statement would go.

The whole expression is as follows:

if(
	not(
		contains(
			string(
                              variables('var_float')
                              ),
			'.')
		),
		variables('var_float'),
		if(
			lessOrEquals(
				length(
					last(
						split(
							string(
								variables('var_float')
							),
							'.'
						)
					)
				),
				2
				),
			variables('var_float'),
			float(
				if(
					contains('56789',
					substring(
						last(
							split(
								string(
									variables('var_float')
									),'.'
								)
							),
							2,1
						)
					),
					concat(
						first(
							split(
								string(
									add(
									variables('var_float'),
									0.01)
									),
								'.'
								)
							),
						'.',
						substring(
							last(
								split(
									string(
										add(
											variables('var_float'),
										0.01
										)
									),
								'.'
								)
							),
							0,2
						)
					),
					concat(
						first(
							split(
								string(
									variables('var_float')
									),
									'.')),
						'.',
						substring(
							last(
								split(
									string(
										variables('var_float')
										),
									'.'
								)
							),
							0,2
							)
						)
					)
				)
			)
		)

Or if you were to write this from scratch in the expression builder, it would look like this (528 characters!):

if(not(contains(string(variables('var_float')),'.')),variables('var_float'),if(lessOrEquals(length(last(split(string(variables('var_float')),'.'))),2),variables('var_float'),float(if(contains('56789',substring(last(split(string(variables('var_float')),'.')),2,1)),concat(first(split(string(add(variables('var_float'),0.01)),'.')),'.',substring(last(split(string(add(variables('var_float'),0.01)),'.')),0,2)),concat(first(split(string(variables('var_float')),'.')),'.',substring(last(split(string(variables('var_float')),'.')),0,2))))))

You can adjust the numbers to your own needs. Wherever there’s a 2 above, substitute that with the number of decimal places you want to round to, and where you see 0.01 in the add() function, adjust to your needs (e.g 0.001 for 3 decimals, 0.1 for one decimal etc).

I hope someone finds this blog post useful. Please feel free to leave comments if you wish.