Forum Discussion

OH's avatar
OH
Regular Visitor
8 years ago
Solved

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 ...
  • v-juanli-msft's avatar
    v-juanli-msft
    8 years ago

    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

     

    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].

     

     

     

    Then create a column

     

    range = RELATED(Table1[category range])

     

     

    Best Regards

    maggie

     

     

     

v-juanli-msft's avatar
v-juanli-msft
Community Support
8 years ago

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

 

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].

 

 

 

Then create a column

 

range = RELATED(Table1[category range])

 

 

Best Regards

maggie

 

 

 

OH's avatar
OH
Regular Visitor
8 years ago

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 Screenshot

  • OH's avatar
    OH
    Regular Visitor
    8 years ago

     

    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

     (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:

     

    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's avatar
      OH
      Regular Visitor
      8 years ago

      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

      • v-juanli-msft's avatar
        v-juanli-msft
        Community Support
        8 years ago

        Hi 

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

         

        Best Regards

        Maggie