Conditional column automatically two outputs are changing format and sort in bar chart not working

[Sayonip]

I have a power bi report which combines some employee database excels from Sharepoint.

I created a conditional column which checks a column named "Service" and enters range of years of service in the new column named "Service age". For example an employee with 7 years of service will have "6-10" shown in the column or an employee with 2 years of experience will have "5 and less". But once I update the output as "6-10" and "10-15", it auto updates as a date like #date(2024,10,15). I have tried to correct the same using the Advanced Editor but not working.

The Bar chart and the Table in the Dashboard also doesnot sort properly. The issue is only for these ranges. Other details are getting sorted properly. Attaching related Screenshot.

[nandic · Accepted solution]

@Sayonip mark this column to be of Text type (ABC), then go to that step on the right side, click on it:

Then in formula above, replace #date(2024, 6, 10) with "6-10".

Example, instead of:

= Table.AddColumn(#"Changed Type", "Service Age", each if [Service] <= 5 then "0-5" else if [Service] <= 10 then #date(2024, 6, 10) else if [Service] <= 15 then #date(2024, 11, 15) else if [Service] <= 20 then "16-20" else if [Service] <= 25 then "21-25" else if [Service] <= 30 then "26-30" else "31-100")

it will be
= Table.AddColumn(#"Changed Type", "Service Age", each if [Service] <= 5 then "0-5" else if [Service] <= 10 then "6-10" else if [Service] <= 15 then "11-15" else if [Service] <= 20 then "16-20" else if [Service] <= 25 then "21-25" else if [Service] <= 30 then "26-30" else "31-100")

As output is text column, sorting will be an issue.
So add another conditional column which will return number output (instead of "0-5" it will be 0, instead of "6-10" it will be 6...) and then when you apply settings in Power Query...

Go to the right side, select column "Service Age" which is text, and then on top choose option "Sort by column" and then choose that new column which is number.
By doing this, you will show on visuals "Service Age", but it will be properly sorted.

Cheers,
Nemanja Andic

[Sayonip]

Hi @nandic,

Thanks so much for the help. It worked very well. I was struggling with this from some time. Thanks again. 😀