Formulas

Power Query Sorting

Pinterest LinkedIn Tumblr

Have you ever wondered that you can sort millions of rows or columns by just a few clicks? Power Query Sorting enables you to do that. In this article, we will discuss how to sort data in Power Query Editor.

Power Query Sorting – Single Column

To sort the data in Power Query, firstly load the data into Power Query Editor. After loading the data in Power Query Editor, let us sort the data by a single criterion using an example.

The below image shows data in the Power Query Editor and we will be sorting the Customer Name column in Descending order.

Power Query Sorting

To the right of the Name column, there is a dropdown box. Click on it and you will get various options. From them, you can select Sort Ascending or Sort Descending as per requirement. The below image will help you, visualize better.

Power Query Sorting

In our example, we select Descending. Below is the output. The ‘Customer Name’ column is sorted in ‘Descending’ order.

Power Query Sorting

To clear sort, you can click on ‘Clear Sort’ option from the drop down.

Power Query Sorting – Multiple Columns

Do you know sorting multiple columns is now easy? Multiple column sorting will allow you to sort data based on various criteria. For example you have a data set which consist on date and sales. You want to see the sales in ascending order on each date. So first we will be sorting the Date column in ascending order, followed by sales in ascending order on each date.

Let us understand, how practically we can sort data by multiple levels. Below is a data set that has 4 columns. We will firstly be sorting the Customer Names column in ascending order. Then by Sales in ascending order.

Power Query Sorting
  • Click on the drop-down box besides date and click on Sort Ascending. The Date column will be sorted in Ascending order.
  • Our next step is to sort the Sales column in Ascending order. We want to see the sales in ascending order on each date. Like on 11-02=2020 the sales will be shown in ascending order as 5000, 10000, 20000 ..and so on.
  • To do that, we will use the Power Query M Function.
  • We will have to enter the syntax in the formula bar as = Table.Sort(#”Promoted Headers”,{{“Date”, Order.Ascending},{“Sales”, Order.Ascending}})
  • Once we enter the syntax, the table will be sorted as below. In the below image the the dates are sorted n ascending order followed by sales.
Power Query Sorting

Note- You can notice a number 1 and 2 written beside the drop-down box of the columns sorted. That indicates the level of sorting. 1 indicates the column sorted first and 2 indicates the column sorted next.

Hope the article Power Query Sorting was helpful and informative.
To know more about Power Query M Function on sorting-Click Here.

Visit our youtube channel for more updates.

Related Posts

Write A Comment