Forum Discussion

Mlemmers's avatar
Mlemmers
Icon for Helper I rankHelper I
2 years ago
Solved

Proper Case function with exception of prepositions in M

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:

countryidcurrency_in_countryships_to_country
   


Expected outcome headers:

Country IDCurrency in CountryShips to Country
   
Need Help
  • slorin's avatar
    slorin
    2 years ago

    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 

6 Replies

  • slorin's avatar
    slorin
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      Mlemmers
      Icon for Helper I rankHelper I
      2 years ago

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

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Icon for Administrator rankAdministrator
    2 years ago

    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-Forum/ba-p/963216
    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/1447523

    • Mlemmers's avatar
      Mlemmers
      Icon for Helper I rankHelper I
      2 years ago

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

        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.