Forum Discussion

klehar's avatar
klehar
Helper V
1 year ago
Solved

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?
Need Help
  • tackytechtom's avatar
    tackytechtom
    1 year ago

    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/

tackytechtom's avatar
tackytechtom
Most Valuable Professional
1 year ago

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/