Sorting Multiple Columns using Power Query in Power BI
Sorting Multiple Columns Using Power Query in Power BI
Sorting data is a fundamental aspect of data analysis that helps in organizing and understanding information effectively. Power Query in Power BI provides robust tools for sorting multiple columns, making the data analysis process more efficient and accurate. This article will guide you through the steps required to sort multiple columns using Power Query in Power BI, ensuring that you can present your data in the most logical and meaningful order.
Key Benefits of Sorting Multiple Columns
- Enhanced Data Clarity: Sorting helps in highlighting important trends and patterns within the dataset.
- Improved Data Organization: Sorting multiple columns allows for a more structured and readable dataset.
- Efficient Data Analysis: Makes it easier to compare and contrast different data points across multiple columns.
Steps to Sort Multiple Columns in Power Query
- Load Data: Import your dataset into Power BI.
- Open Power Query Editor: Navigate to the Power Query Editor to access your data.
- Select Columns: Choose the columns you want to sort.
- Apply Sorting: Use the sorting options to arrange the data in ascending or descending order.
- Save and Apply: Save your changes and apply the sorted data to your Power BI report.
Practical Applications
- Sales Data Analysis: Sort sales data by date and product category to identify seasonal trends and top-performing products.
- Employee Performance Review: Arrange employee data by department and performance score to streamline performance evaluations.
- Inventory Management: Sort inventory data by stock levels and reorder dates to maintain optimal stock levels.
By mastering the technique of sorting multiple columns using Power Query in Power BI, you can significantly enhance your data analysis capabilities, ensuring that your insights are both accurate and actionable.
Implementation in Power BI:
We will go through these steps:
- Going through the Table
- Opening Power Query
- Sorting the first three columns
- Sorting the Final three columns
- Checking the M Query
Happy learning!
1. Going through the Table:
The table presents sales data for office supplies in Los Angeles, California, categorized under various columns. Here’s a detailed breakdown:
Columns in the Table:
- City: This column indicates the city where the sales transactions took place.
- State/Province: This column shows the state or province associated with each transaction.
- Region: This column categorizes the transactions into broader regions, such as West, East, etc.
- Category: This column specifies the type of products being sold, like Office Supplies, Furniture, etc.
- Sales: This column provides the monetary value of the sales for each transaction.
- Quantity: This column records the number of units sold in each transaction.
General Observations of the Data:
- Variety in Location: The transactions occur in multiple cities and states, not just Los Angeles, California. This indicates a broader geographical spread.
- Range of Categories: The data covers multiple product categories such as Office Supplies, Furniture, Technology, etc., showcasing a variety of business segments.
- Sales Figures: The sales amounts vary significantly, reflecting different transaction sizes and potentially diverse customer bases.
- Quantity Sold: There is a wide range in the number of units sold per transaction, suggesting varying customer demands and order sizes.
- Temporal Diversity: The data spans multiple time periods, which may indicate seasonal trends or changes in sales patterns over time.
The table helps in analyzing the sales performance of office supplies in a specific region, which can be useful for inventory management, financial planning, and market analysis.
2. Opening Power Query:
- In the Home tab press on the Transform Data in the Queries section.
- It will open the Power Query window.
3. Sorting the first three columns:
- Press on the City column and select Sort Ascending.
- Now repeat the same process for State/Province and Region columns.
4. Sorting the Final three columns:
- Press on the Category column and select Sort Descending.
- Now repeat the same process for Sales and Quantity columns.
5. Checking the M Query:
- In the APPLIED STEPS, you will find a Sorted Rows step being added.
- Click on it and you will find the M Query for the sorting.
You fill find this Query in the function bar section.
= Table.Sort(#”Removed Other Columns”,{{“City”, Order.Ascending}, {“State/Province”, Order.Ascending}, {“Region”, Order.Ascending}, {“Category”, Order.Descending}, {“Sales”, Order.Descending}, {“Quantity”, Order.Descending}})
Download the Data for the above comparison from this link.
Thank you for your attention!
Follow me or subscribe to get all my Power BI articles!
Don’t forget to subscribe to
👉 Power BI Publication
👉 Power BI Newsletter
and join our Power BI community: