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
Icon for Community Support rankCommunity 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"

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thanks,

     

    unluckily seems not working in my scenario. I get a blank column.

     

    • v-xuding-msft's avatar
      v-xuding-msft
      Icon for Community Support rankCommunity Support
      6 years ago

      Hi Anonymous ,

      Can you please share a few screenshots?

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi v-xuding-msft ,

         

        I manage to create my desired column with a few Pytonh lines, so that is OK now.

        Still my 'problem' on how to filter that on the individual' attributes remains, since that even my 'desired' column (which is already filtered from the original, as pet my example) includes too many attribures per line (screenshot at the end).

         

        I would need to filter that column to find e.g. all entry including 'N1 Barks'.

        If I slplit the column that does not work as said since I end up with 60+columns, I tried a few smart slicers but it's they do not handle so many columns. 

         

        For now the best solution I found is the 'Text Filter' visual which I downloaded from the MP, but that has limitations as well.

        Any hint on how to best do this ?

         

        Many thanks for your support!