How to filter local business directory data in Excel using Power Query?

aislamu 0 Reputation points
2026-11-20T16:26:24.9233333+00:00

Hello,

I am organizing a local service directory spreadsheet in Microsoft Excel (Microsoft 365) and need help filtering entries using Power Query.

For example, when querying my table to find which is the best Appliance repair service in Columbia Maryland Area, I want to filter out businesses with fewer than 10 reviews and sort the remaining results by highest customer rating.

Here is the basic M code I am currently using:

Code snippet

let
    Source = Excel.CurrentWorkbook(){[Name="ServiceDirectory"]}[Content],
    FilteredRows = Table.SelectRows(Source, each [City] = "Columbia" and [Reviews] >= 10)
in
    FilteredRows

How can I add a sorting step to this query so it automatically orders the filtered results by the "Rating" column from highest to lowest?

Tags:Microsoft Excel Power Query

Microsoft 365 and Office | Excel | For education | Windows
0 comments No comments

2 answers

Sort by: Most helpful
  1. Hendrix 2,935 Reputation points Independent Advisor
    2026-11-20T17:07:53.3+00:00

    Hi aislamu,

    Regarding your concern, you can add a Table.Sort step after filtering. You can try this:

    let
        Source = Excel.CurrentWorkbook(){[Name="ServiceDirectory"]}[Content],
        ChangedTypes = Table.TransformColumnTypes(
            Source,
            {
                {"City", type text},
                {"Reviews", Int64.Type},
                {"Rating", type number}
            }
        ),
        FilteredRows = Table.SelectRows(
            ChangedTypes,
            each [City] = "Columbia" and [Reviews] >= 10
        ),
        SortedRows = Table.Sort(
            FilteredRows,
            {{"Rating", Order.Descending}}
        )
    in
        SortedRows
    

    Explanation:

    • ChangedTypes ensures that Reviews and Rating are treated as numbers
    • FilteredRows keeps only businesses in Columbia with at least 10 reviews
    • SortedRows sorts the remaining businesses by Rating, from highest to lowest

    If businesses with the same rating need to be ranked by review count, you can add a secondary sort:

    SortedRows = Table.Sort(
        FilteredRows,
        {
            {"Rating", Order.Descending},
            {"Reviews", Order.Descending}
        }
    )
    

    Hope this helps.

    Was this answer helpful?

    0 comments No comments

  2. AI answer

    2026-11-20T16:26:55.21+00:00

    Add a Table.Sort step after FilteredRows, using Order.Descending for the Rating column.^1^

    let
        Source = Excel.CurrentWorkbook(){[Name="ServiceDirectory"]}[Content],
        FilteredRows = Table.SelectRows(
            Source,
            each [City] = "Columbia" and [Reviews] >= 10
        ),
        SortedRows = Table.Sort(
            FilteredRows,
            {{"Rating", Order.Descending}}
        )
    in
        SortedRows
    

    The query now:

    1. Reads the ServiceDirectory Excel table.
    2. retains rows where City is Columbia and Reviews is at least 10.
    3. Sorts the retained rows from highest to lowest Rating.

    Optional tie-breaker

    If businesses with identical ratings should be ordered by review count, add Reviews as a secondary descending sort:

    SortedRows = Table.Sort(
        FilteredRows,
        {
            {"Rating", Order.Descending},
            {"Reviews", Order.Descending}
        }
    )
    

    Power Query applies multiple sort levels in their listed priority order.^2^

    If the ordering appears incorrect—for example, 9.5 appears before 10—set Rating and Reviews to numeric types before filtering and sorting:

    TypedRows = Table.TransformColumnTypes(
        Source,
        {{"Reviews", Int64.Type}, {"Rating", type number}}
    )
    

    Then use TypedRows instead of Source as the input to Table.SelectRows.


    References

    1. Table.Sort
    2. Sort columns
    AI-generated content may be incorrect. Read our transparency notes for more information.

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.