If you have ever tried to play with dates and numbers in Microsoft Flow, PowerApps or actually any development platform you will know the horror faced by many to the user request “can you just make it look like this…”
So, we had some fun last month with number formatting in Flow and thought I’d share.
User Request – Please add unit separators to numbers of an unknown size but potentially very large numbers. This was a UK request so the separator is a comma.
We looked at various mathematical functions and several online solutions others had published but could not find an elegant way to implement in just a few steps.
We started with an action for every step of the process for all kinds of possible number variations but as the solution needed to be deployed in an already large Flow we needed to make less action extensive.
We finally settled on this elegant little solution…
First we set the value in some variables (manual input of a number for the example) and convert our input to a string to manipulate it.
Then comes the string manipulation bit inside a Do until loop… with this lovely concat function which is basically iterating through the number adding in a separator every 3 digits
concat(if(equals(mod(add(iterationIndexes(‘Do_until’),1),3),0),concat(‘,’,substring(variables(‘MyNumber’),sub(length(variables(‘MyNumber’)),add(iterationIndexes(‘Do_until’),1)),1)),substring(variables(‘MyNumber’),sub(length(variables(‘MyNumber’)),add(iterationIndexes(‘Do_until’),1)),1)),variables(‘NewStringNumber’))
sub(length(variables(‘MyNumber’)),add(iterationIndexes(‘Do_until’),2))
The final variable is to set the iterations to run through on the loop – so this takes the length of our number input as “12345678” so 8 and subtracts 2 as we only want to put the first separator at position 3. In our example then the loop then starts at 6 and proceeds until -1
Then finally we remove any preceding separators in the number as a final tidy up in case of junk and return the formatted value
if(equals(indexOf(variables(‘NewStringNumber’),’,’),0),substring(variables(‘NewStringNumber’),1,sub(length(variables(‘NewStringNumber’)),1)),variables(‘NewStringNumber’))
We could also easily incorporate currency formatting with decimals etc. for a currency based solution, although this is not shown above to keep this post simple and hopefully useful without too much scary math.
Do let me know if you have a Flow pattern for doing this that is more elegant or less of a mind melt – my team and I do love a challenge 🙂
