PowerQuery rename columns by position inside a column with tables

[Fcoatis]

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

[PhilipTreacy · Accepted solution]

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

[Fcoatis]

@PhilipTreacy Thank you very much!!! It worked.

[AllisonKennedy]

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"