CODEWITHSUNDEEP
powerbim
Gep
Gep

Reputation: 928

PowerBI table: how to add number to column name

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!

Upvotes: 0

Views: 253

Answers (2)

Marc Pincince
Marc Pincince

Reputation: 5202

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

Upvotes: 1

xris23
xris23

Reputation: 353

Maybe if you pivot the columns that need to have the number, then add an index and create a new concatenated column with number included. remove the other columns and unpivot again?

Upvotes: 0

Related Questions