Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
DAXRichArd
Resolver I
Resolver I

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"

)

 

DAXRichArd_0-1671489939424.png

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

4 REPLIES 4
css207
Frequent Visitor

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

Hey css207.

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

Thx for your reply. I appreciate your consideration.

Have a great day!

Anonymous
Not applicable

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

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.

DAXRichArd_2-1671621095220.png

 

 

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.