Forum Discussion

Antonioclk's avatar
Antonioclk
New Member
3 years ago

Count specific occurrences based on another column

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

OwnerSystemCompliant
JackulysseTRUE
MaryperseusFALSE
JasonneptuneTRUE
RoseachyllesTRUE
JackjupiterFALSE
JackathenaTRUE
MaryaresFALSE
RoseartemisFALSE

 

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?

Need Help

2 Replies

  • adudani's avatar
    adudani
    Memorable Member
    3 years ago

    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

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    3 years ago

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