Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
@Anonymous,
Not sure why it was removed.
I will re-post:
I duplicated your value column. Check this so you can see.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck4sUtJRMjRQitWJVsrNL8kvSq5MzkkFihmbgsVKikqTs4FcI2MwNxms3hiiHqLZ2ATMCYEqNLQAc32RzTIEKokFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Type = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Value", type number}}), #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Value", "Value - Copy"), #"Changed Type1" = Table.TransformColumnTypes(#"Duplicated Column",{{"Value - Copy", type text}}) in #"Changed Type1"
Then I change a little bit your code:
Column = IF('Table (4)'[Type] = "Car", SWITCH( TRUE(), 'Table (4)'[Value] <= 26, "24", 'Table (4)'[Value] <= 31, "23", 'Table (4)'[Value] <= 35, "22", 'Table (4)'[Value] <= 39, "21", 'Table (4)'[Value] <= 43, "20", 'Table (4)'[Value] <= 48, "19", 'Table (4)'[Value] <= 52, "18", 'Table (4)'[Value] <= 58, "17", 'Table (4)'[Value] <= 64, "16", 'Table (4)'[Value] <= 71, "15", 'Table (4)'[Value] <= 77, "14", 'Table (4)'[Value] <= 86, "13", 'Table (4)'[Value] <= 95, "12", 'Table (4)'[Value] <= 106, "11", 'Table (4)'[Value] >= 107, "10"), 'Table (4)'[Value - Copy] & "%")
You will see the changes inside the red boxes
Just take note that your column now is treated as text data type. You cannot aggregate this column.
hi @Anonymous ,
@Anonymous answered your question, right?
This has been solved?
no, need help
hi,
Maybe its possible in m query as well?
Hi,
Not sure if its the best way to do it but you can create a new column:
My values are from
Another way to do it is to create a lookuptable with the ranges, somthing like this:
(I created this in excel)
And then create a new column in main table:
tahnx
Im sorry but I can't do this for you, but I'll try to explain step by step:
Step 1:
Create a new table (in excel or power bi) with an Index column 1-120 (or as far as you want the ranges to go).
In the next column you put the new value the car shoud get.
Like this:
Car Value
1 31
2 31
3 30
4 30
5 30
6 30
7 29
8 29
9 29
10 29
...
Step 2:
Load this excel into power bi.
In relationship view, create a relationship between the index column ("Car Value" in this example) and the value column in original table.
Step 3:
Create a new column in power bi original table with DAX formula:
NewCarValue = IF(MainTable[Type] = "Car"; RELATED(CarRangeValue[New Value]); MainTable[Value])
You might have to change the table and column names to match your data.
/Adrian
lets
hi @Anonymous ,
Since you don't want to add an excel file, try this:
Column = IF('Table (4)'[Type] = "Car", SWITCH( TRUE(), 'Table (4)'[Value] <= 26, 24, 'Table (4)'[Value] <= 31, 23, 'Table (4)'[Value] <= 35, 22, 'Table (4)'[Value] <= 39, 21, 'Table (4)'[Value] <= 43, 20, 'Table (4)'[Value] <= 48, 19, 'Table (4)'[Value] <= 52, 18, 'Table (4)'[Value] <= 58, 17, 'Table (4)'[Value] <= 64, 16, 'Table (4)'[Value] <= 71, 15, 'Table (4)'[Value] <= 77, 14, 'Table (4)'[Value] <= 86, 13, 'Table (4)'[Value] <= 95, 12, 'Table (4)'[Value] <= 106, 11, 'Table (4)'[Value] >= 107, 10), 'Table (4)'[Value])
Based form your provided data, here is the result:
please look at the attach image.
@mussaenda
did ur last post get deleted? didnt get anything but did get a notificaton
@Anonymous,
Not sure why it was removed.
I will re-post:
I duplicated your value column. Check this so you can see.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck4sUtJRMjRQitWJVsrNL8kvSq5MzkkFihmbgsVKikqTs4FcI2MwNxms3hiiHqLZ2ATMCYEqNLQAc32RzTIEKokFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Type = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Value", type number}}), #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Value", "Value - Copy"), #"Changed Type1" = Table.TransformColumnTypes(#"Duplicated Column",{{"Value - Copy", type text}}) in #"Changed Type1"
Then I change a little bit your code:
Column = IF('Table (4)'[Type] = "Car", SWITCH( TRUE(), 'Table (4)'[Value] <= 26, "24", 'Table (4)'[Value] <= 31, "23", 'Table (4)'[Value] <= 35, "22", 'Table (4)'[Value] <= 39, "21", 'Table (4)'[Value] <= 43, "20", 'Table (4)'[Value] <= 48, "19", 'Table (4)'[Value] <= 52, "18", 'Table (4)'[Value] <= 58, "17", 'Table (4)'[Value] <= 64, "16", 'Table (4)'[Value] <= 71, "15", 'Table (4)'[Value] <= 77, "14", 'Table (4)'[Value] <= 86, "13", 'Table (4)'[Value] <= 95, "12", 'Table (4)'[Value] <= 106, "11", 'Table (4)'[Value] >= 107, "10"), 'Table (4)'[Value - Copy] & "%")
You will see the changes inside the red boxes
Just take note that your column now is treated as text data type. You cannot aggregate this column.
Hi @Anonymous ,
this can be.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck4sUtJRMjRQitWJVsrNL8kvSq5MzkkFihmbgsVKikqTs4FcI2MwNxms3hiiHqLZ2ATMCYEqNLQAc32RzTIEKokFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Type = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Value", type number}}), #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Value", "Value - Copy"), #"Changed Type1" = Table.TransformColumnTypes(#"Duplicated Column",{{"Value - Copy", type text}}) in #"Changed Type1"
I duplicated your value column on power query and set hte data type as text.
Then the code I changed a little bit. Take note of the red boxes.
Take note that your column is now treated as text. this cannot be aggregated.
Column = IF('Table (4)'[Type] = "Car", SWITCH( TRUE(), 'Table (4)'[Value] <= 26, "24", 'Table (4)'[Value] <= 31, "23", 'Table (4)'[Value] <= 35, "22", 'Table (4)'[Value] <= 39, "21", 'Table (4)'[Value] <= 43, "20", 'Table (4)'[Value] <= 48, "19", 'Table (4)'[Value] <= 52, "18", 'Table (4)'[Value] <= 58, "17", 'Table (4)'[Value] <= 64, "16", 'Table (4)'[Value] <= 71, "15", 'Table (4)'[Value] <= 77, "14", 'Table (4)'[Value] <= 86, "13", 'Table (4)'[Value] <= 95, "12", 'Table (4)'[Value] <= 106, "11", 'Table (4)'[Value] >= 107, "10"), 'Table (4)'[Value - Copy] & "%")
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |