Search, Isolate, and Transform Single Words in Phrase

[Adamc]

Is it possible to isolate and transform a single word or phrase in excel power query instead of transforming the text in the entire cell?

For example, I have a column of text with 300 words per row. I want to search within the text for a word or phrase from a list of keyword that comes from a different table. Every occurrence where a keyword is found in the text I want to capitalize that keyword. 

I can almost accomplish what I am explaining here but right now I am only able to capitalize the entire row of text when a keyword is found. How can I isolate a single word without changing the rest of the text in the row?

• Example Kayword to Search For: "summer"
• Example Text Passage: "It's a wonderful summer day today. Last summer didn't have this nice of weather."
  Example Output: "It's a wonderful SUMMER day today. Last SUMMER didn't have this nice of weather."

[ppm1]

You can split the text at the spaces with Text.Split to make a list and then apply a transform for just the "summer" elements and then recombine with spaces.

= Text.Combine(List.Transform(Text.Split([TextColumn], " "), each if _ = "summer" then Text.Upper(_) else _), " ")

Pat

[Adamc]

@ppm1 

Is it possible to use "replace" to replace a keyword or phrase from my list with the capitalized version?

[Adamc]

@ppm1 Thanks for the response.

Some of the keywords and phrases I'm searching for have spaces in them so imagine the keyword in the above example was "last summer" so I want to output "LAST SUMMER". How can I account for this if I don't want to split the text by each space?

[ppm1]

In that case, see scenario 5 in this article - Replace Values in Power Query M (Ultimate Guide) - BI Gorilla

Pat