cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
sifar786 Member
Member

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?

1 ACCEPTED SOLUTION

Accepted Solutions
ImkeF
MVP

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

Hi @sifar786 ,

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

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

5 REPLIES 5
Nathaniel_C Super Contributor
Super Contributor

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

Hi @sifar786 ,

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

sifar786 Member
Member

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

Hi @Nathaniel_C ,

 

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

Nathaniel_C Super Contributor
Super Contributor

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

Hi @sifar786 ,

 

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

 

Nathaniel

ImkeF
MVP

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

Hi @sifar786 ,

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

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

sifar786 Member
Member

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

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

 

Thanks @ImkeF 🙂

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,214)