Forum Discussion

InsightSeeker's avatar
InsightSeeker
Icon for Helper III rankHelper III
2 years ago
Solved

Need help with DAX

I need help to achieve the results shown in the table below.

 

Essentially, in the 'Category' column, if all the values are the same, then return the first value; otherwise, return 'Mix'.

 

Table

Category
Apple-Apple
Apple-Grapes
Apple
Grapes-Grapes
Grapes-Apple-Apple-Grapes

Grapes

 

Result

CategoryResult
Apple-AppleApple
Apple-GrapesMix
AppleApple
Grapes-GrapesGrapes
Grapes-Apple-Apple-GrapesMix
GrapesGrapes
Need Help
Show and Tell
  • amitchandak's avatar
    amitchandak
    2 years ago

    InsightSeeker ,  Add new custom column in Power Query

    let
    _list = List.Distinct( Text.Split([Category],"-")),
    _res= if List.Count(_list)=1 then List.Max(_list) else "Mix"
    in
    _res

     

     

     

    Full Code - Paste in Blank query in Power query

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwoyEnVBZNKsTowvntRYkFqMUIAzIIIIstBRZDMwJRVio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let  
_list = List.Distinct( Text.Split([Category],"-")),
_res= if List.Count(_list)=1 then  List.Max(_list) else "Mix"
in 
_res)
in
    #"Added Custom"

     

1 Reply

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    2 years ago

    InsightSeeker ,  Add new custom column in Power Query

    let
    _list = List.Distinct( Text.Split([Category],"-")),
    _res= if List.Count(_list)=1 then List.Max(_list) else "Mix"
    in
    _res

     

     

     

    Full Code - Paste in Blank query in Power query

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwoyEnVBZNKsTowvntRYkFqMUIAzIIIIstBRZDMwJRVio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let  
_list = List.Distinct( Text.Split([Category],"-")),
_res= if List.Count(_list)=1 then  List.Max(_list) else "Mix"
in 
_res)
in
    #"Added Custom"