topic Re: Text Transformations in Power Query in Power Query

Text Transformations in Power Query

suebayes — Thu, 10 Jun 2021 11:59:39 GMT

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.

Re: Text Transformations in Power Query

Greg_Deckler — Thu, 10 Jun 2021 12:08:24 GMT

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

Re: Text Transformations in Power Query

mahoneypat — Thu, 10 Jun 2021 12:14:01 GMT

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

Re: Text Transformations in Power Query

suebayes — Thu, 10 Jun 2021 12:47:52 GMT

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

Re: Text Transformations in Power Query

suebayes — Thu, 10 Jun 2021 12:49:05 GMT

Thanks Greg_Deckler for replying, much appreciated.