Forum Discussion

nick011's avatar
nick011
Frequent Visitor
2 years ago
Solved

Custom Decimal Places

Hello, Looking for a Power Query function similar to MySQL Format() - formats a number to a format like "#,###,###. ##", rounded to a specified number of decimal places, then it returns the result as...
Need Help
  • nick011's avatar
    nick011
    2 years ago

    Thank you. I actually got what was needed from just this bit 

     

    Number.Round([PrimaryValue],[DecimalPlaces]))

bhanu_gautam's avatar
bhanu_gautam
Super User
2 years ago

nick011 , You can use below mentioned M-Code in Custom column to modify format

 

FormattedValue = Text.FromNumber([Value], "0." & Text.Repeat("0", [DecimalPlaces]))

 

Value is the numeric value to be formatted

Decimal Places is the number of decimal places to format the value to

nick011's avatar
nick011
Frequent Visitor
2 years ago

Thanks for your quick response. However, the following error message was received.

 

"Expression.Error: The name 'Text.FromNumber' wasn't recognized. Make sure it's spelled correctly"

 

 

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    2 years ago

    Try this one 

     

    FormattedValue =
    let
    value = [Value],
    decimalPlaces = [DecimalPlaces],
    roundedValue = Number.Round(value, decimalPlaces),
    textValue = Text.From(roundedValue),
    decimalPointIndex = Text.PositionOf(textValue, "."),
    formattedValue = if decimalPointIndex = -1 then
    textValue & "." & Text.Repeat("0", decimalPlaces)
    else
    textValue & Text.Repeat("0", decimalPlaces - (Text.Length(textValue) - decimalPointIndex - 1))
    in
    formattedValue