Forum Discussion

sam89's avatar
sam89
Frequent Visitor
2 years ago
Solved

Pass fail count from total count group by

HI

I have 2 columns device name and device status. The device status has values pass,fail and app exception. I have to group devices based on number of occcurance of each device for which i did using a groupby in power query using count of device status. For eg, i have device A - it has 2 pass status and 4 fail status, i grouped the device occurance by counting the device status of the particular device. Now I need the count of number of pass, number of fail for each device as well. How to do groupby for this? 

Need Help
  • ManuelBolz's avatar
    ManuelBolz
    2 years ago

    Hello sam89,

     

    Do you perhaps have a screenshot or sample data so that the community can get a better idea of ​​your problem?

    Or maybe this little example will help you:

    let
    //Replace the first Step with your Data Table
    //Source = YourDataTable
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kksKMkvUNJRKkgsLlaK1YlWCk7NK84vQhZxySwuyEmsRBZyTgSpSEvMzAFznTKzU5GlAzLy81KRFfhmJiNzPVITUwpQ1MQCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Device Name" = _t, #"Device Status" = _t]),

    Type = Table.TransformColumnTypes(Source,{{"Device Name", type text}, {"Device Status", type text}}),
    Grouped = Table.Group(Type, {"Device Name", "Device Status"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    Pivoted = Table.Pivot(Grouped, List.Distinct(Grouped[Device Status]), "Device Status", "Count", List.Sum)
in
    Pivoted


    Best regards from Germany
    Manuel Bolz

    🟦Follow me on LinkedIn
    🟨How to Get Your Question Answered Quickly
    🟩Fabric Community Conference
    🟪My Solutions on Github

  • sam89's avatar
    sam89
    2 years ago

    Please find the below screen shots , this is the original table

    Below is the table after grouping

    The transformation i used to group device name based on device status da_status is as below.

    = Table.Group(dbo_vw_ehas_dashboard, {"device_name", "da_status"}, {{"device_dastatus", each Table.RowCount(_), Int64.Type}})

     

     

4 Replies

  • ManuelBolz's avatar
    ManuelBolz
    Responsive Resident
    2 years ago

    Hello sam89,

     

    Do you perhaps have a screenshot or sample data so that the community can get a better idea of ​​your problem?

    Or maybe this little example will help you:

    let
    //Replace the first Step with your Data Table
    //Source = YourDataTable
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kksKMkvUNJRKkgsLlaK1YlWCk7NK84vQhZxySwuyEmsRBZyTgSpSEvMzAFznTKzU5GlAzLy81KRFfhmJiNzPVITUwpQ1MQCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Device Name" = _t, #"Device Status" = _t]),

    Type = Table.TransformColumnTypes(Source,{{"Device Name", type text}, {"Device Status", type text}}),
    Grouped = Table.Group(Type, {"Device Name", "Device Status"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    Pivoted = Table.Pivot(Grouped, List.Distinct(Grouped[Device Status]), "Device Status", "Count", List.Sum)
in
    Pivoted


    Best regards from Germany
    Manuel Bolz

    🟦Follow me on LinkedIn
    🟨How to Get Your Question Answered Quickly
    🟩Fabric Community Conference
    🟪My Solutions on Github

    • sam89's avatar
      sam89
      Frequent Visitor
      2 years ago

      Please find the below screen shots , this is the original table

       

      Below is the table after grouping

       

      The transformation i used to group device name based on device status da_status is as below.

      = Table.Group(dbo_vw_ehas_dashboard, {"device_name", "da_status"}, {{"device_dastatus", each Table.RowCount(_), Int64.Type}})

  • sam89's avatar
    sam89
    Frequent Visitor
    2 years ago

    Please find the below screen shots , this is the original table

    Below is the table after grouping

    The transformation i used to group device name based on device status da_status is as below.

    = Table.Group(dbo_vw_ehas_dashboard, {"device_name", "da_status"}, {{"device_dastatus", each Table.RowCount(_), Int64.Type}})

     

     

    • ManuelBolz's avatar
      ManuelBolz
      Responsive Resident
      2 years ago

      Hello sam89,

       

      I think the following code could be your solution:

       

      let
    //Replace the first Step with your Data Table
    //Source = YourDataTable
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bVG7DsIwDPwVlJkhz1JGNw0FqS8lER0qJh4SC3Rg4PNxW1WtQic7Z5/tu7Qt0WWVHIu9IFsCXbcx3+u9+zzfL3zH5LJtye3xwPwApxwDHyBwRWq86Dk1OIdBDnjOVV3lNJr72YCfbdbjjK5tGUfmicEWQakMyWBNBlpwqcJKY3VSnRSfzxjx1Paz1G5l2XRPUnvGQl7jTMQ5lX+F1OmiArZYNPkA3peUxqFBGg0CVBMSvHGecaGiFQL+AltIlAspeBRdEaOGlgJSfbRy6QIWLj8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [device_name = _t, da_status = _t, device_dastatus = _t]),
    Type = Table.TransformColumnTypes(Source,{{"device_dastatus", Int64.Type}}),

    Grouped = Table.Group(Type, {"device_name", "da_status"}, {{"Count", each List.Sum([device_dastatus]), type nullable text}}),
    Pivoted = Table.Pivot(Grouped, List.Distinct(Grouped[da_status]), "da_status", "Count", List.Sum)
in
    Pivoted


      If my post helped you, please give me a 👍kudos and mark this post with Accept as Solution.

       


      Best regards from Germany
      Manuel Bolz

      🟦Follow me on LinkedIn
      🟨How to Get Your Question Answered Quickly
      🟩Fabric Community Conference
      🟪My Solutions on Github