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

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Ah! I was so near! Didn't know about the List.Zip function. Works like a charm!

         

        Thanks ImkeF :-)