Forum Discussion
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"}}),
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!
2 Replies
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!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),
