cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

How to format list item number as currency?

I want to transform the listitems Stukprijs en AanschafTotaal to a currency number for example € 12,98

 

I thought this can be done by placing the fields Stukprijs a formatnumber expression, but I cannot select Stukprijs to the formatnumber expression.

 

Any ideas? 

 

Aantekening 2020-03-11 170858.png

 
2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Dual Super User III
Dual Super User III

Re: How to format list item number as currency?

You should be able to use something like this

formatNumber(variables('TargetNumber'),'$#.00')

 Just substitute your dynamic content and the appropriate currency symbol



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

View solution in original post

Highlighted
Dual Super User III
Dual Super User III

Re: How to format list item number as currency?

Hi again
There is a typo on the expression
formatNumber(item()?['Stukprijs'],'$#.00')
TRy now


Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



View solution in original post

16 REPLIES 16
Highlighted
Dual Super User III
Dual Super User III

Re: How to format list item number as currency?

Hi!

 

There is a new action block to simplify format currency...

https://flow.microsoft.com/es-es/blog/simplified-number-formatting/

... but your flow is based on a 'Get items' output right? Not 100% sure, anyway, your topic should be a good reason for me to start learning Dutch 😅

If my assumption is right, and this new action block is a valid workaround, then you will probably need to use an Apply to Each to  iterate all over the items. Or, use a expression based on its equivalent WDL function in your Select mapping table

Hope this helps



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted
Dual Super User III
Dual Super User III

Re: How to format list item number as currency?

You should be able to use something like this

formatNumber(variables('TargetNumber'),'$#.00')

 Just substitute your dynamic content and the appropriate currency symbol



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

View solution in original post

Highlighted
Frequent Visitor

Re: How to format list item number as currency?

Unfortunately 

Aantekening 2020-03-11 175058.pngAantekening 2020-03-11 175159.png

 

Highlighted
Frequent Visitor

Re: How to format list item number as currency?

Yes it's based on a get items output. But isn't there a way to transform the data in the select block directly with an expression? 

Highlighted
Dual Super User III
Dual Super User III

Re: How to format list item number as currency?

Oops!

The error you just shared is because of using a variable in your expression that does not exist called 'Stukprijs'.

Following @Pstork1 recommendation, I would use instead

 

 

formatNumber(item('Stukprijs'),'$#.00')

 

 

If this also fails, please hover the mouse over the first SP dynamic content you assigned at the beginning to your Select 'price' column, and share the screenshot so we can identify the Stukprijs internal name in your Sharepoint list ... since sometimes the SP column internal name does not match with the one shown by the Flow User Interface

Thanx!



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted
Frequent Visitor

Re: How to format list item number as currency?

Thanks Efialttes,

 

 Naamloos.png

Highlighted
Dual Super User III
Dual Super User III

Re: How to format list item number as currency?

Hi!

 

Following @Pstork1 recommendation, I would replace currentassigned value

 

item()?['Stukprijs']

 

 

and use this expression instead

 

formatNumber(item()['Stukprijs'],'$#.00')

 

 

Hope this helps

 



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted
Frequent Visitor

Re: How to format list item number as currency?

Aantekening 2020-03-11 180925.png

Highlighted
Dual Super User III
Dual Super User III

Re: How to format list item number as currency?

Hi again
There is a typo on the expression
formatNumber(item()?['Stukprijs'],'$#.00')
TRy now


Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



View solution in original post

Highlighted
Frequent Visitor

Re: How to format list item number as currency?

Thank you very much! That worked!

Highlighted
Dual Super User III
Dual Super User III

Re: How to format list item number as currency?

@Rbovenkamp 

I've seen you marked this topic as solved. Thanks for your kindness!

This way other community members facing this same problem in the future have the chance to find a solution faster.

 

Please note you can mark more than one answer as Solution, even if they come from different users. Why I am mentioning this?

Cause in my opinion @Pstork1  deserves most of the credit for the solution, since 95% of the design comes from him. I just suggested how to adapt it to your current implementation

 

Thanx for making this community so great!

 



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted
Frequent Visitor

Re: How to format list item number as currency?

You're right @Pstork1 is also mentioned as accepted solution.

 

Now I've got another question. I would like to do the same trick for column totals. But it says that this column is retrieved as string instead as float. The column in Sharepoint is a calculated column, which multiplies the amount with the price per item. The column is added as a number column with 2 decimals. How is it possible that this data is retrieved as string in power automate? And how to overcome this? I tried to convert the string data with float(). Unfortunately without result. 

 

 

Aantekening 2020-03-11 184841.pngAantekening 2020-03-11 184740.pngAantekening 2020-03-11 180925.png

 

Highlighted
Frequent Visitor

Re: How to format list item number as currency?

And... @Pstork1 really appreciate your solution. Can you give met some clues about the syntax? I try to understand it, but don't understand this part: 

item()?['Stukprijs'] 

in 

formatNumber(item()?['Stukprijs'],'$#.00')

🙄

Highlighted
Dual Super User III
Dual Super User III

Re: How to format list item number as currency?

@Rbovenkamp 

 

Select iterates through all elements from you input array. Since you assigned 'Get items' output value as Select's input, you are iterating through all items from your Sharepoint List, each SP List item represented as an element of the input array.

 

item() represents current element under evaluation

item()?['Stukprijs'] represents current element Stukprijs column value

 

Select output is also an array.

 

You can also use item() inside 'Apply to each' and 'Filter array' function blocks

Hope this makes sense



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted
Frequent Visitor

Re: How to format list item number as currency?

Nice thank you very much, with your hints I came with this solution to transform the string format into a float:

 

formatNumber(float(item()?['Totaal']),'$#.00')

 

So I understand it correct item()?['Totaal']      item() is the function which puts the column data with a for each. The ['Totaal'] is the coumn selector. But what is the ? and is it possible to give the item() function extra option in between the ()?

Highlighted
Dual Super User III
Dual Super User III

Re: How to format list item number as currency?

Hi again

IN this case the element represented by item() is of type object.

Objects have properties.

And one way in WDL language (which is the programming language behind the graphic editor) to refer to an object's property is by means of the ? operator.

 

You will understand better if you implement the following dummy logic after SElect action block: add 'Apply to each' action block just after your Select action block, assign Select output as its input. Inside Apply to each:

1.- add a dummy Compose action block, assign it as input the following expression:

item()

2.- add a second dummy Compose action block, assign it as input the following expression:

item()?['Totaal']

 

REexecute the flow and inspect content displayed inside 'Apply to each'

Flow_iteration.png

In my example 'Apply to each' performed 92 iterations (one per element from the input array), the browser is currently displaying Dummy Compose outputs from iteration#1, you can click 'Next' to see the particular Dummy compose outputs in iteration #2... and so and so.

Please note Dummy Compose#1 will show... an object! representing the current iteration element from Select array.

Dummy Compose#2 shows value of requested property, also from current iteration element from Select array.

 

If you enjoyed the explanations from my last two replies, you can click on 'Thumbs up' in the one you prefer most, or even in both of them 😉.

Flow_thumbsUp.png

 

Hope this makes sense



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Helpful resources

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

Upcoming Events

Experience what’s next for Power Automate

See the latest Power Automate innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

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