Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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...
General Comment
  • Mariusz's avatar
    Mariusz
    7 years ago

    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


     

Mariusz's avatar
Mariusz
Community Champion
7 years ago

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's avatar
Anonymous
Not applicable
7 years ago

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