Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
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.
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])
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
Proud to be a Super User!