Forum Discussion

PurdieB's avatar
PurdieB
Frequent Visitor
5 years ago
Solved

Power Query Text.Contains function to return true when there is a "like" value

I have a table with 2 concatenated columns that I am trying to flag True or False when a text value is in both columns.

Useing Text.Contains I am able so get a correct value when there is an exact match of the search column but not otherwise.

 

Match = if Text.Contains([Requirement 1], [Held Accomps]) then "True" else "False"

 

What I would like is something that produces the "DESIRED RESULT" column.

 

Held Accomps

Requirement 1MatchDESIRED RESULT

RN

RN , RN-TTrueTrue
RNRN , LPN , CCMATrueTrue
RN & ALDPRN , RN-TFalseTrue
CC & RNRN , RN-TFalseTrue
ALDP FalseFalse
RN-T & PALSRN , RN-T , LPNFalseTrue

 

Any help is appreciated!

Need Help
  • edhans's avatar
    edhans
    5 years ago

    Try this PurdieB 

     

        #"Added Custom" = 
        Table.AddColumn(
            #"Trimmed Text", 
            "Custom", 
            each 
                try
                List.Count(
                    List.Intersect(
                        {
                            List.Transform(Text.Split([Requirement 1], ","), Text.Trim),
                            List.Transform(Text.Split([Held Accomps], "&"), Text.Trim)
                        }
                    ) 
                )>0
                otherwise false

     

     

     

    You have to parse the values out into lists (the first uses & as a delimiter, the second uses a comma) then see where they intersect, then count the rows. If > 0, then true, else false.

     

    The one with null returns an error as it is an empty list, so the try/otherwise statement converts any error to false.

     

    The above is the M code from the advanced editor. You can just paste this into a new Custom Column, though it will be formatted wierdly.

                    try
                List.Count(
                    List.Intersect(
                        {
                            List.Transform(Text.Split([Requirement 1], ","), Text.Trim),
                            List.Transform(Text.Split([Held Accomps], "&"), Text.Trim)
                        }
                    ) 
                )>0
                otherwise false

     

1 Reply

  • edhans's avatar
    edhans
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Try this PurdieB 

     

        #"Added Custom" = 
        Table.AddColumn(
            #"Trimmed Text", 
            "Custom", 
            each 
                try
                List.Count(
                    List.Intersect(
                        {
                            List.Transform(Text.Split([Requirement 1], ","), Text.Trim),
                            List.Transform(Text.Split([Held Accomps], "&"), Text.Trim)
                        }
                    ) 
                )>0
                otherwise false

     

     

     

    You have to parse the values out into lists (the first uses & as a delimiter, the second uses a comma) then see where they intersect, then count the rows. If > 0, then true, else false.

     

    The one with null returns an error as it is an empty list, so the try/otherwise statement converts any error to false.

     

    The above is the M code from the advanced editor. You can just paste this into a new Custom Column, though it will be formatted wierdly.

                    try
                List.Count(
                    List.Intersect(
                        {
                            List.Transform(Text.Split([Requirement 1], ","), Text.Trim),
                            List.Transform(Text.Split([Held Accomps], "&"), Text.Trim)
                        }
                    ) 
                )>0
                otherwise false