top of page

Mastering Sorting in Power BI: A Smarter Approach Using Google Sheets

Writer's picture: nitin rungtanitin rungta

Sorting in Power BI depends on the context. If sorting is needed at the sum total level, the best approach is Power Query.



A . Sorting in Power Query (When Based on Amount)

  1. Create a duplicate table.

  2. Group by the category column and compute the sum of the amount.

  3. Sort the table based on the amount.

  4. Create an index column to establish a ranking.

  5. Merge it back into the original table.


However, in many cases, sorting is not based on amount but rather follows a custom sequence, such as in reports like the Balance Sheet.




Sorting Beyond Alphabetical or Amount Logic


  • Reports like the Balance Sheet do not follow alphabetical sorting or strict numerical order.

  • The display order is based on business logic rather than standard sorting rules.

  • This is where things get tricky and using Google Sheets (GS) becomes an effective solution.

  • Obviosuly goes without saying if your organization is in a MS environment. Using Sharepoint Excel. Make a site if neccessary, but please make sure all other team mates are aware and know how to you Sharepoint.


B. Using Google Sheets for Custom Sorting

  1. Extract unique values from the category column in Power Query.

  2. Paste the values into Google Sheets.

  3. Manually define the required sequence in a new column.

  4. Publish the sheet to the web in Excel format.

  5. Load the Google Sheets data into Power Query as a new source.

  6. Merge this table back into the original dataset and apply the defined sorting logic.




Additional Considerations


Handling Dynamic Data Updates

  • If new rows are expected in the future (e.g., adding new employees in a sorting list), manual updates will be required.

  • The challenge: Who will remember to update the mapping?


Solution: Automated Warning System

  • In the cover page of the dashboard, create a warning chart.

  • If any employee in the main table lacks a mapped sorting value, an alert is displayed.

  • Once the value is updated in Google Sheets, the dashboard updates automatically, and the warning disappears.

  • Please make sure, the link is directly accessible in the dashbaord itself, so the user with permissions can do the changes.

  • Security Integrity - Best case scenario, use Google Forms, connected to the Google Sheets, and give a link to the form in the dashboard instead of direct Google Sheets access.

This ensures that sorting logic remains intact without constant manual monitoring, keeping reports dynamic and error-free.



Have faced more advance sorting challenges? Please share with me - nitin@dataroars.com

11 views0 comments

Recent Posts

See All

Power BI Measure Naming Standards

Objective To establish a clear, concise, and standardized naming convention for Power BI measures, ensuring consistency, avoiding...

Comments


Linkedin.png
Mail.png
WA.png
Calendy.png

© 2024 by DataRoars | PurpleMe India Private Limited. All rights reserved. 

bottom of page