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.
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.
What I did was make a duplicate of the table and filtered down to those records I don't want.
Then I merged the first table(all records) with the 2nd table(all "bad" records, those with removed) and did a left join.
Then I filtered the key from that merge step that is null(thus not on the 2nd table) and it now has my records I want. Not the most efficent, but it works.
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.