Text.Contains form another query/table

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 ,

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