Forum Discussion

PoojaReddy's avatar
PoojaReddy
Regular Visitor
10 months ago
Solved

Sorting applied in Power BI model is not getting applied in excel when connected to that model

Hi, I have a table with four hierarchical levels, and each level has its own sort column. When I apply sorting for each level in Power BI Desktop based on its corresponding sort column, it works cor...
Need Help
  • V-yubandi-msft's avatar
    V-yubandi-msft
    10 months ago

    Hi PoojaReddy ,
    In Power BI Desktop, sorting functions as expected, even when some hierarchy levels contain blank values, because Power BI follows the sort-by-column configuration. However, when connecting Excel to the same dataset using Analyze in Excel, Excel treats blank values differently. It groups all blanks together and does not maintain the defined sort order, which causes the hierarchy to appear incorrectly in Excel, even though it displays properly in Power BI.

     

    To solve this issue, I added a new column that ensures each label is unique, including the blank ones. This allows Excel to recognize them as distinct entries and sort them properly.
    DAX I used:

    Level3_Cleaned = 
IF(
    ISBLANK([Level3]), 
    "~Blank (" & FORMAT([Sort1], "000") & ")", 
    [Level3] & " (" & FORMAT([Sort1], "000") & ")"
)

    Once that’s in place.

    1. Sort Level3_Cleaned by Sort1

    2. Use  Level3_Cleaned  in your Excel PivotTable instead of the original Level 3.

    I ran this with some dummy data and confirmed the logic works perfectly. Power BI and Excel now show consistent results, and the sort order is preserved across both platforms.
    In PowerBI:

    In Excel :

     

    Please let me know if any adjustments are needed.  Thank you for your patience.

     

    Regards,
    Yugandhar.

     

     

PoojaReddy's avatar
PoojaReddy
Regular Visitor
10 months ago

Hi Greg_Deckler,

Thank you for the response !

We use Tabular Editor to make changes in the model hence it is getting treated with a warning. 

Our concern is mainly on the excel. Even though we have same sort order number for blanks it doesnt work in excel.

  • V-yubandi-msft's avatar
    V-yubandi-msft
    Icon for Community Support rankCommunity Support
    10 months ago

    This is a situation where Power BI Desktop and Excel handle things differently, even when working with the same model. When you use Tabular Editor to set the sort order, Power BI Desktop allows multiple blank values to have the same sort key, but Excel is stricter and groups all blanks together, ignoring the sort metadata.

     

    You can try these options:

    1. Replace blank values with placeholders like ~ or Undefined so Excel can tell them apart, and assign each a unique sort number.

    2. Create a composite sort column, such as combining LevelName and SortOrder, and sort your display column by this new field to ensure uniqueness.

    3. Check how Excel is applying the sort, especially if you’re using a Pivot Table, as it may use its own sorting logic in addition to the model’s sort order.

     

    Regards,
    Yugandhar.