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.
From the process you describe, it sounds as if it would be much more efficient to just filter out (remove) the records you don't want, which is what my code would do. If there are other records you don't want, they can be added to that filter step.
That is not simple to do in the UI. You would need to go into the Advanced Editor (or the formula bar), and edit the M-code directly.
Just as an example of the outcomes of using the different approaches provided you in this thread, there was a 50 fold difference in time spent to execute the query comparing the most efficient (mine) to the least, using the data set you provided.
Ok, I will put it in again, but taking time to refresh this table, maybe the number of rows within it.
= Table.SelectRows(Custom1, (r)=>
let
allKeys = Table.SelectRows(Custom1, each [KEY] = r[KEY])
in
not List.Contains(allKeys[NAME],"Removed",Comparer.OrdinalIgnoreCase))
Is there a way to add to remove items that have "Removed" and other junk values(i.e. Some have "Test", "Demo", etc) or maybe I can filter these out ahead of time before the above step.
It depends on exactly what you have and what you want to do.
If you are going to remove just the singular entries, then you could change the last line in that Filter function to something like:
not List.Contains(allKeys[NAME],"Removed",Comparer.OrdinalIgnoreCase) and r[NAME] <> "Demo" and r[NAME] <> "Test"If you want to remove all the KEYS if any NAME is junk, or if your logic works better by selecting which you want to retain, the line would be different.
And there is other logic that can be used depending on the specifics of your problem.