Forum Discussion
I need help
Hello
I need to make this new column ProductType for what it asks me and I do not know what function / functions to use, thanks in advance:
Product Type. It will store the type of product being marketed. Be Meat for those products whose Variety Code start with C, Fish for those products whose Variety Code start with P y Fruits and Vegetables for those products whose Code of Variage start with F.
You can do either in DAX or power query. I prefer Power Query.
DAX, you can do with "Switch" and "Contains", "ContainsString"
- https://dax.guide/containsstring/
- https://dax.guide/containsstringexact/
- https://dax.guide/contains/
- https://dax.guide/switch/
DAX - Add new Column:
Product Type Column = var _pt_1stChar = LEFT('Table'[Variety Code], 1) RETURN SWITCH( TRUE(), _pt_1stChar = "C", "Meat", _pt_1stChar = "P", "Fish", _pt_1stChar = "F", "Fruits & Vegetables" )DAX - Add new measure:
Product Type Measure = var _pt_1stChar = LEFT(SELECTEDVALUE('Table'[Variety Code]), 1) RETURN SWITCH( TRUE(), _pt_1stChar = "C", "Meat", _pt_1stChar = "P", "Fish", _pt_1stChar = "F", "Fruits & Vegetables" )Power query, you can do with "Text.StartsWith" or "Text.Contains" per your needs and Conditional column:
- https://docs.microsoft.com/en-us/powerquery-m/text-startswith
- https://exceltown.com/en/tutorials/power-bi/power-query-get-and-transform/conditional-column-in-power-query-and-in-power-pivot-queries/
- https://www.mssqltips.com/sqlservertip/6362/conditional-column-in-power-bi/
- https://radacad.com/conditional-column-in-power-bi-using-power-query-you-can-do-anything
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck4EA6VYnWilZAQzIB9JvCAfwXYrggEwN62oCEkgFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Variety Code" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Variety Code", type text}}), #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Product Type", each if Text.StartsWith([Variety Code], "C", Comparer.OrdinalIgnoreCase) then "Meat" else if Text.StartsWith([Variety Code], "P", Comparer.OrdinalIgnoreCase) then "Fish" else if Text.StartsWith([Variety Code], "F", Comparer.OrdinalIgnoreCase) then "Fruits & Vegetables" else null) in #"Added Conditional Column"I dont know spanish, sorry, I used some text for Variety code to get the code for you.
2 Replies
A switch statement ought to do it.
ProductType = SWITCH( TRUE(), VarietyCode = "C", "Meat", VarietyCode = "P", "Fish", VarietyCode = "F", "Fruits & Vegetables" )You can do either in DAX or power query. I prefer Power Query.
DAX, you can do with "Switch" and "Contains", "ContainsString"
- https://dax.guide/containsstring/
- https://dax.guide/containsstringexact/
- https://dax.guide/contains/
- https://dax.guide/switch/
DAX - Add new Column:
Product Type Column = var _pt_1stChar = LEFT('Table'[Variety Code], 1) RETURN SWITCH( TRUE(), _pt_1stChar = "C", "Meat", _pt_1stChar = "P", "Fish", _pt_1stChar = "F", "Fruits & Vegetables" )DAX - Add new measure:
Product Type Measure = var _pt_1stChar = LEFT(SELECTEDVALUE('Table'[Variety Code]), 1) RETURN SWITCH( TRUE(), _pt_1stChar = "C", "Meat", _pt_1stChar = "P", "Fish", _pt_1stChar = "F", "Fruits & Vegetables" )Power query, you can do with "Text.StartsWith" or "Text.Contains" per your needs and Conditional column:
- https://docs.microsoft.com/en-us/powerquery-m/text-startswith
- https://exceltown.com/en/tutorials/power-bi/power-query-get-and-transform/conditional-column-in-power-query-and-in-power-pivot-queries/
- https://www.mssqltips.com/sqlservertip/6362/conditional-column-in-power-bi/
- https://radacad.com/conditional-column-in-power-bi-using-power-query-you-can-do-anything
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck4EA6VYnWilZAQzIB9JvCAfwXYrggEwN62oCEkgFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Variety Code" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Variety Code", type text}}), #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Product Type", each if Text.StartsWith([Variety Code], "C", Comparer.OrdinalIgnoreCase) then "Meat" else if Text.StartsWith([Variety Code], "P", Comparer.OrdinalIgnoreCase) then "Fish" else if Text.StartsWith([Variety Code], "F", Comparer.OrdinalIgnoreCase) then "Fruits & Vegetables" else null) in #"Added Conditional Column"I dont know spanish, sorry, I used some text for Variety code to get the code for you.
