Forum Discussion
How can I group like rows ?
I decided on making a clone of the table and just having "removed" in there and merge it back and filter out those records with Key as null, it appears to work and needed to have this due to time sensitivity, but thanks all for your assitance and ideas.
When I filtered out "Removed", I have this line:
#"Filtered Rows2" = Table.SelectRows(#"Added Custom4", each ([NAME] = "Added"))
However, I still see "DATA-123" as Added in the table, the point is if DATA-123(or any other record) has both "Added" and "Removed" to remove both rows.
EaglesTony wrote:
When I filtered out "Removed", I have this line:
#"Filtered Rows2" = Table.SelectRows(#"Added Custom4", each ([NAME] = "Added"))
However, I still see "DATA-123" as Added in the table, the point is if DATA-123(or any other record) has both "Added" and "Removed" to remove both rows.
That is because you did not follow my instructions precisely.
It seems you have filtered something before you even got to entering my line.
It's really hard to tell where you went wrong without knowing what you have done.
I suggest:
- Make a copy of your query as it exists.
- Navigate to the step where you see the table as you show in your question.
- Delete all of the steps below that particular step.
- Then add the Filtered rows step as I showed you and edit in the formula bar as I described.
Here is what I got:
let
Source = OData.Feed(source from another external system)
Custom1 = Source{[Name="Labels",Signature="table"]}[Data],
#"Filtered Rows" = Table.SelectRows(Custom1, each [NAME] = "Added" or [NAME] = "Committed"),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [ISSUE_KEY] = "DATA-123")
in
#"Filtered Rows1"I added that last Filtered Row to see if DATA-123 was being excluded, but it isn't.
Assuming Custom1 step shows the table similar to what you show in your original question, had you done what I have suggested, you should have something like:
let Source = OData.Feed(your data source), Custom1 = Source{[Name="Labels",Signature="table"]}[Data], #"Filtered Rows" = Table.SelectRows(Custom1, (r)=> let allKeys = Table.SelectRows(Custom1, each [KEY] = r[KEY]) in not List.Contains(allKeys[NAME],"Removed",Comparer.OrdinalIgnoreCase)) in #"Filtered Rows"which would show your stated desired result.
It is taking quite awhile to get records back.
Also, Name has other than Committed, Removed, Added. Some are just junk values.