Format Extracted List To Show as Currency (Or Two Decimal Number)

[LarsAustin]

Hi,

How can i format the list that i extracted through List.Select to a currency format? See below M code and output.

My desired result is for each of the value in row under column Cost Per Unit to be formatted as Currency or Fixed Decimal.  Like:

Cost Per Unit

1.31; 0.50; 0.45; 0.46

Thank you

Jojemar

[ronrsnfld · Accepted solution]

If you want to have your list contain values with trailing decimals, as you show, you will need to convert the Number to a text string.

One way to do that is with List.Transform...Number.ToText which will both round the number and show it with two decimals.

List.Transform(decimalList, each Number.ToText(_,"0.00"))

Original

Results

[LarsAustin]

Exactly what I need.

Thanks so much @ronrsnfld !

Jojemar

[ronrsnfld]

One thing that I just noticed.

There are a number of different rounding methods (see this Wikipedia article)

Number.ToRound, by default, uses the so-called "round half to even" method (as does Excel VBA)

The rounding produced by the text function uses, I believe, the "round half away from zero" method (as does regular Excel).

In comparing the two, the Number.Round rounding method can be set explicitly as an optional argument.

So if you wanted to use a particular rounding method other than the default, you could probably round first, then format as text with two decimals.

[watkinnc]

Whaaaaaaat?!?!

That is pretty darn slick.

--Nate

[watkinnc]

I suppose the proper solution would involve List.TransformMany, but I've not yet used it. But you can probably wrap the whole List.Transform in another List.Transform, like

each List.Transform(List.Transform(List.Select([All][Cost Per Unit],  each _ > 0), Currency.From), Number.Round(_, 2)))

--Nate

[LarsAustin]

Hi Nate,

I tried your suggestion above but it gives an error:

I'm happy with the previous solution. Not being able to round to 2 decimals is not a biggie for my requirements for now. But i really appreciate you putting the time and effort to provide the desired outcome to my problem.

Big thanks.

Jojemar

[watkinnc]

Sorry about that! Replace Currency.Type with Currency.From.

--Nate

[LarsAustin]

Hi Nate,

Thanks for that. I get a much better result than the original outcome but how can this be rounded further into 2 decimals?

Thanks

Jojemar

[watkinnc]

Have you tried wrapping the List.Select with List.Transform like:

 each List.Transform(List.Select([All][Cost Per Unit],  each _ > 0), Currency.Type)))

--Nate

[LarsAustin]

Hi Nate,

I tried your recommended solution but it gives an error as below:

Thanks

Jojemar