Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

How to update a column name when data is refreshed

I have a power query where I use two blank query's to add two columns based on the data in first column. = (YourWorkSheet as table ) as text=> let SheetCellA1 =YourWorkSheet[Column1]{0}, S...
Need Help
Tutorial Requests
  • PhilipTreacy's avatar
    PhilipTreacy
    5 years ago

    Hi Anonymous 

    If the columns you want to rename will always be the 1st and 2nd ones, you can reference the columns by their index (indexed from 0)

     

     

    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{Table.ColumnNames(#"Promoted Headers"){0}, "TIME"}, {Table.ColumnNames(#"Promoted Headers"){1}, "MARKET"}, {"Column3", "SEGMENT"}, {"Column4", "SUB-SEGMENT"}, {"Column5", "UPC"}, {"Column6", "DESCRIPTION"}, {"Column7", "SIZE"}, {"Column8", "FLAVOR"}, {"Column9", "BRAND"}})

     

     

    Phil 

    If I answered your question please mark my post as the solution.
    If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

  • PhilipTreacy's avatar
    PhilipTreacy
    5 years ago

    Anonymous 

    No these 2 lines are not needed

     

    DynamicNameHeader= Table.ColumnNames(#"Promoted Headers"){0},
DynmaicNameHeader.1= Table.ColumnNames(#"Promoted Headers"){1},

     

    All they do is assign the names of the first 2 columns to variables.  But these variables are not used in the subsequent  Table.RenameColumns

     

    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{Table.ColumnNames(#"Promoted Headers"){0}, "TIME"}, {Table.ColumnNames(#"Promoted Headers"){1}, "MARKET"}, {"Column3", "SEGMENT"}, {"Column4", "SUB-SEGMENT"}, {"Column5", "UPC"}, {"Column6", "DESCRIPTION"}, {"Column7", "SIZE"}, {"Column8", "FLAVOR"}, {"Column9", "BRAND"}})

     

     Phil

    If I answered your question please mark my post as the solution.
    If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

edhans's avatar
edhans
Community Champion
5 years ago

Yes. You want to rename the columns based on their index position I think, which you call 1 and 2, but Power Query calls this 0 and 1, since it indexes at 0 Anonymous 

 

So this:

Become this:

 

See this M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKkktLlHSUcrNL0pVALNjYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    RenameFields = 
        Table.RenameColumns(
            Source,
            {
                {Table.ColumnNames(Source){0}, "Renamed Field 1"},
                {Table.ColumnNames(Source){1}, "Renamed Field 2"}
            }
        )
in
    RenameFields

The key is the Table.RenameColumns() function. The first one is the field name to rename. Table.ColumnNames() returns the first (0) and second (1) column name from the Source table in my example.

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

 

edhans's avatar
edhans
Community Champion
5 years ago

Anonymous Just curious, can you advise on my solution what didn't meet your needs? Just trying to understand the original question.