Proper Case function with exception of prepositions in M

[Mlemmers]

Hello smart people, 

I'd like to have a function that turns my column headers into proper case with the exception of prepositions.

So far I tried this but it still capitalises prepositions. At this point I don't know what I'm doing anymore 😅 Help?!

let
    Source = (Source) =>
    let
        exceptions = {"in", "of", "for", "without"},
        to_list = Text.Split(Source, " "),
        containsException = (word) => List.Contains(exceptions, Text.Lower(word)),
        hasExceptions = List.AnyTrue(List.Transform(to_list, each containsException(_))),
        text_proper = 
            if hasExceptions then
                Text.Trim(Text.Replace(Source, "_", " "))
            else
                Text.Proper(Text.Trim(Text.Replace(Source, "_", " "))),
        id_space = if Text.EndsWith(text_proper, "id") then Text.Trim(Text.Replace(text_proper, "id", " ID")) else text_proper 
    in
        id_space
in
    Source

Edit:

Example of column headers:

countryid	currency_in_country	ships_to_country

Expected outcome headers:

Country ID	Currency in Country	Ships to Country

[slorin · Accepted solution]

Hi,

Step1 = Table.TransformColumnNames(
      Source, 
      each Text.Combine(
         List.ReplaceMatchingItems(
           List.Transform(Text.Split(_,"_"), Text.Proper),
           {{"In","in"}, {"To", "to"}, {"For", "for"}}),
         " ")),
Step2 = Table.TransformColumnNames(
     Step1,
     each if Text.EndsWith(_,"id") 
          then Text.BeforeDelimiter(_,"id",RelativePosition.FromEnd)&" ID" 
          else _)

Stéphane 

[Mlemmers]

I get a We cannot convert the value to type Table error

[Syndicate_Admin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Mlemmers]

I added an example 🙂  It's nothing more than making sure that the "for", "in", "to", "within" etc. in column headers are not in proper case where the rest is. ships_to_country to Ships to Country

[Anonymous]

Hi @Mlemmers 

You can put the following code to advanced editor in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTICYmOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [countryid = _t, currency_in_country = _t, ships_to_country = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"countryid", type text}, {"currency_in_country", type text}, {"ships_to_country", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Attribute.1", "Attribute.2", "Attribute.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}, {"Attribute.3", type text}}),
    #"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Changed Type1", {"Value"}, "Attribute", "Value.1"),
    #"Replaced Value1" = Table.ReplaceValue(#"Unpivoted Columns1",each [Value.1],each if List.Contains({"in","on","to","of","at","for","about"},[Value.1]) then [Value.1] else Text.Proper([Value.1]),Replacer.ReplaceValue,{"Value.1"}),
    #"Replaced Value" = Table.ReplaceValue(#"Replaced Value1","id","ID",Replacer.ReplaceText,{"Value.1"}),
    #"Pivoted Column" = Table.Pivot(#"Replaced Value", List.Distinct(#"Replaced Value"[Attribute]), "Attribute", "Value.1"),
    #"Merged Columns" = Table.CombineColumns(#"Pivoted Column",{"Attribute.1", "Attribute.2", "Attribute.3"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Pivoted Column1" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "Value")
in
    #"Pivoted Column1"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Mlemmers]

I get a We cannot convert the value to type Table error