Forum Discussion
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 a string.
I have a value column, and a column that states how many decimal places that row value should have.
I need a function that applies that number of decimal places.
Thank you. I actually got what was needed from just this bit
Number.Round([PrimaryValue],[DecimalPlaces]))
5 Replies
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
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"
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
Thank you. I actually got what was needed from just this bit
Number.Round([PrimaryValue],[DecimalPlaces]))
That's great
