Forum Discussion

DAXRichArd's avatar
DAXRichArd
Resolver I
3 years ago
Solved

Conditional column: grouping data, is there an 'in between' option?

Hello,

I work for an airport.

I am categorizing commercial aviation aircraft by the FAA's ASPM Weight Class (see https://aspm.faa.gov/aspmhelp/index/Weight_Class.html)

My data set is airline self reported data for landings.

I want to group aircraft into 4 categories based on ranges of weight in pounds.

  • Small <= 12500
  • Medium > 12500 but <= 41000
  • Large > 41000 but <= 255000
  • Heavy > 255000.

I've solved it for now creating a conditional column and using the order of execution going from small to large.

There are greater than equal to operator >=, less than <, .....

Question: is there a way to create an "inbetween" operator?

>< not equal to.

<> between?

 

Here is the query code from the conditional formating feature.

The targt column for the grouping is the [weight] column.

Thx in advance for all your help.

 

= Table.AddColumn(
#"Removed Columns",
"Aircraft Category",
each
if [weight] <= 12500 then
"Small"
else if [weight] <= 41000 then
"Medium"
else if [weight] <= 255000 then
"Large"
else if [weight] > 255001 then
"Heavy"
else
"NO MATCH"

)

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi DAXRichArd ,

     

    Please try:

    = Table.AddColumn(
#"Removed Columns", 
"Aircraft Category", 
each if [weight] <= 12500 then "Small" 
else if [weight] > 12500 and [weight] <= 41000 then "Medium" 
else if [weight] > 41000 and [weight] <= 255000 then "Large" 
else if [weight] > 255000 then "Heavy" 
else "NO MATCH"
)

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi DAXRichArd ,

     

    Please try:

    = Table.AddColumn(
#"Removed Columns", 
"Aircraft Category", 
each if [weight] <= 12500 then "Small" 
else if [weight] > 12500 and [weight] <= 41000 then "Medium" 
else if [weight] > 41000 and [weight] <= 255000 then "Large" 
else if [weight] > 255000 then "Heavy" 
else "NO MATCH"
)

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

    • DAXRichArd's avatar
      DAXRichArd
      Resolver I
      3 years ago

      Hi v-cgao-msft,

      IT WORKED!

      • In the screenshot below, from left to right, Aircraft Category is my original solution using CONDITIONAL COLUMN.
      • The next column 'v-cgao-msft' is a column created using CUSTOM COLUMN. I copied and pasted your solution above into custom column. That returned 'Table.'
      • The last column 'v-cgao-msft2' is what worked. I right clicked my mouse over the APPLIED STEPS and selected INSERT STEP AFTER, then pasted you solution in the formula bar and hit enter.
        • That worked!

      Big thx for all the help.

      I'm a novice at MQuery, however I can find solutions just hacking away. Hope to be a Master at it like you one day.

      Merry Christmas and have a wonderful New Year.

       

       

  • css207's avatar
    css207
    Frequent Visitor
    3 years ago

    As I know,there is no operator such as between.

    • DAXRichArd's avatar
      DAXRichArd
      Resolver I
      3 years ago

      Hey css207.

      Check out  v-cgao-msft solution using "and".

      Thx for your reply. I appreciate your consideration.

      Have a great day!