Forum Discussion
help with if formula asap
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.
lets
Community Championhi 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
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] & "%") mussaenda
did ur last post get deleted? didnt get anything but did get a notificaton- mussaenda
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.