Forum Discussion

LarsAustin's avatar
LarsAustin
Helper I
4 years ago
Solved

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

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

 

 

 

Need Help
  • ronrsnfld's avatar
    ronrsnfld
    4 years ago

    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

     

     

10 Replies

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    4 years ago

    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

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Whaaaaaaat?!?!

      That is pretty darn slick.

       

      --Nate

      • ronrsnfld's avatar
        ronrsnfld
        Super User
        4 years ago

        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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      LarsAustin
      Helper I
      4 years ago

      Hi Nate,

       

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

       

       

      Thanks

       

      Jojemar

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

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

     

    --Nate

    • LarsAustin's avatar
      LarsAustin
      Helper I
      4 years ago

      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

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      LarsAustin
      Helper I
      4 years ago

      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