Column Contains Certain Values Formula

traviscook21 · Mar 27, 2019

Just wanted to say thank you for any help in advance. I have a table that has multiple values in one column and they are in no particular order so I can split them even though the values are separated by a comma within that column. I think I need to do a if this column contains these certain values I'm looking for then return "Contains" else "DoesNot". However the issues is that there are over 400 values I'm looking for and I don't want to have to write that massive if statement unless there is a better way then just doing the manual conditional column. Does anyone know of a better way to do this?

For example below is the column I'm trying to search. I have over 400 Procedure Codes that I need to search for.
Procedures

36254, 45981, 4225F
36620
28456, 45981, 36215
31239, 4255F, 4559F
27447, 4255F, 4559F, 64447, 64450, 76942
64454
87456
32546
88756
66645
27130
87789
27130, 4255F, 4559F
27130, 4255F, 4559F
19302, 4255F, 4559F
66984
66645
11640, 4255F, 4559F
66984
32546

Thanks!!!

horseyride · Mar 27, 2019

It is unclear. You want to to do which of these?
[a] find rows that contain all the values you are searching for
find rows that contain at least #XX matches with the values you are searching for
[c] find columns that contain all the values you are searching for
[d] find columns that contain at least #XX matches with the values your are searching for
[e] split each row into individual pieces and then find the new rows that match at least one item on the list of values you are searching for

And once found, what do we do with them? Mark them?

traviscook21 · Mar 27, 2019

horseyride said:
It is unclear. You want to to do which of these?
[a] find rows that contain all the values you are searching for
find rows that contain at least #XX matches with the values you are searching for
[c] find columns that contain all the values you are searching for
[d] find columns that contain at least #XX matches with the values your are searching for
[e] split each row into individual pieces and then find the new rows that match at least one item on the list of values you are searching for

And once found, what do we do with them? Mark them?

Option b.

I want to be able to create a new column that says if code 36620 is in the procedures column then put contains else doesnt. I could use a conditional column for each code i'm looking for however there are over 400 plus codes I need to look for in that procedures column. Make sense?

horseyride · Mar 27, 2019

View attachment Powerquery_ListMatches2.xlsx

See example. If Table1 contained the text to search with Column name "Text" and Table2 contained the list of text to search for with Column name "Keywords" then this counts the number of matches per row

Remove this part if you want, which ignores upper/lowercase: , Comparer.OrdinalIgnoreCase

Code:

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "MatchCount", each Table.RowCount(let CurrentText = [Text] in Table.SelectRows(Keywords,each Text.Contains(CurrentText, [Keywords], Comparer.OrdinalIgnoreCase))))
in #"Added Custom"

and this both counts the number of matches and shows the matches in a separate column

