Filter Top N values in query with number column.

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.

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.