Top N in Power Query Editor

[Anonymous]

is there a way we can use top n in specfic column in the power query editor?

[Anonymous]

this data updates weekly and the stamp is the report date,

how do i set a filter to only get top MAX two?

[selimovd]

Hey @Anonymous ,

first of all, when you reply always mark the person like this: @Anonymous 

Otherwise I will get a notification that you replied.

The same way, sort by date, then to a top 2. When the data refreshes, the sort also will refresh to kepp only the top 2 rows.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

i am not sure how many rows to keep to be able to get the MAX 2 report date? @selimovd 

[selimovd · Accepted solution]

Hey @Anonymous ,

got it, you have to add another column that only contains the date.

To get the 2 top dates and then all rows is a little more tricky.

I would split it in 2 queries:

1. The first one you have to drill down to get the top 2 dates  (remove duplicates, sort, kepp first 2 rows, then drill down to the second date).

2. The second one you just filter the date to be after or equal to the date that was the outcome of the first query.

I did a small example:

The table to filter the top two dates:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUAyIjAyNDJR0lE6VYHaCQEZKQEUTIGEnIGCKErNEUU8gI0yxTosyCajTBVIWs0VApNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Value", Int64.Type}}),
    #"Removed Duplicates" = Table.Distinct(#"Changed Type", {"Date"}),
    #"Sorted Rows" = Table.Sort(#"Removed Duplicates",{{"Date", Order.Descending}}),
    #"Kept First Rows" = Table.FirstN(#"Sorted Rows",2),
    Date = #"Kept First Rows"[Date],
    Date1 = Date{1}
in
    Date1

And the result table where you filter for the result of the first query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUAyIjAyNDJR0lE6VYHaCQEZKQEUTIGEnIGCKErNEUU8gI0yxTosyCajTBVIWs0VApNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Value", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Date] >= #"Get Top 2 Dates")
in
    #"Filtered Rows"

Here is my example as PBIX file:

https://www.swisstransfer.com/d/a7d112fa-f474-4600-9738-b552ea4905e0

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[selimovd]

Hey @Anonymous ,

absolutely, just mark the column you want, sort it to what you want to archieve and then in the "home" tab chose "Keep rows" --> "Keep Top Rows":

Afterwards you can decide how many rows and get a top n like that.

If you want to do it in the advanced editor, you can also use the Table.FirstN function.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic