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
OH
Regular Visitor

How to best categorize data based on ranges

Hello all

 

Recently (some weeks ago) I entered the world of PowerBI for analyzing (big) data.

When typing my subject I already read the proposed other articles but non of them really answered my question.

Or I am not yet at a level to understand the answers...

 

Currently I am running a check tool over our CAD-Data

One output is e.g. the volume of a body in the CAD part.

Now I would like to categorize the data depending on the Volume range, i.e.

01: 0-0.1l

02: 0.1-0.2l

03: 0.2-0.5l

04: 0.5-1.0l and so on. I guess you get the idea...

 

First I thought formulas and if/else might do the trick.

But I very soon realized, that programming language/syntax is not at all my key competence.

 

Then I found in the Query Editor the "Conditioned Column", which fairly good meets my needs.
At least I am able to workaround on a user-friendly way and get results despite my poor software skills.

Yet I wonder if there isn't another function that might be better for it or has already this built-in capabilities.

 

As currently I have to "re-type" all 13 or so categories for each dimension column I want to examine (e.g. length, mass, etc.).

Is there a way to somehow re-use the range 0-0.1, 0.1-0.2, etc. in a Table or so?

 

Any hints (if not too deep on the programming side) are welcome...

 

1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

Hi @OH

Assume you have a simple dataset with a column which shows the volume of a body.

First you need create a new table by entering data to show the the Volume range

1.png

 

Second in your dataset, create columns

category num = IF([volumne]<=0.1,1,IF([volumne]<=0.2,2,IF([volumne]<=0.5,3,IF([volumne]<=1,4))))

Next create relstionship between your dataset and new created table by [category num].

 

 

2.png

 

Then create a column

 

range = RELATED(Table1[category range])

 3.png

 

Best Regards

maggie

 

 

 

View solution in original post

7 REPLIES 7
v-juanli-msft
Community Support
Community Support

Hi @OH

Assume you have a simple dataset with a column which shows the volume of a body.

First you need create a new table by entering data to show the the Volume range

1.png

 

Second in your dataset, create columns

category num = IF([volumne]<=0.1,1,IF([volumne]<=0.2,2,IF([volumne]<=0.5,3,IF([volumne]<=1,4))))

Next create relstionship between your dataset and new created table by [category num].

 

 

2.png

 

Then create a column

 

range = RELATED(Table1[category range])

 3.png

 

Best Regards

maggie

 

 

 

Hello Maggie

 

I tried it as proposed.

I created a user defined column in the query editor and entered this formula:

IF([Volumen]<10,1,IF([Volumen]<20,2,IF([Volumen]<50,3,IF([Volumen]<90,4))))

Unfortunately the editor returned an Expression.Error: "The name "IF" was not recognized. Is it spelled correctly?"
(I use the german version and translated the error message, so it might differ from the english output.

 

Here is the Screenshotpikt01.jpg

OH
Regular Visitor

 

pikt01.jpg

Hello Maggie

 

I tried again, this time with reading some post about if-Function and Power Query language and spelling issues.

 

I get it to run with the typing as on the left side.

 

But interestingly, when I try to edit such a created column with clicking on the gearwheel

pikt02.jpg (sorry, you cannot see it, i am not yet familiar with this community editor

 

Anyway, when I want to edit, not the effortful typed formula appears but this dialog:

pikt03.jpg

 

Which is basically just where I started from before posting my initial question!

Maybe you haven't been aware of this fact.

So I guess beside my ER there is no really solution yet to my question...;-)

 

OH
Regular Visitor

Hello Maggie

 

As I have worked the last few days/weeks mainly with the Power Query Editor inside PowerBI Desktop I thought this is the right tool for such tasks. But now I found that the steps you proposed have to be taken in the dataset tab instead.

And there the uppercase Spelling and so on works.

 

Anyway: the two different locations where a user can edit tables are not easy for a beginner to find out the difference...

 

Best regards and a nice weekend

Hi 

Sorry for not leading you clearly and efficiently, I would pay attention later.

 

Best Regards

Maggie

Hello maggie

 

Thanks for the explanations, they are just the right level for me to reproduce...;-)

 

Apart from the technique of second table for the ranges and the relationsship creation,

the sentence: "Next in your dataset, create columns..." followed by the formula, was basically the eye-opener:

I now realize that somewhere in the process I MUST have a kind of "IF" construct to tell the software, how to categorize.

 

But as you seem to belong to the PowerBI Team maybe this might an enhancement request (feel free to forward it to the right place, if you are not the person in charge for ERs)

 

I believe it is a very common usecase, that data contains values within a min and max (numerical) value.

And often it is helpful to categorize those data by dividing the min/max range in a certain number of "from-to" ranges ("categories") either linear or logaritmic.

 

Now a function/GUI would be desirable that allows the user (after as selection of a column containing values) to select/enter:

  • Number of ranges to divide the total min max range (thus calculating the Stepwidth fo each category)
  • Or Stepwidth (thus calculating the Number of Ranges)
  • linear or logaritmic division of ranges
  • A (semi-automatic) naming Scheme for the categories to be created

and then the function creates a new column with the categories.

 

OH
Regular Visitor

Sorry, It seems I have posted my Article double. As I was not yet registered, I thought the first post was lost and so typed again.

But obviously it was saved as well.

 

Maybe I receive now double the answers...

 

Best regards

Olaf

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.