Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
My query uses Group By to group by three categorical columns and get sum of a Price column. The resulting table looks like below.
I want to keep only the rows that have the max value in the Price column.
Purchase Order | Vendor | Customer | Price | |
1015 | V03 | 99000043 | 6,000.00 | <== KEEP |
1016 | V03 | 99000043 | 176,000.00 | <== KEEP |
1060 | V02 | 99000047 | 3,351.70 | <== EXCLUDE |
1060 | V02 | 99000039 | 18,648.30 | <== KEEP |
1086 | V03 | 99000000 | 8,021.84 | <== KEEP |
1094 | V03 | 99000169 | 401,000.00 | <== KEEP |
1102 | V03 | 99000033 | 1,400.00 | <== EXCLUDE |
1102 | V02 | 99000000 | 3,700.00 | <== KEEP |
I have tried List.Max() to get the desired result using:
= Table.SelectRows(Source, each ([Price] = List.Max(Source[Price])))
But when I run the query it takes very long time to complete, in fact I have not had it complete yet, I cancel it after 10 minutes. That is too long.
The data behind the query is csv file with about 20,000 rows x 35 columns text and numbers. So it isn't so big that it would be non-performant.
What other options are there besides List.Max() to get the desired results?
Solved! Go to Solution.
Hi, try this one:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZFBC4MwDIX/ivQcSmJiGw+etjLGJuwyGDjxsN+w/79ax6ZSxEAheYeP9167zhBhGqoMmPZ8Gtp2wDLu3o+ycFyLNA7iPT6LGMXnG5FfTVNcQriZHmYoN0PxGkV+N8hh3pP/eWLgioAqZ/0CFR6H6/0Y9tC4/tJIwYmCClve9qX5gCnN5EsBS4qo2qpsompZozQ1OJkSpJ1dUUo0B2nS+f9/BJJFZbpa0spcQga/Yaz/AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Purchase Order" = _t, Vendor = _t, Customer = _t, #" Price " = _t, #" " = _t]),
Typed = Table.TransformColumnTypes(Source, {{" Price ", type number}}, "en-US"),
Grouped = Table.Group(Typed, {"Purchase Order"}, {{"recs", each Table.Max(_, " Price "), type record}}),
Recs = Table.FromRecords( Grouped[recs] )
in
Recs
You can also use List.Max, but with a small addition to the code. This edit is needed to bypass the lazy calculation of lists in the M language:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZFBC4MwDIX/ivQcSmJiGw+etjLGJuwyGDjxsN+w/79ax6ZSxEAheYeP9167zhBhGqoMmPZ8Gtp2wDLu3o+ycFyLNA7iPT6LGMXnG5FfTVNcQriZHmYoN0PxGkV+N8hh3pP/eWLgioAqZ/0CFR6H6/0Y9tC4/tJIwYmCClve9qX5gCnN5EsBS4qo2qpsompZozQ1OJkSpJ1dUUo0B2nS+f9/BJJFZbpa0spcQga/Yaz/AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Purchase Order" = _t, Vendor = _t, Customer = _t, #" Price " = _t, #" " = _t]),
Typed = Table.TransformColumnTypes(Source, {{" Price ", type number}}, "en-US"),
Grouped = Table.Group(Typed, {"Purchase Order"}, {{"tabs", each Table.SelectRows(_, let latest = List.Max([#" Price "]) in each [#" Price "] = latest), type table}}),
Recs = Table.Combine( Grouped[tabs] )
in
Recs
Hi, try this one:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZFBC4MwDIX/ivQcSmJiGw+etjLGJuwyGDjxsN+w/79ax6ZSxEAheYeP9167zhBhGqoMmPZ8Gtp2wDLu3o+ycFyLNA7iPT6LGMXnG5FfTVNcQriZHmYoN0PxGkV+N8hh3pP/eWLgioAqZ/0CFR6H6/0Y9tC4/tJIwYmCClve9qX5gCnN5EsBS4qo2qpsompZozQ1OJkSpJ1dUUo0B2nS+f9/BJJFZbpa0spcQga/Yaz/AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Purchase Order" = _t, Vendor = _t, Customer = _t, #" Price " = _t, #" " = _t]),
Typed = Table.TransformColumnTypes(Source, {{" Price ", type number}}, "en-US"),
Grouped = Table.Group(Typed, {"Purchase Order"}, {{"recs", each Table.Max(_, " Price "), type record}}),
Recs = Table.FromRecords( Grouped[recs] )
in
Recs
You can also use List.Max, but with a small addition to the code. This edit is needed to bypass the lazy calculation of lists in the M language:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZFBC4MwDIX/ivQcSmJiGw+etjLGJuwyGDjxsN+w/79ax6ZSxEAheYeP9167zhBhGqoMmPZ8Gtp2wDLu3o+ycFyLNA7iPT6LGMXnG5FfTVNcQriZHmYoN0PxGkV+N8hh3pP/eWLgioAqZ/0CFR6H6/0Y9tC4/tJIwYmCClve9qX5gCnN5EsBS4qo2qpsompZozQ1OJkSpJ1dUUo0B2nS+f9/BJJFZbpa0spcQga/Yaz/AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Purchase Order" = _t, Vendor = _t, Customer = _t, #" Price " = _t, #" " = _t]),
Typed = Table.TransformColumnTypes(Source, {{" Price ", type number}}, "en-US"),
Grouped = Table.Group(Typed, {"Purchase Order"}, {{"tabs", each Table.SelectRows(_, let latest = List.Max([#" Price "]) in each [#" Price "] = latest), type table}}),
Recs = Table.Combine( Grouped[tabs] )
in
Recs
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.