Select Top N by sorting with respect to date and ignore all non blank values

[Anonymous]

Hi,

I have created a summarized table similar to the below table.

And the expected output is as shown below

I have to first sort the summarized table in descending order of Date and consider the top N rows from every individual row excluding blanks. I tried but couldn't get the expected result.

Could someone please help me in achieving this?

[Anonymous]

Hi @amitchandak,

Please find data in the below link

https://docs.google.com/spreadsheets/d/1sHk06ih0X4UEbseVoWz0Fwzg8h5TxR4g-I443VEBgyk/edit?usp=sharing

I was looking for a query in DAX.

The expected output would look like

Thanks

[amitchandak]

@Anonymous ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[AlB]

@Anonymous 

Here is a solution based on your initial table and N = 2. Place the following M code in a blank query to see the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NYzLDQAgCEN36dmLH1BnIey/hoCQECgPWhH0gQary392aJMQfmD6dATduVlNlzMo+eMqeyyVwcvaSRcVzUjHDNUH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Column4", Int64.Type}, {"Date", Int64.Type}}),

    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date", Order.Ascending}}),
    N_ = 2, //Number of items to be extracted (Top N)
    col1_ = List.FirstN(List.Select(#"Sorted Rows"[Column1], each _<> null), N_), 
    col2_ = List.FirstN(List.Select(#"Sorted Rows"[Column2], each _<> null), N_),
    col3_ = List.FirstN(List.Select(#"Sorted Rows"[Column3], each _<> null), N_),
    col4_ = List.FirstN(List.Select(#"Sorted Rows"[Column4], each _<> null), N_),
    res_ = Table.FromColumns({col1_, col2_, col3_, col4_})
in
    res_

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

Hi @AlB ,

Thanks for your quick response.

N here is 300 and please find below the link for the data.

https://docs.google.com/spreadsheets/d/1sHk06ih0X4UEbseVoWz0Fwzg8h5TxR4g-I443VEBgyk/edit?usp=sharing

For every individual column, I need to consider the top 300 order by date descending.

Thanks

[AlB]

Hi @Anonymous 

Where is the date to sort by?

N is what number?

Can you share the original table in text-tabular format rather than with a screen cap, so that its contents can be copied?

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers