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.
And another approach, which seems to execute quite rapidly:
Original
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckxJSU1R0lFycQxx1DU0MlaK1YlWCkrNzS/DFHbOz83NLClBSJiYmoElUAwxt7DEFDQ2MsRmMlg4FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [NAME = _t, KEY = _t]),
filter = Table.SelectRows(Source, (r)=>
let
allKeys = Table.SelectRows(Source, each [KEY] = r[KEY])
in
not List.Contains(allKeys[NAME],"Removed",Comparer.OrdinalIgnoreCase))
in
filter
Result
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.
Not sure how to code this, as my last line is(I was trying to set some sort of flag and then filter out):
#"Added Custom4" = Table.AddColumn(#"Filtered Rows1", "FeatureHasRemovalOnIt", each if Text.Contains([NAME], "-Removed", Comparer.OrdinalIgnoreCase) then "Y" else "N")
With the code I provided there is no need for that step. The code will remove (filter out) the proper rows all in that single step. Perhaps if you pasted the original code into the Advanced Editor (replacing all that is there), you would understand better.
Also, eliminating the "create a new column" step increases the efficiency/speed of the process.
Perhaps:
- Duplicate your existing query
- Delete all the lines below where your full table appears.
- Then follow the instructions regarding inserting the line I Mentioned.