Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

Capitalize only first word

Hello,

1) In power query (Excel) I am trying to capitalize ONLY the first word for each row in a given column. 

I do not want to capitalize every word in the string using Text.Proper . In the power query editor under transform > format a 'sentence case' option doesnt appear (as it does in Word for example). 

 

So for example I would like to turn:

'some text' -> 'Some text'

(and not 'Some Text' or 'some text' or 'SOME TEXT')

 

I presume I might be able to use Text.Start to get the first letter only, but my syntax is all wrong.

 

2) Next, I would like to transform:

'some text, more text' -> 'Some text, More text'

I presume this can be done in 2 steps (first using step 1 above if solved), and then by using that delimiter ', ' somehow. 

 

Help with the syntax is much appreciated!!

Best, Rogerbij

1 ACCEPTED SOLUTION
Mariusz
Community Champion
Community Champion

Hi @Anonymous 

 

Please see the M expression for the first part below, just split the column on the first left delimiter and then use Text.Proper next merge this columns back together.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKi5JLCpRKEmtKFGK1YlWSs1LgXJiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [label = _t]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "label", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"label.1", "label.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"label.1", type text}, {"label.2", type text}}),
    #"Capitalized Each Word" = Table.TransformColumns(#"Changed Type1",{{"label.1", Text.Proper, type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Capitalized Each Word",{"label.1", "label.2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"label")
in
    #"Merged Columns"

For the second bit, please can you provide bigger data sample?

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
Mariusz Repczynski


 

View solution in original post

7 REPLIES 7
TimoRiikonen
Advocate III
Advocate III

Here is the critical line to regular solution where you change only the first letter:

= Table.TransformColumns(#"Previous step",{{"Column name", each Text.Upper(Text.At(_,0)) & Text.Range(_, 1, Text.Length(_) - 1), type text}})

 

loissyylane
New Member

Hi, I know this is a very late reply. I was also looking for the right formula for this and I found this one:

=UPPER(LEFT(A1,FIND(" ",A1)-1))&PROPER(MID(A1,FIND(" ",A1),LEN(A1)))

 

Pooja_Powerbi
New Member

1. If you want all the words first letter should capitalize then= Transform data>Goto Power query> Right click>Click on Transform>Will get these Screenshot options you can capitalize each word.

2. If you want to capitalize only one intial word in the full sentence then split the column by number of Character> once you seperate the column then capitalizethe 1st column then merge both the column.

 

I hope have answered your question

 

 

1.

Pooja_Powerbi_0-1678596644269.png

2.

Pooja_Powerbi_1-1678597150491.png

 

 

Anonymous
Not applicable

Hi Mariusz, thanks for the fast reply. I confirm it works to split the column on the first space delimter, use Text.Proper on that first column only, and then merge the two columns back together again. I was a little surprised this cant be done in one line, like when formatting to Text.Proper. However, its simple enough, which I like.  In reality I have a column that is populated with values separated by a comma from a 'select multiple' question in a survey. Some rows have one value, some two, some three. Eg. 

MyFruit

--------

apples are red, bananas are yellow

oranges are orange, grapes are purple, mangoes are orange

blueberries are blue

 

I would like:

Apples are red, Bananas are yellow

Oranges are orange, Grapes are purple, Mangoes are orange

Blueberries are blue

 

Thanks everyone for the input. Roger

Hi @Anonymous 

 

You can copy the the below and paste it into Advance Editor of a Blank Query, from there you will be able to investigate the steps yourself.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VYxBCoAwDAS/EnLORX1O6SHFUITahkgRf2/UXiSXze4wIeCEhKxa5AA2AZOVIHH1+4pLSmknRgo4O9qMax7slwmysY5Ku7mKYPel/bDXsLghlS5JzLYxPz/GeAM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [id = _t, text = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"text", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"text", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "text"),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "text", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"text.1", "text.2"}),
    #"Capitalized Each Word" = Table.TransformColumns(#"Split Column by Delimiter1",{{"text.1", Text.Proper, type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Capitalized Each Word",{"text.1", "text.2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"text"),
    #"Grouped Rows" = Table.Group(#"Merged Columns", {"id"}, {{"text", each _[text], type list}}),
    #"Extracted Values" = Table.TransformColumns(#"Grouped Rows", {"text", each Text.Combine(List.Transform(_, Text.From), ", "), type text})
in
    #"Extracted Values"

 

Let me know if you need anything else.

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
Mariusz Repczynski

 

Anonymous
Not applicable

Hi @Mariusz 

I could follow the first example, but not the second example. Could you please explain to me the logical steps in English in the second example so I understand the logic? 

Thanks, Roger

Mariusz
Community Champion
Community Champion

Hi @Anonymous 

 

Please see the M expression for the first part below, just split the column on the first left delimiter and then use Text.Proper next merge this columns back together.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKi5JLCpRKEmtKFGK1YlWSs1LgXJiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [label = _t]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "label", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"label.1", "label.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"label.1", type text}, {"label.2", type text}}),
    #"Capitalized Each Word" = Table.TransformColumns(#"Changed Type1",{{"label.1", Text.Proper, type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Capitalized Each Word",{"label.1", "label.2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"label")
in
    #"Merged Columns"

For the second bit, please can you provide bigger data sample?

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
Mariusz Repczynski


 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors