Forum Discussion

TeeGee's avatar
TeeGee
Helper II
6 years ago
Solved

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 wr...
Need Help
  • SteveCampbell's avatar
    SteveCampbell
    6 years ago

    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  
    Read my blogs on  

SteveCampbell's avatar
SteveCampbell
Memorable Member
6 years ago

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  
Read my blogs on  

  • TeeGee's avatar
    TeeGee
    Helper II
    6 years ago

     

    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),