Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Conditional column with does NOT include and OR criteria

Hello,   what is the best way to set up a conditional column in the following scenario:   COLUMN DESIRED OUTCOME (isolate all 'C') A. Red, A.Green, A. Violet, B. Pink, C. Brown C. Brown ...
Need Help
Tips and Tricks
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Thanks, 

yes it's ugly - with the addition that my data has many more attributes than the colours in my example (all in the same nasty format). If I split the column I end up with approx. 50 columns, too much too handle for any single slicer or smart slicer visual.

 

Any hints? There has to be  a way to do this ....🤔

 

v-xuding-msft's avatar
v-xuding-msft
Community Support
6 years ago

Hi Anonymous ,

You could have a try in Power Query like below.

= Text.Combine(List.Select(Text.Split([COLUMN],","),each Text.Contains(Value.As(_,type text),"C.")),",")

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WctRTCEpN0VFw1HMvSk3NAzEUwjLzc1JLdBSc9BQCMvOydRSc9RScivLL85R0lODMWB2QZkxNMAVgVnhGZkkqki4kQZB+uAVAhlNOaSrYIKBzgDrySnNyoHaQ4EAdhKSzHsQgpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [COLUMN = _t, #"DESIRED OUTCOME (isolate all 'C')" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"COLUMN", type text}, {"DESIRED OUTCOME (isolate all 'C')", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Combine(List.Select(Text.Split([COLUMN],","),each Text.Contains(Value.As(_,type text),"C.")),","))
in
    #"Added Custom"