Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
JS_UNI4C
Helper I
Helper I

Table sorting in Power BI desktop

Hi all, 

 

Hoping someone can help me as I have no clue what has changed and why I suddenly keep getting this sort error when it worked perfectly fine in the past. 

 

I am trying to sort a column (Level 1) in a table using a sort order column (Level 1 Order) as picutured below. There is a unique number for each value in Level 1 but there are duplicates. In the past, this has made no difference so if I had the same text in the first column but it had the same sort number in the sort column, it worked perfectly. This is how I created financial statement layouts. 

 

No suddently this seems broken and Power BI keeps telling me I have different numbers for the same value in the first column. 

 

What am I missing here???

 

JS_UNI4C_1-1712743882164.png

 

 

JS_UNI4C_0-1712743710255.png

 

 

JS_UNI4C_2-1712743924071.png

 

 

1 ACCEPTED SOLUTION

Hi, 

 

Turns out that because I had another table in my data model that was using the same headers as this table, it was causing the conflict. I changed the table headers and it worked fine. 

 

How I get around the same "null" values is by going into power query editor and then filtering those out, going back to DAX and then set the sort order before going into power query editor and removing the filter. It still keeps the same sort order then. 

 

I didn't realise Power BI would consider a totally non-linked table to sort the current table but that was my issue and it is now resolved. Unfortunate that it took me almost an entire day to figure this out!

View solution in original post

6 REPLIES 6
v-jianpeng-msft
Community Support
Community Support

Thanks @Jonvoge and @kleigh .

Hi, @JS_UNI4C 

I'm using the data provided by your picture in Power BI:

vjianpengmsft_0-1712813090332.png

When I use the Level 1 order column to sort Level 1, I don't find any errors, as shown in the image below:

vjianpengmsft_1-1712813167248.png

But when I use the Level 2 order to sort the Level 2 columns, I reproduce the problem:

vjianpengmsft_2-1712813246966.png

For Level 2 columns, the column has multiple null values, and each null value corresponds to a different order, resulting in an error. You can right-click on your table and click on the copy table function to paste it into Excel, and then create a new table in Power BI to paste the table that from Excel. Level 1 is then re-ordered.

vjianpengmsft_3-1712813473181.png

vjianpengmsft_4-1712813507173.png

vjianpengmsft_5-1712813548651.png

 

 

 

 

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

 

Hi, 

 

Turns out that because I had another table in my data model that was using the same headers as this table, it was causing the conflict. I changed the table headers and it worked fine. 

 

How I get around the same "null" values is by going into power query editor and then filtering those out, going back to DAX and then set the sort order before going into power query editor and removing the filter. It still keeps the same sort order then. 

 

I didn't realise Power BI would consider a totally non-linked table to sort the current table but that was my issue and it is now resolved. Unfortunate that it took me almost an entire day to figure this out!

Hi, @JS_UNI4C 

Thank you very much for your reply and the solution you shared. I think your solution is fantastic. You can mark your response as a solution to help others in the community find answers quickly when they encounter the same problem.

 

 

 

 

How to Get Your Question Answered Quickly

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Jonvoge
Solution Supplier
Solution Supplier

Hi.

 

Could there be a sneaky leading or trailing 'space' or other similar character on one of the rows in either of the two columns? Try to run it through a Trim function in Power Query first and see if it makes a difference,

_____________________________________________________
I hope my comment was helpful.
If your question was answered, please mark your post as 'Solved' and consider giving me a 'Thumbs Up'.
Find me on LinkedIn, Sessionize, or my blog Downhill Data

Hi Jonvoge, 

 

I did think of that and tried that already. No such things which is why I am stumped and reached out here. I am completely bamboozled by this. 

Does DAX Query View turn up anything?

EVALUATE
SUMMARIZE('Your Table', [Level 1], [Level 1 Order])
 
If you only get each value once there for Level 1 Order it would seem to be a bug?

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.