Power Query sort by multiple columns is one of the most fundamental tasks for organizing and analyzing data efficiently. Moreover, when working with large datasets, sorting multiple columns simultaneously not only saves time but also ensures your data is logically structured for better insights. Accordingly, this guide will show you how to use Power Query to sort multiple columns, both through the Editor and with advanced M code in Power Query, such as Table. Sort.
Why Sorting Multiple Columns in Power Query is Essential
Sorting in Power Query, above all, organizes your data logically and, consequently, enhances reporting and analysis. For instance, whether you’re creating dashboards in Power BI or preparing reports in Excel, sorting multiple columns can significantly improve how you manage and interpret your data. Specifically, it allows you to:
- Group related data for easy analysis, such as grouping products by category.
- Furthermore, help identify patterns or outliers in your dataset quickly and effectively.
- Lastly, ensure consistency, especially when working with dynamic datasets that are frequently updated.
As an illustration, imagine sorting a product catalog. If you only sort by one column, then the results may still appear somewhat disorganized. However, by sorting multiple columns, such as Category followed by Product Name, you can achieve precise and logical grouping, which ultimately makes your data easier to analyze.
Sorting Methods in Power Query
Method 1: Sorting with Power Query Editor
The Power Query Editor provides an intuitive way to sort columns visually. Here’s how:
- Open Power Query Editor: Load your data into Power Query.
- Select Columns to Sort: Start by sorting the first column. For example, click the dropdown in Category and choose Sort Ascending.
- Add More Sort Steps: Select the next column (e.g., Product Name) and sort it. Power Query applies these steps sequentially.
- View Applied Steps: In the Applied Steps pane, you’ll see each sorting action as a separate step.
However, there are limitations:
- Sorting happens sequentially, one column at a time.
- Modifications to the dataset can disrupt sorting orders.
- It’s less flexible for complex scenarios or reusable workflows.
Method 2: Sorting with M Code (Table.Sort)
To overcome these limitations, use the powerful Table.Sort function in M code. This method allows you to sort multiple columns in a single step.
Syntax Overview: Power Query Table.Sort
The basic syntax for sorting with Table.Sort is:
= Table.Sort(PreviousStep, {{"Column1", Order.Ascending}, {"Column2", Order.Ascending}})
- Replace
PreviousStepwith the name of your query’s last step (e.g.,#"Changed Type"). - Specify the columns and sort orders in the curly braces
{}. UseOrder.AscendingorOrder.Descending.
Practical Example
Let’s sort a dataset of products by Category (ascending) and Product Name (ascending). Use the following code:
=Table.Sort(#"Changed Type", {{"Category", Order.Ascending}, {"Product_Name", Order.Ascending}})
Key Notes:
- Adjust
#"Changed Type"to match the previous step in your query. - Replace
CategoryandProduct_Namewith your column names.
Result: The data is sorted first by Category, grouping similar items together, and then by Product Name alphabetically within each category.
Of course, you can apply this logic to filter by 3 or more columns. Just follow the structure from the M Code and adjust it based on your needs!
Comparing Normal Sorting vs. M Code Sorting
| Feature | Power Query Editor (Normal Sorting) | M Code (Table.Sort) |
|---|---|---|
| Ease of Use | Beginner-friendly, no coding required | Requires knowledge of M code syntax |
| Sorting Multiple Columns | Sequential, one column at a time | Simultaneous sorting in one step |
| Customization | Limited to basic ascending/descending | Fully customizable |
| Reusability | Less efficient for dynamic datasets | Ideal for dynamic and complex queries |
| Scalability | Suited for small datasets | Works efficiently on large datasets |
Recommendation: Use M code for complex datasets or when you need reusable and dynamic sorting logic.
Best Practices for Sorting in Power Query
- Match Column Names: Ensure column names in your dataset match the ones specified in the M code.
- Correct Step Order: Place sorting steps strategically in your query. Sorting early may save time, but it can also overwrite other transformations.
- Use Descriptive Step Names: Rename your steps in the Applied Steps pane to improve query readability.
- Test with Different Data: Validate the query on various datasets to ensure consistency.
FAQs: Sorting Multiple Columns in Power Query
Frequently Asked Questions
How do I sort multiple columns in Power Query using M code?
Use the `Table.Sort` function, specifying the columns and sort orders within curly braces. Example:
= Table.Sort(#"Changed Type", {{"Category", Order.Ascending}, {"Product_Name", Order.Ascending}})
What’s the difference between Power Query Editor and M Code for sorting?
The Editor is easier to use but sorts columns sequentially. M code allows simultaneous sorting in one step and offers more flexibility for dynamic datasets.
Can I combine ascending and descending orders in one query?
Yes! For example, you can sort one column in ascending order and another in descending order: {{"Category", Order.Ascending}, {"Sales", Order.Descending}}.
Conclusion
In conclusion, sorting data by multiple columns in Power Query improves data organization, enhances reporting, and simplifies analysis. While the Power Query Editor offers a simple way to sort columns, M code (Table.Sort) is more powerful and flexible, especially for complex or reusable workflows. Now you know how to sort by two columns or more in Power BI.
Interested to learn some more amazing Power BI trips? Follow my blog post series: 8 Tips to Create an Interactive Power BI Dashboard
Happy learning 🙂
