cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Prodigy
Post Prodigy

resolving two decimal point for create csv table action

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Post Prodigy
Post Prodigy

Re: resolving two decimal point for create csv table action

Thanks to all for helping and sharing your ideas @v-bacao-msft  @DeepakS  @yashag2255  @edgonzales . 

After spending some time and research, I ended up getting the solution, used formula below as an expression and it worked great. Below expression limits to two decimal points and rounds up great as well.

 

formatNumber(item()?['Amount'],'N','en-US')

View solution in original post

25 REPLIES 25
Highlighted
Super User II
Super User II

Re: resolving two decimal point for create csv table action

@dmistry 

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.

Highlighted
Super User II
Super User II

Re: resolving two decimal point for create csv table action

Hi @dmistry 


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. 
 
Highlighted
Dual Super User III
Dual Super User III

Re: resolving two decimal point for create csv table action

Hi @dmistry 

 

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-Po... 

 

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!

 

Highlighted
Post Prodigy
Post Prodigy

Re: resolving two decimal point for create csv table action

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. 

Highlighted
Post Prodigy
Post Prodigy

Re: resolving two decimal point for create csv table action

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.

Highlighted
Post Prodigy
Post Prodigy

Re: resolving two decimal point for create csv table action

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

Highlighted
Super User II
Super User II

Re: resolving two decimal point for create csv table action

@dmistry 

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

Highlighted
Post Prodigy
Post Prodigy

Re: resolving two decimal point for create csv table action

 Capture.JPG

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.

Highlighted
Super User II
Super User II

Re: resolving two decimal point for create csv table action

@dmistry 

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)

20200210.PNG

 

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-

 

 

Highlighted
Super User II
Super User II

Re: resolving two decimal point for create csv table action

Hi @dmistry 

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. 

 

Highlighted
Post Prodigy
Post Prodigy

Re: resolving two decimal point for create csv table action

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:

Capture.JPG

Highlighted
Post Prodigy
Post Prodigy

Re: resolving two decimal point for create csv table action

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

Capture.JPG

 

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? 

Highlighted
Super User II
Super User II

Re: resolving two decimal point for create csv table action

Hi @dmistry ,

 

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. 

Highlighted
Post Prodigy
Post Prodigy

Re: resolving two decimal point for create csv table action

@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. 

Capture.JPG

Highlighted
Super User II
Super User II

Re: resolving two decimal point for create csv table action

Hi @dmistry 

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. 
Highlighted
Post Prodigy
Post Prodigy

Re: resolving two decimal point for create csv table action

@DeepakS  thank you again for the help. 

 

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

Capture.JPG

Highlighted
Super User II
Super User II

Re: resolving two decimal point for create csv table action

Hi @dmistry 

 

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,

 

Highlighted
Post Prodigy
Post Prodigy

Re: resolving two decimal point for create csv table action

Sure, thanks again. 

 

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

 

1.JPG2.JPG

Highlighted
Super User II
Super User II

Re: resolving two decimal point for create csv table action

Hi @dmistry 

 

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. 

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors
Users online (5,413)