Forum Discussion

Fcoatis's avatar
Fcoatis
Post Patron
5 years ago
Solved

PowerQuery rename columns by position inside a column with tables

Hello all,

 

Need help here. I´m trying to rename Columns by position, but my tables are inside a column. Here´s what I´m trying to do:

 

Thanks in advance

Need Help
  • PhilipTreacy's avatar
    PhilipTreacy
    5 years ago

    Hi Fcoatis 

    Use Table.ColumnNames to access the column name, referenced by position:

     

    Table.RenameColumns
(
   [Limpa],
   {
   {Table.ColumnNames([Limpa]){0},"Col1"},
   {Table.ColumnNames([Limpa]){1},"Col2"},
   {Table.ColumnNames([Limpa]){2},"Col3"},
   {Table.ColumnNames([Limpa]){3},"Col4"}
   }
)

     

     

    Regards

    Phil

3 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    5 years ago

    Hi Fcoatis 

    Use Table.ColumnNames to access the column name, referenced by position:

     

    Table.RenameColumns
(
   [Limpa],
   {
   {Table.ColumnNames([Limpa]){0},"Col1"},
   {Table.ColumnNames([Limpa]){1},"Col2"},
   {Table.ColumnNames([Limpa]){2},"Col3"},
   {Table.ColumnNames([Limpa]){3},"Col4"}
   }
)

     

     

    Regards

    Phil

    • AllisonKennedy's avatar
      AllisonKennedy
      Community Champion
      5 years ago

      Thanks PhilipTreacy  - that's heaps more efficient than the custom function I was working on. Looks something like this:

       

       

      = (Parameter1 as binary) => let
      Source = Csv.Document(Parameter1,[Delimiter=",", Columns=6, Encoding=1252, QuoteStyle=QuoteStyle.None]),
      #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
      #"Demoted Headers" = Table.DemoteHeaders(#"Promoted Headers"),
      #"Kept First Rows" = Table.FirstN(#"Demoted Headers",1),
      #"Transposed Table" = Table.Transpose(#"Kept First Rows"),
      #"Added Index" = Table.AddIndexColumn(#"Transposed Table", "Index", 1, 1, Int64.Type),
      #"Added Conditional Column" = Table.AddColumn(#"Added Index", "New Headings", each if [Index] = 1 then "Col1" else if [Index] = 2 then "Col2" else if [Index] = 3 then "Col3" else [Index]),
      #"Removed Other Columns" = Table.SelectColumns(#"Added Conditional Column",{"New Headings"}),
      #"Transposed Table1" = Table.Transpose(#"Removed Other Columns"),
      #"Appended Query" = Table.Combine({#"Transposed Table1", #"Demoted Headers"}),
      #"Promoted Headers1" = Table.PromoteHeaders(#"Appended Query", [PromoteAllScalars=true]),
      #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers1",{{"Col1", type text}, {"Col2", type text}, {"Col3", type text}, {"4", type text}, {"5", type text}, {"6", type text}}),
      #"Removed Top Rows" = Table.Skip(#"Changed Type",1)
      in
      #"Removed Top Rows"