Forum Discussion
Post ProdigyConditional Column Question
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?
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 _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
10 Replies
bdehningI have a Decimal Number Column that provides Years Employed. I use that to create the range of years.
grantsambornSolution Sage
Just add 1 more Else-IF to check if LOE By Year is less than 51.
- 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.
- 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
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.
v-yanjiang-msftCommunity Support
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 _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.