remove rows based on condition on another column

[Anonymous]

hello 

I have data similar to this and I want to remove ids that have not go through C step 

id	steps
1	a
2	a
2	b
1	b
1	c
3	a

so the expected result is

1	a
1	b
1	c

I have tried this one but the problem is that removes all related steps too  

Table = CALCULATETABLE(table,FILTER (

FILTER (

ALLEXCEPT ( table,table[id]),

table[steps] = "c"

))

and if I can do it in power query direct without creating a new table that will be perfect

[edhans · Accepted solution]

Hi @Anonymous 

Insert this formula as a custom column in Power Query:

let 
    varID = [id]
in   
Table.MatchesAnyRows(
    Table.SelectRows(#"Changed Type", each [id] = varID), each [steps] = "c"
)

It will return this table:

Then simply filter to filter out the "FALSE" values, and you will be left with your table above, the ID's that are 1 and have gone through step C.

Note: this will work as long as there is a step C. If there is also a D, E, and F, it will still keep that data.

[Greg_Deckler]

Well, here is a DAX way, attached. Might be a little over complicated but I wrote it and it worked first time so that's always a little scary. I keep thinking there should be a more elegant way with EXCEPT or INTERSECT something.  @ImkeF and @edhans can potentially help with a Power Query solution. PBIX is attached.

Table 2 = 
    VAR __Table = 
        SUMMARIZE(
            'Table',
            [id],
            "HasC","c" IN SELECTCOLUMNS('Table',"steps",[steps])
        )
    VAR __IDsWithC = FILTER(__Table,[HasC])
RETURN
    FILTER('Table',[id] IN SELECTCOLUMNS(__IDsWithC,"id",[id]))

[Mariusz]

Hi @Anonymous 

Sure, you can Group by id, later filter for any rows containing "c" and expand.
Please see the script below or attached at the end.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUpUitWJVjJCYSWBWYYorGQwyxiiLhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [id = _t, steps = _t]),
    #"Grouped Rows" = Table.Group(Source, {"id"}, {{"rows", each _, type table [id=number, steps=text]}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each List.Contains( [rows][steps], "c" ) ),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Filtered Rows", "rows", {"steps"}, {"steps"})
in
    #"Expanded Rows"

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Anonymous]

thank you @Mariusz but if I have more than two columns can I show all the columns?

[Mariusz]

Hi @Anonymous 

You could select any column that is present in your table as Group by rows Column was inclusive of all columns in a dataset.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[edhans]

@Anonymous - my additional column will not prevent you from seeing any other columns. I avoided the Group By method because I suspected your data was a bit more involved than what you shared with us, and a Group By can give unwanted results if you have multiple "Group By" columns that have different data for your IDs.

If my formula doesn't work in your situation, please advise where it breaks down and I'll see if I can tweak it.