Yet another Sort by another Column error.

[Thomas_MedOne]

I've done research but so many issues out there that are not the same. This is my last resort...

I have a column: Day of Week and I have a column that I built in Power Query for the sorting. (Conditional column to output a number) The values are here:

Day of Week	DayNum
Monday	1
Tuesday	2
Wednesday	3
Thursday	4
Friday	5
Saturday	6
Sunday	7
HolidayPay	8
SuperHolidayPay	9

After creating the column in Powerquery and applying. I go to the Day of Week and I choose to sort by DayNum and I get the complaint that there are multiple values in one column for the same column. So..

I went through the long process of filtering only by Monday (then Tuesday, etc) for Day of Week and checking DayNum to see that the values are unique. Then I did the same filtering on DayNum and those values are unique.

So, both are unique for each type and number!  Why won't it sort?!  Is it a caching issue because I once had it duplicated but now I don't?

[Ritaf1983]

Hi @Thomas_MedOne 

The issue seems to be that the sorting you're performing is somehow conflicting with the order of the days. On the unique values table, everything works fine (see the attached image).

Therefore, what you need to do is sort your small table and link it to the large table using a one-to-many relationship. In the visualizations, you'll use the days from the small table. This will work.

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

[Thomas_MedOne]

My table itself of course has lots of other columns and there are lots of Mondays and Tuesdays, etc. and the "DayNum" column is in the same table. So I don't have a small table at all.  I guess I could add one but it seems overkill just to sort one column.

[Ritaf1983]

Hi @Thomas_MedOne 

Small table with 9 rows is very far from overkilling. It will solve the issue , and this is how pbi's engine like to work 🤓

[Thomas_MedOne]

No, but just creating a table to sort another table, that just seems unnecessary. perhaps in some instances it is not. The solution, ultimately, was to refresh the data, quit Power BI Desktop and load it back up again and it allowed me to sort. It must have been a caching issue from a previous attempt when I didn't have unique values.

Thanks for looking into it and even teaching something that I didn't know was a thing. I am pretty new at all this.

[blodus]

Restarting PBI worked for me too. I had created the Sort with the wrong FORMAT setting but when I changed it, I was still getting the error. 

Exiting out of PBI and reloading resolved the error and am it is now sorting on the new column. 

[Ritaf1983]

Happy to hear that the issue is solved.

You can accept your answer as solution.

According to small table and efficiency , note that the small table for sorting is only 18 cells.

When you adding to your table a column for sorting the number of cells is the same of the big table... 

[sjoerdvn]

Hard to tell. Execute a data refresh before setting the sort. Also, check for blanks or empty strings.

[Thomas_MedOne · Accepted solution]

I had to refresh the data and then quit Power BI Desktop and reload it up again and then it allowed me. It must have been a caching issue.