none
Rename Column 1 RRS feed

  • Question

  • Hi,

    I import data into Power Query

    First 2 records looks like this:

    • 01 Nov 2015 | Name | Age | Value
    • 01 Nov 2015 | Andy | 23 | 123.45

    I promote first row to headers

    I rename column 1 header to "Date"

    My Problem:

    When i run the query for another data set the next day, the Date is 02 Nov 2015 which causes my rename set to fail as it is looking for 01 Nov 2015.

    How do I rename column 1 by basically referring to the column by INDEX instead of NAME?

    Hope this is clear.

    TX

    Wednesday, November 11, 2015 10:10 AM

Answers

  • Hello rdssh,

    here is code for renaming any 1st column:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Promoted = Table.PromoteHeaders(Source),
        RenCols = Table.RenameColumns(Promoted,{{Table.ColumnNames(Promoted){0}, "Date"}})
    in
        RenColsThis part:

    Table.ColumnNames(Promoted){0}
    gives us the name of first column (zero-based)  in "Promoted" table


    Maxim Zelensky Excel Inside

    • Marked as answer by rudi.s Thursday, November 12, 2015 6:33 AM
    Wednesday, November 11, 2015 3:17 PM
  • Couple of options which all depends on how you use Power Query.  The advanced editor option is probably the one that you want, but have included GUI options as well.

    Using the GUI only

    Option 1

    Before you promote the first row to headers, change the first column (which I am assuming is a text field) to a date field.  Then when you promote the first row, it won't use the date field as the header and it will just call it 'Column 1'.

    You now have a generic name in which you can refer to.

    Option 2

    Rename all of the columns without promoting the first row as headers and then delete the first row.

    Using the advanced Editor

    What I have done is created another source that looks at the first value in the first column (which before you promote the headers, should be "01 Nov 2015".  I then promote the headers and then rename the Date Column but instead of referencing it by name, use the name that we have taken from Source2.  This can now be used dynamically 

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Source2 = Source{0}[Column1],
        PromHeaders = Table.PromoteHeaders(Source),
        RenameDateHeader = Table.RenameColumns(PromHeaders,{{Source2, "Date"}})


    in
    RenameDateHeader

    • Marked as answer by rudi.s Thursday, November 12, 2015 6:33 AM
    Wednesday, November 11, 2015 10:45 AM

All replies

  • Couple of options which all depends on how you use Power Query.  The advanced editor option is probably the one that you want, but have included GUI options as well.

    Using the GUI only

    Option 1

    Before you promote the first row to headers, change the first column (which I am assuming is a text field) to a date field.  Then when you promote the first row, it won't use the date field as the header and it will just call it 'Column 1'.

    You now have a generic name in which you can refer to.

    Option 2

    Rename all of the columns without promoting the first row as headers and then delete the first row.

    Using the advanced Editor

    What I have done is created another source that looks at the first value in the first column (which before you promote the headers, should be "01 Nov 2015".  I then promote the headers and then rename the Date Column but instead of referencing it by name, use the name that we have taken from Source2.  This can now be used dynamically 

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Source2 = Source{0}[Column1],
        PromHeaders = Table.PromoteHeaders(Source),
        RenameDateHeader = Table.RenameColumns(PromHeaders,{{Source2, "Date"}})


    in
    RenameDateHeader

    • Marked as answer by rudi.s Thursday, November 12, 2015 6:33 AM
    Wednesday, November 11, 2015 10:45 AM
  • Lawrence,

    TX a stack for the advice.

    I'll give each of your suggestions a go and see which I can adapt to suit. If I have further questions, I'll post back. in theory, your advice looks great! Will let you know, and then mark as answered!

    Cheers

    Wednesday, November 11, 2015 11:17 AM
  • You're welcome.
    Wednesday, November 11, 2015 11:18 AM
  • Hello rdssh,

    here is code for renaming any 1st column:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Promoted = Table.PromoteHeaders(Source),
        RenCols = Table.RenameColumns(Promoted,{{Table.ColumnNames(Promoted){0}, "Date"}})
    in
        RenColsThis part:

    Table.ColumnNames(Promoted){0}
    gives us the name of first column (zero-based)  in "Promoted" table


    Maxim Zelensky Excel Inside

    • Marked as answer by rudi.s Thursday, November 12, 2015 6:33 AM
    Wednesday, November 11, 2015 3:17 PM
  • TX Maxim.

    I'm waiting for feedback from the person I'm helping and will reply with marked answer when I do.

    Cheers guys...grateful to receive your help!

    Wednesday, November 11, 2015 8:21 PM
  • TX Lawrence and Maxim,

    The 'M' code that both of you supplied worked great.

    Issue resolved!

    Cheers to both!

    Thursday, November 12, 2015 6:34 AM