Forum Discussion

bdehning's avatar
bdehning
Icon for Post Prodigy rankPost Prodigy
3 years ago
Solved

Conditional 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...
Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    3 years ago

    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's avatar
bdehning
Icon for Post Prodigy rankPost Prodigy
3 years ago

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

grantsamborn's avatar
grantsamborn
Icon for Solution Sage rankSolution Sage
3 years ago

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's avatar
    bdehning
    Icon for Post Prodigy rankPost Prodigy
    3 years ago

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