CODEWITHSUNDEEP
excelpowerbipowerquerypowerbi-desktopm
d365b
d365b

Reputation: 21

Identify grouped rows based on multiple conditions using PowerQuery

I have such a table

Code Column A Column B Column C Level Column D Column E
1234 Cell 1 Cell 1 Cell 1 25 Cell 1 Cell 1
1234 Cell 2 Cell 2 Cell 2 50 Cell 2 Cell 2
1234 Cell 3 Cell 3 Cell 3 50 Cell 3 Cell 3
1234 Cell 4 Cell 4 Cell 4 75 Cell 4 Cell 4
5678 Cell 5 Cell 5 Cell 5 10 Cell 5 Cell 5
5678 Cell 6 Cell 6 Cell 6 disabled Cell 6 Cell 6
5678 Cell 7 Cell 7 Cell 7 20 Cell 7 Cell 7
5678 Cell 8 Cell 8 Cell 8 100 Cell 8 Cell 8
9090 Cell 9 Cell 9 Cell 9 disabled Cell 9 Cell 9
9090 Cell 10 Cell 10 Cell 10 disabled Cell 10 Cell 10
9090 Cell 11 Cell 11 Cell 11 disabled Cell 11 Cell 11
9090 Cell 12 Cell 12 Cell 12 disabled Cell 12 Cell 12

I have grouped rows by "Code" and grabbed all values, so I ended up with mini tables. I want to identify those codes where the column "Level" in each mini table contains at least a "disabled" and other values.

Referring to the above table I should get as output only code 5678.

I managed to add a custom column "HasDisabled".

How can I filter out the codes that have all "disabled"?

M-code

let
    Source = #"Import",
    #"Sorted Rows" = Table.Sort(Source,{{"Code", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Code"}, {{"Tables", each _, type table [#"Code"=nullable text, #"Column A"=nullable text, #"Column B"=nullable text, #"Column C"=nullable text, #"Level"=nullable text, #"Column D"=nullable text, #"Column E"=nullable text]}}),

     #"Added Custom" = Table.AddColumn(#"Grouped Rows", "HasDisabled", each
    let
        Levels = [Tables][#"Level"], ContainsDisabled = List.Contains(Levels, "disabled", Comparer.OrdinalIgnoreCase)
    in
        ContainsDisabled)
in
    #"Added Custom"

Upvotes: 1

Views: 72

Answers (1)

davidebacci
davidebacci

Reputation: 30174

Try this:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", Int64.Type}, {"Column A", type text}, {"Column B", type text}, {"Column C", type text}, {"Level", type any}, {"Column D", type text}, {"Column E", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Code"}, {{"All", each _, type table [Code=nullable number, Column A=nullable text, Column B=nullable text, Column C=nullable text, Level=any, Column D=nullable text, Column E=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each 
if List.Contains( [All][Level], "disabled")  and List.Count( List.RemoveItems([All][Level],{"disabled"})) > 0 then true else false)
in
    #"Added Custom"

enter image description here

Upvotes: 0

Related Questions