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
TeeGee
Helper II
Helper II

Simpler way to write M code to rename multiple columns by position? (user defined global function?)

I am importing multiple spreadsheets with different languages, so am having to hardcode my columns names by ordinal position in the M code.

I find the syntax below hard to read and even harder to write....is there a better way of doing this?  I've seen a few functions that have been written that deal with column renaming, but I haven't found an understandable article on how one actually writes or incorporates an existing function into a PQ project.  Hopefully there's a way to define a function globally so it can be called from all queries?

 

 

 

 

 

rename00 = #"Removed Columns1",
rename01 = Table.RenameColumns(rename00,{{Table.ColumnNames(rename00){1}, "CBF ID"}}),
rename02 = Table.RenameColumns(rename01,{{Table.ColumnNames(rename01){2}, "CBF Description"}}),
rename03 = Table.RenameColumns(rename02,{{Table.ColumnNames(rename02){3}, "RTO Recovery TIME Objective"}}),
rename04 = Table.RenameColumns(rename03,{{Table.ColumnNames(rename03){4}, "RPO Recovery POINT Objective"}}),
rename05 = Table.RenameColumns(rename04,{{Table.ColumnNames(rename04){5}, "Overall Business Impact"}}),
rename06 = Table.RenameColumns(rename05,{{Table.ColumnNames(rename05){6}, "Disruption Frequency"}}),
rename07 = Table.RenameColumns(rename06,{{Table.ColumnNames(rename06){7}, "# of FTEs"}}),
rename08 = Table.RenameColumns(rename07,{{Table.ColumnNames(rename07){8}, "Revenue Impact"}}),
rename09 = Table.RenameColumns(rename08,{{Table.ColumnNames(rename08){9}, "Cost Impact"}}),

1 ACCEPTED SOLUTION
SteveCampbell
Memorable Member
Memorable Member

You could do it all in one step, for example:

 Table.RenameColumns(rename00,{  {Table.ColumnNames(rename00){1}, "CBF ID"}, {Table.ColumnNames(rename00){2}, "CBF Description"},
{Table.ColumnNames(rename00){3}, "RTO Recovery TIME Objective"}.... }),

Notice each rename is wrapped in {} and seperated by commas, but then the total step is wrapped in the red {}

 

 

Appreciate your Kudos
I love to share - connect with me!

Stay up to date on  linkedin-logo.png
Read my blogs on  powerbi.tips_.png



Did I answer your question? Mark my post as a solution! Proud to be a Super User!


Connect with me!
Stay up to date on  
Read my blogs on  



View solution in original post

2 REPLIES 2
SteveCampbell
Memorable Member
Memorable Member

You could do it all in one step, for example:

 Table.RenameColumns(rename00,{  {Table.ColumnNames(rename00){1}, "CBF ID"}, {Table.ColumnNames(rename00){2}, "CBF Description"},
{Table.ColumnNames(rename00){3}, "RTO Recovery TIME Objective"}.... }),

Notice each rename is wrapped in {} and seperated by commas, but then the total step is wrapped in the red {}

 

 

Appreciate your Kudos
I love to share - connect with me!

Stay up to date on  linkedin-logo.png
Read my blogs on  powerbi.tips_.png



Did I answer your question? Mark my post as a solution! Proud to be a Super User!


Connect with me!
Stay up to date on  
Read my blogs on  



 

Wonderful, that syntax is much nicer, and you can even write it like the following and it maintains the structure even after you save.  Way easier to read and write!!

 

	    t = removeHeaderRows,
        renamedColumns = Table.RenameColumns(t,{  
            {Table.ColumnNames(t){1}, "Dept Request Num"}, 
            {Table.ColumnNames(t){2}, "Need"}, 
            {Table.ColumnNames(t){3}, "Business Reason"},
            {Table.ColumnNames(t){4}, "Requester Name"},
            {Table.ColumnNames(t){5}, "Desired Time Frame (months)"},
            {Table.ColumnNames(t){6}, "Impact if not completed"},
            {Table.ColumnNames(t){7}, "Comments"} 
            }),
        #"Removed Top Rows" = Table.Skip(renamedColumns,1),

 

 

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.