Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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...
Solved! Go to Solution.
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
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
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
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...;-)
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:
and then the function creates a new column with the categories.
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
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |