Two Handy Power Query Tricks for Custom Sorting and Error Handling

Useful Power Query Tricks

Power Query is a powerful tool for data transformation, and knowing a few advanced tricks can help you work more efficiently. Here are two techniques I learned from the above video.

1. Custom Sorting in Power Query

By default, Power Query sorts data in ascending or descending order, but what if you need a custom order? For example, let’s say you have ratings labeled as E, A, B, and you want them sorted in that specific sequence. You can achieve this using the List.PositionOf function:

= Table.Sort(Source, {each List.PositionOf({"E", "A", "B"}, [Ratings])})

This method allows you to define an arbitrary order and sort the table accordingly. It’s especially useful when dealing with categorical data that doesn’t follow a standard numerical or alphabetical order.

2. The Error Trick – Filtering Non-Date Values

Sometimes, a dataset contains values that cannot be converted into a date. Instead of manually checking for errors, you can use the try function to filter out non-date values:

= List.Select(Source, each (try Date.From("1" & _))[HasError])

This formula attempts to convert each value into a date by prefixing it with “1” (to ensure a valid month-day format). If the conversion fails, the [HasError] property will be true, meaning the value is not a valid date. This trick helps you quickly identify and isolate (or keep, depending on your use case) problematic data.