none
Prepend string to each column name in a table RRS feed

  • Question

  • I can't seem to get my head around how to do this...

    I'm building a function to create a specific PowerPivot date table from a generic one.  I want to prepend each colum name with a string, like "ContractStart "

    I can get a list containing a tables ColumnNames with Table.RenameColumns, but how would I feed each list element to Table.RenameColumns to prepend each with that static string?

    Thanks,

    Dale

    Friday, August 7, 2015 9:56 PM

Answers

  • An alternative which may or may not be more straightforward is indeed to use Table.RenameColumns:

    let
        Source = Table.FromRecords({[Date="1/1/2000", Year=2000, Month = 1, Day = 1], [Date="1/2/2000", Year=2000, Month = 1, Day = 2]}),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Year", Int64.Type}, {"Month", Int64.Type}, {"Day", Int64.Type}}),
        Renames = List.Transform(Table.ColumnNames(#"Changed Type"), each {_, "ContractStart " & _}),
        NewHeaders = Table.RenameColumns(#"Changed Type", Renames)
    in
        NewHeaders

    • Marked as answer by Dale Hohm Monday, August 10, 2015 12:23 PM
    Saturday, August 8, 2015 2:32 AM

All replies

  • Hi Dale,

    One of many ways is

    NewHeaders= Table.FromColumns(Table.ToColumns(#"Previous Step"), List.Transform(Table.ColumnNames(#"Previous Step"), each "ContractStart "&_))

    Regards

    Friday, August 7, 2015 10:12 PM
  • Thanks, this works, but I still don't have my head around it.

    Here it is in a self-contained example using your suggestion:

    let
        Source = Table.FromRecords({[Date="1/1/2000", Year=2000, Month = 1, Day = 1], [Date="1/2/2000", Year=2000, Month = 1, Day = 2]}),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Year", Int64.Type}, {"Month", Int64.Type}, {"Day", Int64.Type}}),
        NewHeaders = Table.FromColumns(Table.ToColumns(#"Changed Type"), List.Transform(Table.ColumnNames(#"Changed Type"), each "ContractStart "&_))
    in
        NewHeaders

    Can you explain what Table.FromColumns/Table.ToColumns do in this context?  Are they actually splitting the full table into a list for each column, then rebuilding the table with new column headers?  If so, is this efficient (with a very large table)?  I expected the solution to include Table.RenameColumns...

    Thanks again,

    Dale

    Saturday, August 8, 2015 2:24 AM
  • An alternative which may or may not be more straightforward is indeed to use Table.RenameColumns:

    let
        Source = Table.FromRecords({[Date="1/1/2000", Year=2000, Month = 1, Day = 1], [Date="1/2/2000", Year=2000, Month = 1, Day = 2]}),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Year", Int64.Type}, {"Month", Int64.Type}, {"Day", Int64.Type}}),
        Renames = List.Transform(Table.ColumnNames(#"Changed Type"), each {_, "ContractStart " & _}),
        NewHeaders = Table.RenameColumns(#"Changed Type", Renames)
    in
        NewHeaders

    • Marked as answer by Dale Hohm Monday, August 10, 2015 12:23 PM
    Saturday, August 8, 2015 2:32 AM
  • Thanks Curt - this is what I was trying to do.  

    It did not occur to me to use List.Transform and then provide the result to Table.RenameColumns.  Both the syntax for creating a nested list and the use of more than one _ replacement character were Ah-Ha moments for me.

    Dale

    Monday, August 10, 2015 12:30 PM