Forum Discussion
M- text contains
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
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.
5 Replies
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.
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 IsQuestionYou'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.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 IsQuestionYou'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.
