resolving two decimal point for create csv table action

[Anonymous]

Hi,

In a flow, I am using below actions:

1. Get items from a list

2. create csv table using values from above action with custom headers

3. Create a file in sharepoint with .csv extension and data coming from action 2.

Now I have two number fields: debit and credit and I want to make sure that they show only two decimal places but it does not seem to be working ootb. So I want 3.457 to resolve as 3.46 as an example. Can someone please help me with the formula i can use to limit to 2 decimal places.

thanks.

[edgonzales]

@Anonymous 

Hi there.  So, I think the easiest way would be to multiply it by 100, convert it to an integer (to round it to a whole number), then divide that by 100.

I found this article with the expression detailed out.   https://grootcrm.net/tip-microsoft-flow-how-to-round-a-decimal/

Keep us posted.

-Ed

If you liked this reply, please give it a thumbs up! If this reply has answered your question or resolved your challenge, please consider marking it as a Solution. This helps other users find it more easily via search.

[DeepakS]

Hi @Anonymous 

Here is another option: you can use the following expression that will roundup decimal up-to two decimal point :

if(contains('56789', substring(string(variables('Number')),add(length(split(string(variables('Number')),'.')[0]),3),1)),substring(string(add(variables('math'),0.01)),0,add(length(split(string(variables('Number')),'.')[0]),3)),substring(string(variables('math')),0,add(length(split(string(variables('Number')),'.')[0]),3)))

variables('Number') - is type float number that i am using here.

add(length(split(string(variables('Number')),'.')[0]),3) will round up for two , you can change this for other round up requirement:

For one decimal:

add(length(split(string(variables('Number')),'.')[0]),2)

For three decimal:

add(length(split(string(variables('Number')),'.')[0]),4)

——————————————————
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up. 

[yashag2255]

Hi @Anonymous 

You can also take a look at this flow I have submitted to the flow cookbook that appropriately rounds of decimals. 

https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Round-off-to-two-decimal-places-using-Power-Automate/td-p/440451 

Also, you could use the number format action for this case. 

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[Anonymous]

Thanks @DeepakS , however I am not following the variable approach here, what exactly is the math variable used for in expression?

I am dealing with get items so not going to use variables, I am only concerned with item()['Debit']), can you please help in regards to that, thanks in advance. 

[Anonymous]

yes thanks for sharing @yashag2255 , however I am not clear in regards to using it with item()['Debit']) as my use case does not involve variables.

[Anonymous]

Thanks @edgonzales , can you please help me with the formula in regards to using item()['Debit'])? I am not leveraging variables at all.

[edgonzales]

@Anonymous 

You bet.  You can simply replace that bit with the dynamic value that you want to round.

Maybe post a pic of what you've built so far and I can try to mimic the same context.

-Ed

[Anonymous]

 

Surething, please see above, I am concerned with Debit field, create file action simply creates a csv file in one of the sp libraries with data coming from create csv table action.

[edgonzales]

@Anonymous 

Ok, so, I think @yashag2255 had the easiest solution.  I forgot all about this action (it's relatively new), but right after your "Get Items" step, add the action step "Format Number"  (see below)

Where mine shows "Outputs", you would instead drop in the dynamic value that corresponds to the "Debit" value in your SP list.

Then, in your "Create CSV Table" for the Debit field, scroll until you see the dynamic value output of your Format Number step.

Everything else can stay the same.

Hope that helps.

-Ed-

[DeepakS]

Hi @Anonymous 

You can try the solution  what @edgonzales and @yashag2255  suggested. 

Here is updated expression what i suggested (updated for you column name)

if(contains('56789', substring(string(items('Apply_to_each')['Debit']),add(length(split(string(items('Apply_to_each')['Debit']),'.')[0]),3),1)),substring(string(add(items('Apply_to_each')['Debit'],0.01)),0,add(length(split(string(items('Apply_to_each')['Debit']),'.')[0]),3)),substring(string(items('Apply_to_each')['Debit']),0,add(length(split(string(items('Apply_to_each')['Debit']),'.')[0]),3)))

——————————————————
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up. 

[Anonymous]

Thanks again @DeepakS , I tried using below.

if(contains('56789',
substring(string(item()?['Debit']),
add(length(split(string(item()?['Debit']),'.')[0]),3),1)),
substring(string(add(item()?['Debit'],0.01)),0,
add(length(split(string(item()?['Debit']),'.')[0]),3)),
substring(string(item()?['Debit']),0,
add(length(split(string(item()?['Debit']),'.')[0]),3)))

but getting below:

[Anonymous]

Thanks @edgonzales , never heard about such action, thanks for mentioning, good to know that it exists. I tried to use it, see below. 

Apply to each gets added automatically to format number which makes sense due to get items, however though in create csv table, I am not able to specify dynamic value for Debit header, thoughts? 

[DeepakS]

Hi @Anonymous ,

Possible reason for this is either your input number is not Float(contain a decimal) or empty . I would say add a check to see if number is float or not (if contains '.').

——————————————————
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up. 

[Anonymous]

@DeepakS  thanks again. Can you check if below is correct based on what you recommended?

if(contains('.'),if(contains('56789',
substring(string(item()?['Debit']),
add(length(split(string(item()?['Debit']),'.')[0]),3),1)),
substring(string(add(item()?['Debit'],0.01)),0,
add(length(split(string(item()?['Debit']),'.')[0]),3)),
substring(string(item()?['Debit']),0,
add(length(split(string(item()?['Debit']),'.')[0]),3))))

Getting error below. 

[DeepakS]

Hi @Anonymous 

Try This:

if(contains(string(item()?['Debit']),'.'),if(contains('56789',
substring(string(item()?['Debit']),
add(length(split(string(item()?['Debit']),'.')[0]),3),1)),
substring(string(add(item()?['Debit'],0.01)),0,
add(length(split(string(item()?['Debit']),'.')[0]),3)),
substring(string(item()?['Debit']),0,
add(length(split(string(item()?['Debit']),'.')[0]),3)))),string(item()?['Debit']))

Note: not at my computer now so used phone to reply,will recheck once back.

——————————————————
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up. 

[Anonymous]

@DeepakS  thank you again for the help. 

However its still not working, getting below. Any help?

[DeepakS]

Hi @Anonymous 

Could you please share the number for which it failing or it failing for every number? also if possible share your flow screen capture too.

Regards,

[Anonymous]

Sure, thanks again. 

Noticing weird issues: it worked well for Amount 9.775 and resolved it to 9.78 but failed for 17.575

[DeepakS]

Hi @Anonymous 

Interesting ,because i tried with same number it works for me. could you please recheck and confirm its failing for this number not other.

i may suggest you to rather break the expression into it to more smaller expression now as its a bit complex to test, once everything works you can combine it back.

——————————————————
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.