Flow fun with number formatting – adding separators

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 🙂

Thanks for listening – don’t forget to leave comments below or get in touch with me directly if you’d like to chat about the content posted here or anything to do with the Power Platform – I’m a Business Applications speaker and evangelist with a clear focus on delivering real business value from technology. I speak at least once a month so please find me at an event and #LetsGetCoffee

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s