Forum Discussion

CharlotteCity12's avatar
CharlotteCity12
Microsoft Employee
4 years ago
Solved

Group and Count by Problem

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

 

 

 

Need Help
  • Samarth_18's avatar
    Samarth_18
    4 years ago

    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

2 Replies

  • Samarth_18's avatar
    Samarth_18
    Community Champion
    4 years ago

    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

  • vojtechsima's avatar
    vojtechsima
    Super User
    4 years ago

    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.