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 ,
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 @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.