Rearrange Columns by position

[hatstand]

Hi All,

If I have Column1, Column2, Column3 and add a new Column and then move this to position 2, the code will reflect this. The problem is that if new columns are added to the data or renamed etc the code will fail.

Can I add a new column but directly into position 2 or can I move the column without the list needing to list all the existing columns?

I have read numerous pages and watched numerus videos but can't seem to find an answer. so any help would be very much appreciated.

 

[RoryA]

You can use a combination of List.RemoveRange and List.InsertRange to move items around in a list, so applying that to Table.ColumnNames should get the result you need.

 

[hatstand]

Thanks Rory. That gave me what I needed to solve it. I'm quite new to query, so it took a while. Here is the code I created, just in case anyone needs it in future.
let
Source = MyFile,
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),

// Get the index of the column "Column6"
Column3Index = List.PositionOf(Table.ColumnNames(#"Promoted Headers"), "Header6"),

// Exclude "Column6" from the list of columns
ColumnsList = List.RemoveItems(Table.ColumnNames(#"Promoted Headers"), {"Header6"}),

// Calculate the new position for "Column6" after excluding it from the list
NewPosition = if Column3Index < 2 then 1 else Column3Index - 3,

// Insert "Column6" at the desired position
ReorderedColumns = Table.ReorderColumns(#"Promoted Headers", List.InsertRange(ColumnsList, NewPosition, {"Header6"}))
in
ReorderedColumns