Filter Top N values in query with number column.

[Anonymous]

Hi,

I wanna have only top N values with column 'Period', not top N rows cuz some rows have the same value.

[timalbers · Accepted solution]

Hi @Anonymous 

in Power Query add something like this to your query:

let
    Source = YourTable,
    Distinct = Table.Distinct(Table.SelectColumns(Source, {"Period"})),
    Sorted = Table.Sort(Distinct, {{"Period", Order.Descending}}),
    TopN = Table.FirstN(Sorted, N),
    Merged = Table.NestedJoin(Source, {"Period"}, TopN, {"Period"}, "Merge", JoinKind.Inner),
    Result = Table.ExpandTableColumn(Merged, "Merge", {"Period"})
in
    Result

you'll need to replace "N" with the actual amount of values you want to keep and "YourTable".

In DAX (e.g. for a new calculated table) you could do this:

Top_N = 
VAR Top_Periods = TOPN( N, VALUES( YourTable[Period] ), YourTable[Period], DESC )
RETURN FILTER( YourTable, YourTable[Period] IN Top_Periods )

same here, replace N and YourTable accordingly.

[danextian · Accepted solution]

Hi @Anonymous 

In the Query Editor, remove duplicates from the Period column, sort it in descending order, and use Table.Buffer to store the sorting in memory. Then, from the Home tab, select Keep Rows and enter the number of rows to retain.

This will return only one row per unique period. If you need to preserve all related rows, duplicate the query and merge it with the original using an Inner Join. Note: This additional transformation may impact refresh performance due to the increased processing steps. Alternatively, consider using RANKX in DAX.

Please see the attached pbix.

[Anonymous]

Thanks but can we filter out without duplicating data table or dax?

Main purpose of this is to reduce modeling size and loading time by taking some unnecessary data out before move on to dax data. 

[timalbers · Accepted solution]

Hi @Anonymous 

in Power Query add something like this to your query:

let
    Source = YourTable,
    Distinct = Table.Distinct(Table.SelectColumns(Source, {"Period"})),
    Sorted = Table.Sort(Distinct, {{"Period", Order.Descending}}),
    TopN = Table.FirstN(Sorted, N),
    Merged = Table.NestedJoin(Source, {"Period"}, TopN, {"Period"}, "Merge", JoinKind.Inner),
    Result = Table.ExpandTableColumn(Merged, "Merge", {"Period"})
in
    Result

you'll need to replace "N" with the actual amount of values you want to keep and "YourTable".

In DAX (e.g. for a new calculated table) you could do this:

Top_N = 
VAR Top_Periods = TOPN( N, VALUES( YourTable[Period] ), YourTable[Period], DESC )
RETURN FILTER( YourTable, YourTable[Period] IN Top_Periods )

same here, replace N and YourTable accordingly.