M- text contains

[bhard]

Hi all,

I am importing a report that consists of multiple checklists. In each checklist there are four questions that are the same in every checklist. They are all questions related to someone’s skills. I have been doing a keyword search and using the findings to create a new column.

Example:

QuestionText	Response Code
Does the site have the skills?	Yes
What skills are required?	X,Y,Z
Is there a skills gap?	No
What is the skills gap remediation action?	Null

I have been using the following power query to identify these questions and differentiate from the rest of the checklist.

= Table.AddColumn(#"Renamed Columns", "Skill", each if Text.Contains([QuestionText], "skills") then 1 else 0)

I then use this column to either exclude these questions from the rest of the calculations for the checklist. For example

= Table.SelectRows(#"Filtered Rows", each [Skill] = 0)

Or include the four questions do some calculations.

= Table.SelectRows(#"Filtered Rows", each [Skill] = 1)

Easy enough and all of that is working fine. But I have run into an issue.

People who write the checklist have created other questions with the keyword ‘skills’ in them. The keyword search no longer works as intended.

Now, I need to modify the new column to search for the entre question, not just a keyword. If the [QuestionText] field contains any of the four questions, mark true, if not mark false.

But, there are four questions. Can I use an OR function and include all four questions as the search criteria? What is the best way to structure this using multiple criteria for the TextContains function?

Thanks

[Syndicate_Admin]

You could try this. Just put your questions in a list, then use List.Contains:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY07DgIxDAWvYrnOHWhooKCFJUrhJRaxCBvIBym3JwmLoLLsN2+sNW4DJ8iOIUlmcPTiz3YT79MGFU6c0CiNR0d5PQNFhsjPIpFtZ05qUudB7YasxfRlr/ToyCH8LJL+fnSgye5shbKEBejSx+gU79cWL71VQ4kwS8zOUm3AvviKxrwB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [QuestionText = _t, #"Response Code" = _t]),
    IsQuestion = 
        Table.AddColumn(
            Source,
            "Is Response Question",
            each if List.Contains(
                    {
                        "Does the site have the skills?",
                        "What skills are required?",
                        "Is there a skills gap?",
                        "What is the skills gap remediation action?"
                    }, [QuestionText]
                ) then true else false
        )
in
    IsQuestion

You'll see it returns this:

Just replace true/false with your desired output.

You can add as many things to that list (the thing between the squigly brackets and it will evaulate every question against that list.

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

[edhans]

You could try this. Just put your questions in a list, then use List.Contains:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY07DgIxDAWvYrnOHWhooKCFJUrhJRaxCBvIBym3JwmLoLLsN2+sNW4DJ8iOIUlmcPTiz3YT79MGFU6c0CiNR0d5PQNFhsjPIpFtZ05qUudB7YasxfRlr/ToyCH8LJL+fnSgye5shbKEBejSx+gU79cWL71VQ4kwS8zOUm3AvviKxrwB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [QuestionText = _t, #"Response Code" = _t]),
    IsQuestion = 
        Table.AddColumn(
            Source,
            "Is Response Question",
            each if List.Contains(
                    {
                        "Does the site have the skills?",
                        "What skills are required?",
                        "Is there a skills gap?",
                        "What is the skills gap remediation action?"
                    }, [QuestionText]
                ) then true else false
        )
in
    IsQuestion

You'll see it returns this:

Just replace true/false with your desired output.

You can add as many things to that list (the thing between the squigly brackets and it will evaulate every question against that list.

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

[Greg_Deckler · Accepted solution]

@bhard Try:

if Text.Contains([QuestionText], "Does the site have the skills?") or Text.Contains([QuestionText], "What skills are required?") or Text.Contains([QuestionText], "Is there a skills gap?") or Text.Contains([QuestionText], "What is the skills gap remediation action?") then 1 else 0)

Note, you could also drop the Text.Contains at this point and just use [QuestionText] = "What is the skills gap remediation action?" for example. 

[bhard]

I kept Text.Contains just to make the change quickly. The or function worked great. Thanks. 

[edhans]

That's great @bhard - Text.Contains is a bit easier to understand from the start. Glad you have a solution that works.