Count specific occurrences based on another column

[Antonioclk]

Hi i have a table with 3 columns:

• Owner - name of the system owner (string)
• System - name of the system (string)
• Compliant - value of compliancy (boolean)

something like this

Owner	System	Compliant
Jack	ulysse	TRUE
Mary	perseus	FALSE
Jason	neptune	TRUE
Rose	achylles	TRUE
Jack	jupiter	FALSE
Jack	athena	TRUE
Mary	ares	FALSE
Rose	artemis	FALSE

i need to have a column showing the TOT number of system for each owner, and the Count of TRUE occurrences for each owner. 

For instance, i need to know that jack has 3 systems and has 2 compliant system, so i can obtain the % of compliancy.

how can i do that?

[wdx223_Daniel]

NewStep=let a=Table.Group(PreviousStepName,"Owner",{"n",each {Table.RowCount(_),List.Count(List.Select([Compliant],each _="TRUE"))}}) in #table(Table.ColumnNames(PreviousStepName)&{"SystemCount","CompliantCount"},Table.ToList(PreviousStepName,each _&a{[Owner=_{0}]}[n]))

[adudani]

hi @Antonioclk ,

create a blank query and copy and paste the following code in the advanced editor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kpMzlbSUSrNqSwuTgUyQoJCXZVidaKVfBOLKoH8gtSi4tTSYiDLzdEnGCLllVicnwcUyUstKCnNQ9EVlA82JTE5ozInJ7UYWQpqU1ZpQWZJahGagWCpxJKM1LxELI5ILEpFdQHMmqKS1NxMJKlYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Owner = _t, System = _t, Compliant = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Owner", type text}, {"System", type text}, {"Compliant", type logical}}),
    #"Compliance Count" = Table.AddColumn(#"Changed Type", "Compliance Count", each if Text.Start(Text.From([Compliant], "en-CA"), 1)="t" then 1 else 0),
    #"Grouped Rows" = Table.Group(#"Compliance Count", {"Owner"}, {{"Count", each _, type table [Owner=nullable text, System=nullable text, Compliant=nullable logical, Compliance Count=number]}, {"Compliance Count", each List.Sum([Compliance Count]), type number}}),
    #"System Count" = Table.AddColumn(#"Grouped Rows", "System Count", each Table.RowCount([Count])),
    #"% Compliance" = Table.AddColumn(#"System Count", "% Compliance", each [Compliance Count]/[System Count]),
    #"Changed Type1" = Table.TransformColumnTypes(#"% Compliance",{{"% Compliance", Percentage.Type}})
in
    #"Changed Type1"

Output:

You can remove the count column if not required. it is for validation purposes here