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.
Can you explain from a manual part (i.e. selected Name column then group by) instead of the Advanced Editor View ?
The code does not use GroupBy (and executes considerably faster). If you wanted to do it from the UI,
1. when your table is showing, select the down arrow in the NAME column, and de-select "Removed"
2. In the formula bar you will see something similar to below. "Source" will be the same as your previous step.
3. Replace what you see after the first comma with:
(r)=>
let
allKeys = Table.SelectRows(Source, each [KEY] = r[KEY])
in
not List.Contains(allKeys[NAME],"Removed",Comparer.OrdinalIgnoreCase))
resulting in:
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.