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 | 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?

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

Forum Discussion

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

5 Replies

Related content

MULTIPLE CARD

Changing number format in a table

Change Whole Number to a Date

Multiple dataViewMappings

Change numbers to display as thousands or milions