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.
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.
I don't know what you mean, nor how it relates to your question. You don't provide sufficient information for me to comment.
"It is taking quite awhile to get records back"
- From what step?
- How long?
- what are the errors if any?
- If your data source is slow, that would certainly be a cause.
"Also, Name has other than Committed, Removed, Added. Some are just junk values".
- How does that relate to your original question?
- The code I provided does what you stated in your original question, which was to remove KEYs if there were any NAME="Removed" listed with any one of the KEYs.
I have this, but it seems to take quite awhile to refresh:
= Table.SelectRows(Custom1, (r)=>
let
allKeys = Table.SelectRows(Custom1, each [KEY] = r[KEY])
in
not List.Contains(allKeys[NAME],"Removed",Comparer.OrdinalIgnoreCase))That seems almost identical to what you wrote previously. I had given you a list of possibilities for slowness. I am afraid that without access to a data set example that can reproduce the problem, I don't know what to add about the slowness.