Filter by number of decimal places

[Anonymous]

I have a large table with a number of values in a field that have 3-6 decimal places - I would like to see/extract these rows only. Is there a way I can do this using filters in Power BI Desktop?

[v-jiascu-msft]

Hi @Anonymous,

Could you please mark the proper answer if it's convenient for you? That will be a help to others.

Best Regards!
Dale

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

Maybe you could try this formula as a calculated column. Please test it before using it.

Decimal3to6 =
VAR NumOfDecimal =
    IF (
        [Column1] = TRUNC ( [Column1] ),
        0,
        LEN ( [Column1] ) - LEN ( TRUNC ( [Column1] ) )
            - 1
    )
RETURN
    IF ( NumOfDecimal >= 3 && NumOfDecimal <= 6, 1, 0 )

Best Regards!

Dale

[GilbertQ]

Hi @Anonymous

The way I would go about it is to go into the Query Editor and do the following:

• Duplicate the column that has got the decimal places.
• Then split this column by the full stop "." delimiter
• Make sure that the data type for this column is set to Text. 
• I would then transform this column to Length (This will give you the amount of chacters in each Column). 
• I would then create a conditional column where it would say where the Column is < 4 then "Less than 3" else "Between 3 and 6"
• Then delete all the columns that were created except for this last Conditional column.
• Then Close and Apply your data.

Then in your report view you could use this as filter or slicer in your data.