Text.Contains form another query/table

[jtomczyk]

Hi,
I have a database in which I have service intervensions. I would need to assign those intervensions based on the key-words in the descritions. The tricky part is that recently we have implemented new codes in the descriptions, so those one should be handled in different way.

In query "ACCServiceCall" I have a column "CallDescription"

I need to create new column "Custom" based on the strings that are in that "CallDescription" column. 
I have creates such code that works.

= if Text.Contains([CallDescription], "plc", Comparer.OrdinalIgnoreCase)= true then "HR01" else
(if Text.Contains([CallDescription], "visit", Comparer.OrdinalIgnoreCase)= true then "HR02" else

null))
(I have a list of 100 codes but to make it simple I have reduced that list to two only)

The problem starts here. I need to add a condition that for the strings that exist in another query "ServiceCodes", column "ServiceCode" (for example HR12, HR13...PR07, see below) shound result in new columnt as "New code exist" 

The code should looks something like below, where XXXXX is a part of the code that I do not know...

= if Text.Contains([CallDescription], XXXXX, Comparer.OrdinalIgnoreCase)= true then "New code exist" else
if Text.Contains([CallDescription], "plc", Comparer.OrdinalIgnoreCase)= true then "HR01" else
(if Text.Contains([CallDescription], "visit", Comparer.OrdinalIgnoreCase)= true then "HR02" else

null))

The final result should looks like that

Thanks for helping me to solve that problem.

[BA_Pete · Accepted solution]

Hi @jtomczyk ,

Try this as the first evaluation line:

