add comma separator to numbers

[M_nwadibia]

I have a requirement to add (,) separator to 2 digit number or 3 as I have in the screen shot below. I need an expression to achieve this. Any help will be appreciated

[v-xiaoyan-msft]

Hi @M_nwadibia , 

Please try this in Power Query editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxmDSxBRMmZlbKMXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Column1", "Column1 - Copy"),
    #"Split Column by Position" = Table.ExpandListColumn(Table.TransformColumns(Table.TransformColumnTypes(#"Duplicated Column", {{"Column1 - Copy", type text}}, "en-US"), {{"Column1 - Copy", Splitter.SplitTextByRepeatedLengths(1), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1 - Copy"),
    #"Grouped Rows" = Table.Group(#"Split Column by Position", {"Column1"}, {{"Count", 
each Text.Combine( [#"Column1 - Copy"],","), type nullable number}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Grouped Rows",{{"Count", type text}})
in
    #"Changed Type2"

You may also try measure like this :

Measure =
VAR _selvalue =
    SELECTEDVALUE ( 'Table'[Column1] )
RETURN
    IF (
        LEN ( _selvalue ) > 1,
        LEFT ( _selvalue, 1 ) & ","
            & RIGHT ( _selvalue, 1 ),
        _selvalue
    )

(If you have more than 2 multi-digits, then this measure may not always meet your needs)

Best regards.
Community Support Team_ Caitlyn

[M_nwadibia]

This is in Reporting service and not Power BI. I need an expression in reporting service to achieve this.

[M_nwadibia · Accepted solution]

I used the expression below to solve the comma requirements. I got the hint from the link below

=Replace(Replace(Fields!field1.Value,"1","a"),"0","b")

=Replace(Replace(Fields!Field1.Value,"Test","Test A"),"Test A B","Test C")

https://stackoverflow.com/questions/7608128/is-it-possible-to-use-one-replace-function-to-replace-mu...