Text Transformations in Power Query

[suebayes]

I want to be able to capitalise only the first word in any sentence within a column.  Note there can be many sentences within one column.

What data looks like

Name       Answer

Sue	This is great.  Fantastic news.
Jon	THIS IS GREAT.  I really like this.  Silly capitals.
Joe	Not just one sentence.  THERE COULD BE LOTS.

What it should look like

Name    Answer

Sue	This is great.  Fantastic news.
Jon	This is great.  I really like this.  Silly capitals.
Joe	Not just one sentence.  There could be lots.

[mahoneypat · Accepted solution]

Here's one way to do it in the query editor.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PY3LCsIwFER/Zci69B98RFspLTRxFboI4aLRkIi5Rfx7bzfCrIZzZpxTZiXVKHuPFZLbmzy3wMln9pVjQKZPbdXSOHUpeSO73kBynvXOCtlDlJS+SPFJYNmR0sStCf4V2ae/vh2NhfFYK6NkQqXMlAOJYTs9axym63DEXmOYrBFt+QE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Answer = _t]),
    #"Lowercased Text" = Table.TransformColumns(Source,{{"Answer", Text.Lower, type text}}),
    #"Added Custom" = Table.AddColumn(#"Lowercased Text", "Custom", each let x = Text.Split([Answer], ". ")
in List.Transform(x, each Text.Upper(Text.Start(Text.Trim(_),1)) & Text.End(Text.Trim(_), Text.Length(Text.Trim(_))-1)))
in
    #"Added Custom"

Pat

[suebayes]

Thanks Pat, I sort of thought that might be the way to go but couldn't wrap my head around the syntax required to make it do it.  That's very elegant thank you @mahoneypat 

[Greg_Deckler]

@suebayes Not aware of anything for this. There is Text.Proper and Text.Upper but they do not do what you are asking. You could try adding column from examples and see if it can figure it out. @edhans @ImkeF 

[suebayes]

Thanks @Greg_Deckler for replying, much appreciated.