Forum Discussion
Syndicate_Admin
4 years agoAdministrator
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 ...
- 4 years ago
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.
sevenhills
4 years agoSuper User
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.