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

[PoojaReddy]

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 correctly. However, when connecting Excel to the same Power BI model published to the service, the sorting doesn’t behave as expected — all blank values are getting grouped together.

Data :

Expectation :

In Excel :

Can anyone please help on this ?

Thank you !

[V-yubandi-msft]

Hi @PoojaReddy ,
Could you please let us know whether your issue has been resolved or if you are still encountering any difficulties? Your feedback is important to the community and may assist others with similar concerns.

Thank You.

[V-yubandi-msft]

Hi @PoojaReddy ,
May I know if your issue has been resolved, or if you still need any additional details? Please let us know if there’s anything else we can help with.

Thank you.

[PoojaReddy]

Hi @V-yubandi-msft,

No I havent received a proper solution. The issue still persists.

Any help would be appreciated.

[V-yubandi-msft]

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.

[V-yubandi-msft]

Hi @PoojaReddy ,

May I know if your issue has been resolved, or if you still need any additional details? Please let us know if you need any further assistance.
 

Thank you.

[PoojaReddy]

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]

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.

[Greg_Deckler]

@PoojaReddy When you say "apply sorting for each level in Power BI Desktop based on its corresponding sort column" how are you doing that exactly? Because you can't define Sort by columns with the data that you have because you have multiple different sort values for blank values and that is not allowed.