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

 

  • 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

     

     

     

7 Replies

  • 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

       

      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'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

    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