PowerBI table: how to add number to column name

Question

I have a table with the following column names:

A	B	C	D	E	F	G

I need to rename my columns so that from a certain column onwards they are numbered sequentially:

A	B	C	D (1)	E (2)	F (3)	G (4)

I know how to do it manually, but since I have 65 of such columns I was hoping to use something like TransformColumnNames to do it programmatically.

Many thanks!

Answer 1

Here's one way: It starts with a table named Table 1 as the source.

let
    Source = Table1,
    //Replace the "D" below with the name of your column that you want to start numbering at
    #"Get Column Number to Start Adding Numbers At" = List.PositionOf(Table.ColumnNames(Source),"D"),
    #"Setup Column Numbers" = List.Transform({1..List.Count(Table.ColumnNames(Source))}, each if _-#"Get Column Number to Start Adding Numbers At" > 0 then " (" & Text.From(_-#"Get Column Number to Start Adding Numbers At") & ")" else ""),
    #"Create New Column Names" = List.Zip({Table.ColumnNames(Source), #"Setup Column Numbers"}),
    #"Converted to Table" = Table.FromList(#"Create New Column Names", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Extracted Values" = Table.TransformColumns(#"Converted to Table", {"Column1", each Text.Combine(List.Transform(_, Text.From)), type text}),
    Result = Table.RenameColumns(Source, List.Zip({Table.ColumnNames(Source),#"Extracted Values"[Column1]}))
in
    Result