Pass fail count from total count group by

[sam89]

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? 

[ManuelBolz · Accepted solution]

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 · Accepted solution]

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]

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

[ManuelBolz · Accepted solution]

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]

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}})