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 Owner System ...
Need Help
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