Code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Matches", each let CurrentText = [Text] in Table.SelectRows(Keywords,each Text.Contains(CurrentText, [Keywords], Comparer.OrdinalIgnoreCase))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Match", each Table.Column([Matches],"Keywords")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom1", {"Match", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Added Custom2" = Table.AddColumn(#"Extracted Values", "MatchCount", each Table.RowCount([Matches])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Matches"})
in
    #"Removed Columns"

and this would create a separate column for each matching item by row

Code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Matches", each let CurrentText = [Text] in Table.SelectRows(Keywords,each Text.Contains(CurrentText, [Keywords], Comparer.OrdinalIgnoreCase))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Match", each Table.Column([Matches],"Keywords")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom1", {"Match", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Matches"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Match", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Match.1", "Match.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Match.1", type text}, {"Match.2", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Text"}, "Attribute", "Value"),
    #"Added Custom2" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each 1),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom2",{"Attribute"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[Value]), "Value", "Custom", List.Sum)
in  #"Pivoted Column"

Ron Coderre · Mar 27, 2019

I put this data in an Excel Table named tblProcList and connected Power Query to it.

Then, I created this query:

Code:

let
    Source  = tblProcList,
    AddListOfProcs_Col = Table.AddColumn(Source, "List of Procs", each Text.Split([ProcList],", ")),
    AddMatch_Col = Table.AddColumn(AddListOfProcs_Col, "Match", 
                   each List.Contains(
[List of Procs],"36620")),
    Add_AllInOneCol_solution_Col = Table.AddColumn(AddMatch_Col, "All in One Col", 
                                    each List.Contains(Text.Split([ProcList],", "),"36620"))
in
    Add_AllInOneCol_solution_Col

Is that something you can work with?

horseyride · Mar 27, 2019

Ron - doesn't that only work for one code (36620)?

Ron Coderre · Mar 27, 2019

Yup...I'm starting small.
There are several ways the OP could go with this, depending on the needs and ability to maintain the code.
(I wrote a PQ App a few months ago that checked dozens of columns and hundreds of rows for a long list of text strings. The new owners picked up PQ quickly, so I was comfortable creating a comprehensive application with them)

traviscook21 · Mar 27, 2019

Ron Coderre said:
Yup...I'm starting small.
There are several ways the OP could go with this, depending on the needs and ability to maintain the code.
(I wrote a PQ App a few months ago that checked dozens of columns and hundreds of rows for a long list of text strings. The new owners picked up PQ quickly, so I was comfortable creating a comprehensive application with them)

This will work just fine! How do I incorporate my list of 400 other codes beside "36620" besides having to type them all out?

Ron Coderre · Mar 27, 2019

OK...I dove into a Power Query app that I previously wrote and extracted the key parts.
Using...This Excel Table named tblProcList

Code:

ProcList
36254, 45981, 4225F
36620
28456, 45981, 36215
31239, 4255F, 4559F
27447, 4255F, 4559F, 64447, 64450, 76942
64454
87456, 36620
32546
88756
66645
27130
87789
27130, 36620, 4255F, 4559F
27130, 4255F, 4559F
19302, 4255F, 4559F
66984
66645
11640, 4255F, 4559F
66984
32546

and...this Excel Table named tblTestText:

Code:

TestList
36620
4255F
4559F

This M-Code references the tblProcList and creates a new column that lists every matching item from tblTestText that is found in the current row:
Query Name: ListMatchingProcs
M-Code:

Code:

let
    Source = tblProcList,
    Add_MatchingKeywords_Col = Table.AddColumn(Source, "Matching Procs", 
        (This) => Text.Combine(List.Select(tblProcsToFind[TestList], each Text.Contains(This[ProcList], _)),", "))
in
    Add_MatchingKeywords_Col

For that data....these are the results:

Code:

ProcList                                      Matching Procs
36254, 45981, 4225F                    
36620                                         36620
28456, 45981, 36215                    
31239, 4255F, 4559F                           4255F, 4559F
27447, 4255F, 4559F, 64447, 64450, 76942      4255F, 4559F
64454                    
87456, 36620                                  36620
32546                    
88756                    
66645                    
27130                    
87789                    
27130, 36620, 4255F, 4559F                    36620, 4255F, 4559F
27130, 4255F, 4559F                           4255F, 4559F
19302, 4255F, 4559F                           4255F, 4559F
66984                    
66645                    
11640, 4255F, 4559F                           4255F, 4559F
66984                    
32546

Is that something you can use?

traviscook21 · Mar 27, 2019

Ron Coderre said:

OK...I dove into a Power Query app that I previously wrote and extracted the key parts.
Using...This Excel Table named tblProcList

Code:

ProcList
36254, 45981, 4225F
36620
28456, 45981, 36215
31239, 4255F, 4559F
27447, 4255F, 4559F, 64447, 64450, 76942
64454
87456, 36620
32546
88756
66645
27130
87789
27130, 36620, 4255F, 4559F
27130, 4255F, 4559F
19302, 4255F, 4559F
66984
66645
11640, 4255F, 4559F
66984
32546

and...this Excel Table named tblTestText:

Code:

TestList
36620
4255F
4559F

This M-Code references the tblProcList and creates a new column that lists every matching item from tblTestText that is found in the current row:
Query Name: ListMatchingProcs
M-Code:

Code:

let
    Source = tblProcList,
    Add_MatchingKeywords_Col = Table.AddColumn(Source, "Matching Procs", 
        (This) => Text.Combine(List.Select(tblProcsToFind[TestList], each Text.Contains(This[ProcList], _)),", "))
in
    Add_MatchingKeywords_Col

For that data....these are the results:

Code:

ProcList                                      Matching Procs
36254, 45981, 4225F                    
36620                                         36620
28456, 45981, 36215                    
31239, 4255F, 4559F                           4255F, 4559F
27447, 4255F, 4559F, 64447, 64450, 76942      4255F, 4559F
64454                    
87456, 36620                                  36620
32546                    
88756                    
66645                    
27130                    
87789                    
27130, 36620, 4255F, 4559F                    36620, 4255F, 4559F
27130, 4255F, 4559F                           4255F, 4559F
19302, 4255F, 4559F                          [COLOR=#ff0000] 4255F, 4559F[/COLOR]
66984                    
66645                    
11640, 4255F, 4559F                           4255F, 4559F
66984                    
32546

Is that something you can use?

Kind of. Instead of returning the matching codes (4255F, 4559F) like in the red text above, I just need it to return the word "Matches" if any of my codes in my lookup table match or "NoMatch" if not. Can you change the code for this result for me? I can probably figure it out going through the entire code but this would be very appreciated

Ron Coderre · Mar 27, 2019

For a quick solution (but maybe not the most efficient)...Try this:

Code:

let
    Source = tblProcList,
    Add_MatchingKeywords_Col = Table.AddColumn(Source, "Matching Procs", 
        (This) => Text.Length(Text.Combine(List.Select(tblProcsToFind[TestList], each Text.Contains(This[ProcList], _)),", "))>0)
in
    Add_MatchingKeywords_Col

Edited to include this shorter version:

Code:

[FONT=Verdana]let[/FONT]
[FONT=Verdana]    Source = tblProcList,[/FONT]
[FONT=Verdana]    Add_MatchingKeywords_Col [/FONT]= Table.AddColumn(Source, "Matching Procs", (This) => List.Count(List.Select(tblProcsToFind[TestList], 
                                                each Text.Contains(This[ProcList], _)))>0)
[FONT=Verdana]in[/FONT]
[FONT=Verdana]    Add_MatchingKeywords_Col
[/FONT]

Does that help?

traviscook21 · Mar 27, 2019

Ron Coderre said:

For a quick solution (but maybe not the most efficient)...Try this:

Code:

let
    Source = tblProcList,
    Add_MatchingKeywords_Col = Table.AddColumn(Source, "Matching Procs", 
        (This) => Text.Length(Text.Combine(List.Select(tblProcsToFind[TestList], each Text.Contains(This[ProcList], _)),", "))>0)
in
    Add_MatchingKeywords_Col

Edited to include this shorter version:

Code:

[FONT=Verdana]let[/FONT]
[FONT=Verdana]    Source = tblProcList,[/FONT]
[FONT=Verdana]    Add_MatchingKeywords_Col [/FONT]= Table.AddColumn(Source, "Matching Procs", (This) => List.Count(List.Select(tblProcsToFind[TestList], 
                                                each Text.Contains(This[ProcList], _)))>0)
[FONT=Verdana]in[/FONT]
[FONT=Verdana]    Add_MatchingKeywords_Col
[/FONT]

Does that help?

This is perfect. Problem solved!! You saved me having to write a nested if with over 450 else if's lol. Thank you so much.

traviscook21 · Mar 27, 2019

Ron Coderre said:

For a quick solution (but maybe not the most efficient)...Try this:

Code:

let
    Source = tblProcList,
    Add_MatchingKeywords_Col = Table.AddColumn(Source, "Matching Procs", 
        (This) => Text.Length(Text.Combine(List.Select(tblProcsToFind[TestList], each Text.Contains(This[ProcList], _)),", "))>0)
in
    Add_MatchingKeywords_Col

Edited to include this shorter version:

Code:

[FONT=Verdana]let[/FONT]
[FONT=Verdana]    Source = tblProcList,[/FONT]
[FONT=Verdana]    Add_MatchingKeywords_Col [/FONT]= Table.AddColumn(Source, "Matching Procs", (This) => List.Count(List.Select(tblProcsToFind[TestList], 
                                                each Text.Contains(This[ProcList], _)))>0)
[FONT=Verdana]in[/FONT]
[FONT=Verdana]    Add_MatchingKeywords_Col
[/FONT]

Does that help?

My table (in this scenario tblProcList) is around 350,000 rows. This code you provided me works however it takes a really long time to refresh/update. tblProcList is connected to a database. Do you have any recommendations on how to speed this up some?

Thanks,

Ron Coderre · Mar 27, 2019

That's a lot of heavy lifting for this kind of application.
I'd start by making sure you have these Power Query Options set:
GLOBAL
Data Load
- Specify custom default load settings
--Un-check: Load to worksheet
--Un-check: Load to Data Model
- Check: Fast Data Load

Privacy
- Select: Always ignore Privacy Level settings

CURRENT WORKBOOK
- Background Data
-- Un-check: Allow data preview to download in the background

Column Contains Certain Values Formula

traviscook21

New member

horseyride

New member

traviscook21

New member

horseyride

New member

Ron Coderre

Member

horseyride

New member

Ron Coderre

Member

traviscook21

New member

Ron Coderre

Member

traviscook21

New member

Ron Coderre

Member

traviscook21

New member

traviscook21

New member

Ron Coderre

Member