Conditional Column Question

[bdehning]

Let me try this question another way.    I have the following Conditional Column that works for Numerical sorting. How can I add an Else if to continue from 5-50 without having to create 46 more Else If?     

[v-yanjiang-msft · Accepted solution]

Hi @bdehning ,

According to your description, here's my solution.

Add a custom column in Power Query.

if [LOE By Year]=null then "No Data" else if[LOE By Year]<1 then "< 1 yr"else if [LOE By Year]<2 then "1 yr" else Text.Combine({Number.ToText(Number.RoundDown([LOE By Year]))," yrs"})

Result:

Then in Desktop, select the LOE Range column, click Column tools>Sort by column>LOE By Year.

Get the expected result:

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

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

[bdehning]

 If a person had need for a smaller conditiotnal column, how do make No Data or Null stay ahead of <1 yr  when sorting?

[grantsamborn]

That's where the SortOrder column comes in.  In the Data view, select the description and use Sort By Column to sort by the SortOrder column.

[bdehning]

But I want to see if they do exist in data 6 yrs, 7 yrs, 8, yrs, 9 yrs, etc?

[grantsamborn]

If you want 50 buckets, you will probably need 50 lines of conditions unless you create a bucket table with the 50 buckets as well as upper and lower boundaries and a sort order column.

[bdehning]

Ok the  Bucket Example is probably the best route and cleanest reather than 50 or more Else If's.  

[bdehning]

What do I use as Output if:   Else if LOE by Year is less than 51 Then ????

[grantsamborn]

I'm guessing "5-50" based on your initial post.

[grantsamborn]

Just add 1 more Else-IF to check if LOE By Year is less than 51.

[bdehning]

I have a Decimal Number Column that provides Years Employed.   I use that to create the range of years.