Forum Discussion
M- text contains
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
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.