Group and Count by Problem

[CharlotteCity12]

I need to group by 'garage id' and 'block id' then list with count the Problem Sub Code.. see sample data  with results below:

[vojtechsima]

Hi, @CharlotteCity12,
If you fancy your result in Power Query, here's the code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0ARL+IQFKsTpIAmbGLngETNG1YBUIdyYkgKLFDKQiPFwpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"garage id" = _t, blockid = _t, problem_subcode = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"garage id", Int64.Type}, {"blockid", Int64.Type}, {"problem_subcode", type text}}),
    #"Grouped Rows1" = Table.Group(#"Changed Type", {"blockid", "garage id", "problem_subcode"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows1", "text_formula", each [problem_subcode] &  " ("&Text.From([Count])&")"),
    #"Grouped Rows2" = Table.Group(#"Added Custom", {"garage id", "blockid"}, {{"problem_Chain", each [text_formula]}}),
    #"Extracted Values2" = Table.TransformColumns(#"Grouped Rows2", {"problem_Chain", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
    #"Extracted Values2"

Please note, I tested it on manually put data, you have to modify the source if you want to see it working in your table.

[Samarth_18 · Accepted solution]

Hi @CharlotteCity12 ,

Create a measure like below:-

Measure = 
    VAR _Table = 
        SUMMARIZE(
            'Table',
            [Garage id],
            'Table'[Blockid],
            'Table'[problem_subcode],
            "Count",COUNTROWS('Table')
        )
RETURN
    CONCATENATEX(_Table,[problem_subcode] & " " & "("&[Count]&")",", ")

Output:-

Thanks,

Samarth