= Table.AddColumn(previousStep, "yourNewColumnName", each if
  List.AnyTrue(
    List.Transform(
      ServiceCodes[ServiceCode],
      (x) => Text.Contains([CallDescription], x)
    )
  ) then "New Code Exists" else CONTINUE YOUR OTHER EVALUATIONS

Pete

[jtomczyk]

you're a genius!

[BA_Pete]

Lol, thanks 🙂

FYI, you can also mark my second answer post as a solution if you feel it solved a different problem to the original post.

Pete

[BA_Pete · Accepted solution]

Hi @jtomczyk ,

Try this as the first evaluation line:

= Table.AddColumn(previousStep, "yourNewColumnName", each if
  List.AnyTrue(
    List.Transform(
      ServiceCodes[ServiceCode],
      (x) => Text.Contains([CallDescription], x)
    )
  ) then "New Code Exists" else CONTINUE YOUR OTHER EVALUATIONS

Pete

[jtomczyk]

Hi Pete,

Many thanks for your prompt response.
Unfortunetly I got errors everywhere without indicating what is the root cause.

= Table.AddColumn(#"Sorted Rows", "CallDescription", each if
  List.AnyTrue(
    List.Transform(
      ServiceCodes[ServiceCode],
      (x) => Text.Contains([CallDescription], x)
    )
  ) then "New Code Exists" else
(if Text.Contains([Call Description], "color change", 		Comparer.OrdinalIgnoreCase)= true then "HR01" else 
.........

regards

JT

[BA_Pete]

@jtomczyk ,

The first thing to do is to ensure you are adding this new column into your ACCServiceCall table where the original [CallDescription] field is. The fact that Power Query allowed you to add a new column called [CallDescription] tells me that you're trying to add this new column into a table that doesn't already include such a field. If so, my code isn't going to work.

The next thing to do is to call your new column something else. In your example, you've called it [CallDescription]. The code I provided actually references a column called [CallDescription] as I thought that was where the text was that you wanted to match against your ServiceCodes table. This is creating a circular reference.

If you still get an error after the above changes, then select one of the cells that has 'Error' in it. Don't click the actual word, just select the cell and the error text will appear at the bottom of your preview window. See if this tells you what the issue is. If you're not sure, post a screenshot of the error message here and I'll have a look for you.

My code definitely works when implemented as designed. Here's my test code that you can use to check against your setup if it helps:

//Call this query 'countryTable'. This is the equivalent of your ServiceCodes query
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCiktyk6tVIrViVYKzcssSU1RCC5JLEktVoqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [country = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"country", type text}})
in
    #"Changed Type"

//This query is the equivalent of your ACCServiceCall query with the original [CallDescription] field in it
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKijKTy9KzFVIVAgpLcpOrVSK1UEIJmETTFYIzcssSU1R8M7MS0/Jz0WRTFEISCwoTVTwSy1XcC/NzEtNRJFOhekNLkksSS1GkUtT8CxJzEG1Kx2P+gwsVsUCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [program = _t]),
    addFindWord = Table.AddColumn(Source, "findWord", each if
List.AnyTrue(
    List.Transform(
        countryTable[country],
        (x) => Text.Contains([program], x)
    )
) then "TEXT" else if
Text.Contains([program], "United") then "Contains United" else null)
in
    addFindWord

Pete

[jtomczyk]

Hi Pete,

you were correct.! Small modification of the code helped. The existing colum was "Call Description" 

= Table.AddColumn(#"Sorted Rows", "CallDescription", each if
  List.AnyTrue(
    List.Transform(
      ServiceCodes[ServiceCode],
      (x) => Text.Contains([Call Description], x)
    )
  ) then "New Code Exists" else
(if Text.Contains([Call Description], "color change", 		Comparer.OrdinalIgnoreCase)= true then "HR01" else 
.........

There are two other problems.

1. I noticed that the code is case-sensitive.
for example in the database it is written "hr01" and in another query "ServiceCodes", column "ServiceCode" we have "HR01". As a result the code do not recognize it as "New Code Exist"

What should I add to above code so it is not case-sensitive?
2. What should be the code if we want to have the code from the table instead of "New Code Exist"?

[BA_Pete · Accepted solution]

@jtomczyk ,

1) Yes, M code is entirely case-sensitive. This is a question of error-handling. It depends on where the upper/lower case versions occur, and whether they are ALWAYS upper/lower case in each source. One option might be to select your fields where the value might be a different case and transform the text to all upper case via Transform Tab > Format > UPPERCASE/lowercase. You could also write this into your code, but I think you'd take a hit on performance as you'd need to essentially evaluate at least twice, once for upper case, once for lower.

2) This gets a bit more complicated, but not too much more. In the interest of getting you a quick answer I've put together the following code. However, it's worth noting that this calls the evaluation custom function twice, so may not be the most performant solution possible:

= Table.AddColumn(#"Sorted Rows", "CallDescription", each if
List.AnyTrue(
    List.Transform(
        ServiceCodes[ServiceCode],
        (x) => Text.Contains([Call Description], x)
    )
)
then
try ServiceCodes{
	List.PositionOf(
		List.Transform(
			ServiceCodes[ServiceCode],
			(x) => Text.Contains([Call Description], x)
		),
		true
	)
} [ServiceCode] otherwise null
else if
Text.Contains([Call Description], "color change", Comparer.OrdinalIgnoreCase)= true then "HR01" else 
.........

For completeness, here's updated test code for my second query previously provided:

//Test code to replace previous provided, equivalent to your ACCServiceCall query
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKijKTy9KzFVIVAgpLcpOrVSK1UEIJmETTFYIzcssSU1R8M7MS0/Jz0WRTFEISCwoTVTwSy1XcC/NzEtNRJFOhekNLkksSS1GkUtT8CxJzEG1Kx2P+gwsVsUCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [program = _t]),
    addFindWord = Table.AddColumn(Source, "findWord", each if
List.AnyTrue(
    List.Transform(
        countryTable[country],
        (x) => Text.Contains([program], x)
    )
)
then
try countryTable{
    List.PositionOf(
        List.Transform(
            countryTable[country],
            (x) => Text.Contains([program], x)
        ),
        true
    )
} [country] otherwise null
else if
Text.Contains([program], "United") then "Contains United" else null)
in
    addFindWord

Pete