The madness with decimal places for Power Automate

We recently had a request from a client to produce a document from Power Automate which was formatted in a way that could go straight out to their clients which contained some prices for them which were calculated in CRM in a non-standard way. The calculation generally worked except the process that was returning them couldn’t use a currency field, it had to return a decimal value instead.

On the surface, this didn’t present too much of a problem, we’d simply add in the currency symbol before the value and that should appear fine in the document. However, when it came to testing, we were finding that the document was not only filled with sensible values such as £4.52 and €0.54 but some not-very-sensible values such as £6, $7.2, €1.25474882. Whilst these are technically correct, they didn’t look great. As these values were being called from multiple parts of the Flow, and Flow does not have a rounding function, I decided to write a child Flow that could be called again and again that would be passed a numeric value along with the number of decimal places needed and it would return a value in formatted text.

Before I get into the details of how I solved this seemingly-simple-yet-incredibly-horrific problem, it is very frustrating that there isn’t an expression in Power Automate that does this for us. We have similar things in other pieces of software or, at the very least, some tools that make it reasonably easy to do.

The Flow itself

In some of my blogs, I’ve detailed the steps needed to solve the problem. Here, I won’t be able to do this; the Flow is just too massive. As an illustration of this, here’s a snapshot of the whole thing fully expanded out:

The Challenges

As I say, I’m not going to go into the full details of this, just some of the interesting challenges faced. Looking at the steps here, a good number of them were converting figures from one data type to another because Flow is really picky with this. I won’t go into details with this here, just the main logic I used.

Getting the main multiplier 

Because this is completely back to basics, the Flow is receiving two numbers; the number to convert and the number of decimal places. The first challenge is to add the correct number of 0’s to the end of the number so we needed a multiplier before conversion. Converting 4.52 and 4.521 to 2 decimal places should yield the same value – the best way to do this would be to multiply both numbers by 100 and then drop any decimal places remaining (so 4.52 becomes 452 and 4.521 becomes 452.1 and so on). What if it was 3 decimal places or maybe none at all?

What we need here is a power – another feature not available in Power Automate. So 10 to the power of the number of decimal places should give us our multiplier (10² = 100, 10³ = 1000, 10⁴ = 10000 and so on). To write this, I had to declare a counting variable and then do a ‘loop until’ the counter got to the number of decimal places. With each pass, I had to add 1 to the counter and then multiply a variable by 10. This was time-consuming and fiddly but it did work.

Dropping the extra figures

Another annoying feature of Flow is that the conversion of a decimal to a whole number completely ignores any decimal places so, at the point of conversion, it chops rather than rounds. This meant that 341.2 became 341 which is correct but 23.8 became 23 which is not what we want; 23.8 should become 24. The answer I found didn’t seem very satisfactory but the logic did seem to work – the answer was simply to add 0.4999 to the number and then do the chop. So, repeating the above example, 341.2 first became 341.6999 before losing its decimals and becoming 341 while 23.8 first became 24.2999 before the decimals went and it became 24.

Adding the decimal point

This is where the whole process had to branch. I worked out that I needed to treat numbers less than 1 differently than those equal to or greater than 1 – there was no catch-all here.

For numbers greater than 1, it wasn’t too bad. Just get the length of the multiplied value then count back from the end the required number of decimal places and then insert a decimal point. There were challenges here but this does cover the basic logic.

For numbers less than 1, it was a case of working out how many leading 0’s to add. This was just the difference between the length of the number returned from the first stage and the number of decimal places needed. So, 0.01 to 3 decimal places would see 0.01 multiplied by 1000 (which is 10) then we have the number of decimal places needed (3) minus the length of the previous value (2) so we need a single 0 adding at the beginning so 10 becomes 010. For this, I used the ‘loop until’ again to add the required number of 0’s. Once done, putting ‘0.’ at the beginning solved this so, with the example above, 010 then becomes 0.010 which, I think you’ll agree, is 0.01 to 3 decimal places.

A couple more twists

At this point, I thought I had it and started doing more testing. But I realised that, sometimes, null values were being sent which broke the Flow completely. This was fairly easy to solve; add a condition that checks if the passed value contains data and, if it doesn’t, assign zero to it. It then went down the ‘less than 1’ route just like everything else.

Then there was one further surprise; I did a test that involved a credit (ie a negative number). This really messed up the logic and, for this part, I cheated a little bit. Right at the start, I took the number to be converted and put it through a further condition – is it less than 0. If it was, I multiplied it by -1, effectively turning it into a positive value and running it through the same logic then, at the end, add the minus symbol back into the start of the number before returning the value back.

Conclusions 

Despite this seeming like a straightforward task that I could accomplish in an hour or two, it turned into something so much more complicated. It wasn’t too hard to get it to cope with a scenario but generally, each one led on to another scenario, and so on. So is the Flow now working? I think the best I can say is: kind of. It mostly works but more testing is needed.

An example of this was highlighted during the last wave of testing. The case in question was 0.143 which I wanted to 2 decimal places. Interestingly, I’d found that I’d run this test 3 times and, for 2 of these, it gave me 0.14 as the output which is absolutely what I expected. However, the other test gave me 0.00000143* as the output which is just nonsense. Same inputs. Same process. Different outputs.

*That’s not strictly correct – there was a MASSIVE number of 0’s but hopefully you get the idea.

I trained as a computer scientist and the idea behind any science is that you should be able to repeat an experiment and, as long as you use all the same conditions at the start, you should get the same result at the end. Clearly, this hasn’t happened here and I find it very conflicting, as a scientist, to call a system that gives such strange results a ‘science’. There’s a lot more that I could argue about here, but that’s a completely different blog.