Forum Discussion
remove rows based on condition on another column
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.
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]))
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"
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
thank you Mariusz but if I have more than two columns can I show all the columns?
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.