How to get top5 values based on a field.

Let's work on getting the desired output where all tied values are included when calculating the top 5 records based on the values field.

Here's how you can achieve this in M Query:

1. Load Your Data Source:

   • Open Power Query and load your data source.

2. Sort the Table:

   • Sort the table by the values column in descending order.

3. Identify the Top 5 Values:

   • Identify the unique top 5 values.

4. Filter the Table:

   • Filter the table to include all rows where the values column matches any of the top 5 values.

Here's a sample M Query script to achieve this:

let
    // Step 1: Load your data source
    Source = Excel.Workbook(File.Contents("YourFilePath.xlsx"), null, true),
    Data = Source{[Name="YourSheetName"]}[Data],

    // Step 2: Sort the table by the "values" column in descending order
    SortedTable = Table.Sort(Data, {{"values", Order.Descending}}),

    // Step 3: Identify the unique top 5 values
    Top5Values = List.FirstN(List.Distinct(List.Sort(Table.Column(SortedTable, "values"), Order.Descending)), 5),

    // Step 4: Filter the table to include all rows with the top 5 values
    FilteredTable = Table.SelectRows(SortedTable, each List.Contains(Top5Values, [values]))
in
    FilteredTable

Replace "YourFilePath.xlsx" and "YourSheetName" with your actual file path and sheet name. This script will sort the table by the values column, identify the unique top 5 values, and filter the table to include all rows with those values.