cancel
Showing results for 
Search instead for 
Did you mean: 
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
v-cgao-msft
Community Support
Community Support

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!

v-cgao-msft
Community Support
Community Support

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
Exciting changes

Power BI Community Changes

Check out the changes to the Power BI Community announced at Build.

May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Kudo Data Story carousel

Data Stories Gallery

Visit our Data Stories Gallery and give kudos to your favorite Data Stories.

Top Solution Authors
Top Kudoed Authors