Forum Discussion

Amazing_Random's avatar
Amazing_Random
Icon for Helper I rankHelper I
3 years ago
Solved

Formatting the first three significant digits

So I have been required to format the first three signifcant digits in a report that has values ranging from 1000s to as low as 0.0000s

I'm trying to find a way to format it with if statements but they are not working properly.

 

For example, I need the values to be like this:

1247.87 -> 1248

193.02 -> 193

23.59 -> 23.6

7.83 -> 7.83

0.009 -> 0.01

Need Help

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Amazing_Random Try:

    Column = 
  VAR __Value = [Column1]
  VAR __Length = LEN(TRUNC(__Value) & "")
  VAR __Result = 
    SWITCH(TRUE(),
      __Length >= 3, ROUND(__Value,0),
      __Length = 2, ROUND(__Value,1),
      __Length = 1, ROUND(__Value,2)
    )
RETURN
  __Result
  • ppm1's avatar
    ppm1
    Icon for Solution Sage rankSolution Sage
    3 years ago

    Here's one way to do it in the query editor.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below. The key formula is in the #"Added Custom" step.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlGK1QEzoLSesYkpmGmgB5IFsmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [InputNumber = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"InputNumber", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Number.Round([InputNumber], 3 - (1 + Number.IntegerDivide(Number.Log10(Number.Abs([InputNumber])) , 1)))),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type number}})
in
    #"Changed Type1"

     

     

    Pat