cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sifar786
Post Patron
Post Patron

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

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

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

5 REPLIES 5
Nathaniel_C
Super User
Super User

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





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

Proud to be a Super User!




Hi @Nathaniel_C ,

 

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

Hi @sifar786 ,

 

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

 

Nathaniel





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

Proud to be a Super User!




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

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

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

 

Thanks @ImkeF 🙂

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors