Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Rename multiple column names along with changing their column numbers to Letters

I have a table imported into Power Query editor. The columns have the following names:   ID | Agg | Cluster1 | Cluster2 | Cluster3... ------------------------------------------ 1234 | 0.232 | abd...
Need Help
Show and Tell
Tips and Tricks
  • ImkeF's avatar
    ImkeF
    6 years ago

    Hi Anonymous ,

    your formula won't work in a "Table.TransformColumnNames"-formula. You have to use Table.RenameColumns instead like so:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTICYmMgNlGKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Agg = _t, Cluster1 = _t, Cluster2 = _t]),
    Custom1 = 
        Table.RenameColumns(
            Source, 
            List.Zip(
                {
                    Table.ColumnNames(Source), 
                    List.Transform(
                        List.ReplaceValue(
                            List.FindText(
                                Table.ColumnNames(Source),
                                ""),
                            "Cluster",
                            "TC",
                            Replacer.ReplaceText), 
                        each if Text.Contains(_,"TC") 
                                then Text.Replace("_", "_","TC " & Character.FromNumber(Number.FromText(Text.Middle(_,2))+64)) 
                                else _ )
                } ))
in
    Custom1

     

Nathaniel_C's avatar
Nathaniel_C
Community Champion
6 years ago

Hi Anonymous ,

Check out this blog from MattAllington at Renaming all the columns at once. He is also having a free webinar next week. Check it out!

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Nathaniel_C ,

     

    It doesn't work for me. I need to also convert the column numbers to their corresponding Letters.

    • Nathaniel_C's avatar
      Nathaniel_C
      Community Champion
      6 years ago

      Hi Anonymous ,

       

      Maybe ImkeF  can help. She is major league for M.

       

      Nathaniel

      • ImkeF's avatar
        ImkeF
        Community Champion
        6 years ago

        Hi Anonymous ,

        your formula won't work in a "Table.TransformColumnNames"-formula. You have to use Table.RenameColumns instead like so:

         

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTICYmMgNlGKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Agg = _t, Cluster1 = _t, Cluster2 = _t]),
    Custom1 = 
        Table.RenameColumns(
            Source, 
            List.Zip(
                {
                    Table.ColumnNames(Source), 
                    List.Transform(
                        List.ReplaceValue(
                            List.FindText(
                                Table.ColumnNames(Source),
                                ""),
                            "Cluster",
                            "TC",
                            Replacer.ReplaceText), 
                        each if Text.Contains(_,"TC") 
                                then Text.Replace("_", "_","TC " & Character.FromNumber(Number.FromText(Text.Middle(_,2))+64)) 
                                else _ )
                } ))
in
    Custom1