Forum Discussion
Yet another Sort by another Column error.
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?
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.
10 Replies
Hard to tell. Execute a data refresh before setting the sort. Also, check for blanks or empty strings.
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.
THANK YOU!!!! I was about to lose it. Quitting Power BI and restarting fixed the issue.
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
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.
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 🤓
