Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
sam89
Frequent Visitor

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? 

2 ACCEPTED SOLUTIONS
ManuelBolz
Responsive Resident
Responsive Resident

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

View solution in original post

sam89
Frequent Visitor

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

sam89_0-1718096522525.png

Below is the table after grouping

sam89_1-1718096598848.png

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_2-1718096770342.png

 

 

View solution in original post

4 REPLIES 4
sam89
Frequent Visitor

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

sam89_0-1718096522525.png

Below is the table after grouping

sam89_1-1718096598848.png

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_2-1718096770342.png

 

 

ManuelBolz
Responsive Resident
Responsive Resident

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.

ManuelBolz_0-1718097533943.png

 


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
Responsive Resident
Responsive Resident

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

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

sam89_3-1718097002150.png

 

Below is the table after grouping

sam89_4-1718097002341.png

 

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.