Forum Discussion

jtomczyk's avatar
jtomczyk
Helper I
4 years ago
Solved

Text.Contains form another query/table

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 implemente...
Need Help
  • BA_Pete's avatar
    BA_Pete
    4 years ago

    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

  • BA_Pete's avatar
    BA_Pete
    4 years ago

    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

BA_Pete's avatar
BA_Pete
Super User
4 years ago

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's avatar
jtomczyk
Helper I
4 years ago

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's avatar
    BA_Pete
    Super User
    4 years ago

    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