Forum Discussion
Top 2 for each date
Hi
This is my data
Here for each date, I want the top 2 sales numbers 16 and 14 for 1/1
17 and 15 for 2/1
How can i do this in M code/query editor?
Hi klehar ,
How about this?
Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUBSIjAyNTJR0lQzOlWB10MRMsYkZYxAwgYkbIYuZYxEyxiBljETNUio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [date = _t, sales = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}, {"sales", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"date"}, {{"TopSales", each Table.FirstN( Table.Sort(_, {{"sales", Order.Descending}}), 2 ), type table [sales=Int64.Type]}}), #"Expanded TopSales" = Table.ExpandTableColumn(#"Grouped Rows", "TopSales", {"sales"}, {"TopSales.sales"}) in #"Expanded TopSales"
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
4 Replies
Hi klehar ,
How about this?
Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUBSIjAyNTJR0lQzOlWB10MRMsYkZYxAwgYkbIYuZYxEyxiBljETNUio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [date = _t, sales = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}, {"sales", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"date"}, {{"TopSales", each Table.FirstN( Table.Sort(_, {{"sales", Order.Descending}}), 2 ), type table [sales=Int64.Type]}}), #"Expanded TopSales" = Table.ExpandTableColumn(#"Grouped Rows", "TopSales", {"sales"}, {"TopSales.sales"}) in #"Expanded TopSales"
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/Looks like some complex manuallly written code. Can you care to explain plz
Hi klehar ,
Absolutely!
It's essentially a few things that we mix here.
First, we need to group our data by date. If you do this in the UI you ususally can choose aggregations like min, max or count etc. However, you can use other functions that are not shown in the UI as well. In our case we use the FirstN([our column], 2), returning the first two members per group. Now, this is not entirely correct, yet, since we need to make sure its always returning the highest two members per group. That's when the Order.Descending comes into the game. We are nesting those three operations into each other, where we for each group take the top 2 rows of the sorted sales column.The syntax looks more complicated than it actually is. What I usually do is, that I create the syntax via the UI first separately and then cut out the snippets and paste them together. Helpful to know is the "_" (underscore) syntax, where you tell M to do "something" per the current group. 🙂
Hope this helps 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/Hi klehar ,
Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.
If these also don't help, please share more detailed information and description to help us clarify your scenario to test.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
