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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors