Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

 

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





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

Proud to be a Super User!




Anonymous
Not applicable

Hi @Nathaniel_C ,

 

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

Hi @Anonymous ,

 

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

 

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

Anonymous
Not applicable

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

 

Thanks @ImkeF 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors