Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
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 usin...
Need Help
  • ManuelBolz's avatar
    ManuelBolz
    2 years ago

    Hello Anonymous,

     

    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

  • Anonymous's avatar
    Anonymous
    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}})

     

     

Anonymous's avatar
Anonymous
Not applicable
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 Anonymous,

     

    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