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

[Anonymous]

I have a table imported into Power Query editor. The columns have the following names:

ID | Agg | Cluster1 | Cluster2 | Cluster3...

------------------------------------------

1234 | 0.232 | abdef | wdkdf | dksdmsd

1092 | 0.436 | hghg  | kjfgfkf | opwkffkd

1342 | 1.299 | wlslvs | isivmw | wyywcga

 I am trying to rename the "Cluster" columns with "TC" but changing their Column number to it's corresponding Letter. e.g. :

ID | Agg | TC A | TC B | TC C ...

---------------------------------

= Table.TransformColumnNames(Table1, each if Text.Contains(_,"Cluster") then Text.Replace(_,"Cluster","TC " & Character.FromNumber(Number.FromText(Text.Middle(Text.Replace(_,"Cluster","TC "), 2))+64)) else _ )

I was able to write the above Query, however, i am getting the below output:

ID | Agg | TC A1 | TC B2 | TC C3 ...

------------------------------------------

1234 | 0.232 | abdef | wdkdf | dksdmsd

1092 | 0.436 | hghg  | kjfgfkf | opwkffkd

1342 | 1.299 | wlslvs | isivmw | wyywcga

I was able to rename correctly using List, but don't know how to plug this List as Header row in my original table:

= List.Transform(List.ReplaceValue(List.FindText(Table.ColumnNames(Table1),""),"Cluster","TC",Replacer.ReplaceText), each if Text.Contains(_,"TC") then Text.Replace("_", "_","TC " & Character.FromNumber(Number.FromText(Text.Middle(_,2))+64)) else _ )

ID | Agg | TC A1 | TC B2 | TC C3 ...

Any idea why i am getting this output and how do i rename them correctly?

[Nathaniel_C]

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]

Hi @Nathaniel_C ,

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

[Nathaniel_C]

Hi @Anonymous ,

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

Nathaniel

[ImkeF · Accepted solution]

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]

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

Thanks @ImkeF 🙂