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

[DAXRichArd]

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"

)

[css207]

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

[DAXRichArd]

Hey css207.

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

Thx for your reply. I appreciate your consideration.

Have a great day!

[v-cgao-msft · Accepted solution]

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]

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.