Power Query: Do not show group if column is equal to a certain value

[Anonymous]

Hi there,

I do have a table like this in Power BI which displays employee absences. The status changes automatically and, unfortunately, each change is still shown in the database. The data looks like this:

Now what I want to accomplish: If the state of an ID is equal to cancelled, the whole ID should not be displayed. In the upper example, I'd just like to see all entries belonging to ID's 2 and 3. Is there a way to achieve this in Power Query?

Thanks for your help!

[Icey · Accepted solution]

Hi @Anonymous ,

Try this:

1. Create a State list.

2. Create a parameter.

3. Filter your data table.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpLTE4syczPAzINDPWByMjACCSeXJSaWJKaohSrg6nOCEldYnJJZlkqVmWmyMYl5iWn5uRADTTCYyCyxejqjLFbbAzkOiYVpwKtALKMzLCZFgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Type = _t, Date = _t, State = _t]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Date", type date}}, "en-GB"),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"ID", Int64.Type}, {"Type", type text}, {"State", type text}}),
    RemovedIDs = Table.SelectRows(#"Changed Type", each [State] = #"State Parameter")[ID],
    #"Filter Table" = Table.SelectRows(#"Changed Type", each not (List.Contains(RemovedIDs,[ID])))
    
in
    #"Filter Table"

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlB]

@Anonymous 

Another version that might be faster:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpLTE4syczPAzINDPWByMjACCSeXJSaWJKaohSrg6nOCEldYnJJZlkqVmWmyMYl5iWn5uRADTTCYyCyxejqjLFbbAzkOiYVpwKtALKMzLCZFgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Type = _t, Date = _t, State = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Type", type text}, {"Date", type date}, {"State", type text}}),

    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each Table.Group(#"Changed Type", {"ID"}, {{"Aux_", each Table.Max(_, "Date")[State], type text}}){[ID=[ID]]}[Aux_] <> "cancelled")
in
    #"Filtered Rows"

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB]

Hi @Anonymous 

Please always show your sample data in text-tabular format in addition to (or instead of) the screen captures. A screen cap doesn't allow people to readily copy the data and run a quick test and thus decreases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here. Or, ideally, share the pbix (beware of confidential data).

Place the following M code in a blank query to see the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpLTE4syczPAzINDPWByMjACCSeXJSaWJKaohSrg6nOCEldYnJJZlkqVmWmyMYl5iWn5uRADTTCYyCyxejqjLFbbAzkOiYVpwKtALKMzLCZFgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Type = _t, Date = _t, State = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Type", type text}, {"Date", type date}, {"State", type text}}),

    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each Table.Max(Table.SelectRows(#"Changed Type", (inner)=>inner[ID]=[ID]), "Date")[State]<>"cancelled")
in
    #"Filtered Rows"

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers