Forum Discussion

HenryJS's avatar
HenryJS
Icon for Post Prodigy rankPost Prodigy
6 years ago
Solved

If Column Contains three 'strings'

Hi all,

 

Is it possible to create a new column which states IF [Disciplines] =< 3 'Strings' THEN Yes

 

A string being anything with a , between it.

 

So Works Manager is one string

 

Technician is one string etc..

 

 

Data Protection
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • V-lianl-msft's avatar
    V-lianl-msft
    6 years ago

    Hi HenryJS ,

     

    You can also try using DAX to create a calculated column.

    Column = IF(SEARCH( "*,*,*", 'Table'[Disciplines], 1, 0 ) > 0,BLANK(),"Yes")

     

    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

6 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    6 years ago

    HenryJS what defines the string, space between words or what? you gave the example what is one string but what 3 string, please provide the logic.

    • HenryJS's avatar
      HenryJS
      Icon for Post Prodigy rankPost Prodigy
      6 years ago

      Hi parry2k 

       

      In this instance anything which has , besides it.

       

      So we could do a statement which states if there are more than 2 "," THEN No

       

      Does that make sense?

       

      So if the field has more than 2 "," anywhere in the text THEN NO ELSE YES

      • parry2k's avatar
        parry2k
        Icon for Super User rankSuper User
        6 years ago

        HenryJS I would recommend adding a custom column in power query to get the count and then you can derive your logic from there

         

        here is a quick M code which you can paste in blank query and check it out and later add in to your query.

         

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxK1oFipVidaKWKyiowjSyWmJiog4TBYsnJyTpArBQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "String Count", each List.Count( Text.Split([Column1],",")), Int64.Type)
in
    #"Added Custom"

         

  • V-lianl-msft's avatar
    V-lianl-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi HenryJS ,

     

    You can also try using DAX to create a calculated column.

    Column = IF(SEARCH( "*,*,*", 'Table'[Disciplines], 1, 0 ) > 0,BLANK(),"Yes")

     

    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.