Forum Discussion
Remove rows based on second key in another column
Hello,
I need help filtering out data in a large dataset.
I have a table that looks like this:
Column1 | Column2 | Value1 | Value2 | Value3 |
35 | XY-23 | Prod1 | A | 1231 |
35 | XY-23 | Prod2 | C | 1232 |
35 | XY-23 | Prod3 | B | 1233 |
36 | XY-23 | Prod1 | B | 1234 |
36 | XY-23 | Prod3 | B | 1235 |
36 | XY-23 | Prod4 | Y | 20 |
36 | XY-23 | Prod5 | H | 30 |
40 | XY-100 | Prod1 | U | 10 |
40 | XY-100 | Prod9 |
| 1 |
25 | XY-100 | Prod2 | C | 1236 |
25 | XY-100 | Prod9 | B | 1237 |
I want to group by Column2, then sort all records in Column1 and keep only the ones with the highest value in Column1.
Expected result should be:
Column1 | Column2 | Value1 | Value2 | Value3 |
36 | XY-23 | Prod1 | B | 1234 |
36 | XY-23 | Prod3 | B | 1235 |
36 | XY-23 | Prod4 | Y | 20 |
36 | XY-23 | Prod5 | H | 30 |
40 | XY-100 | Prod1 | U | 10 |
40 | XY-100 | Prod9 |
| 1 |
Can somebody pls help?
Thanks.
gr = Table.Group( your_tbl, "Column2", {"x", (x) => [a = List.Max(x[Column1]), b = Table.SelectRows(x, (w) => w[Column1] = a)][b]}), z = Table.Combine(gr[x])
3 Replies
Hi Merleau
Duplicate your starting table.
With this table group by Column2 and choose Max of Column1 as the aggregation. name the new column Column1
Which gives
Select this new table and then Merge it with the original table, using Column1 as the matching column
Giving this
Click the double headed arrow in the column header to expand the tables, only choosing the value columns
Rearrange columns as needed
Please check my PBIX file (linked above) for this example data and queries.
Regards
Phil
gr = Table.Group( your_tbl, "Column2", {"x", (x) => [a = List.Max(x[Column1]), b = Table.SelectRows(x, (w) => w[Column1] = a)][b]}), z = Table.Combine(gr[x])Many thanks to AlienSx and PhilipTreacy for your quick responses. Both solutions work perfectly.
