Forum Discussion
Insert Custom Column after a specific Column index in Table instead of at the end of Table
How do i Insert a Custom Column after a specific Column index in a Table instead of the default behaviour of placing the new column at the end of Table? The AddColumn() command does not have a index position parameter in it.
for e.g. i want to insert the new column after the 6th Column.
I think there should have been a Table function similar to :
List.InsertRange(list as list, index as number, values as list) as list Is there something like a one-liner code?
Hello Anonymous
you have to apply a Table.ReorderColumns afterwards and calculate the new order dynmacially. Check out this solution. Use the variable IndexWhereToPlaceLastColumn the decide where to put your last column from your previous step.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i44FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t, D = _t, E = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", type text}, {"B", type text}, {"C", type text}, {"D", type text}, {"E", type text}}), IndexWhereToPlaceLastColumn = 2, NewOrder = Table.ReorderColumns ( #"Changed Type", List.InsertRange(List.RemoveLastN(Table.ColumnNames(#"Changed Type"), 1), IndexWhereToPlaceLastColumn, {List.Last(Table.ColumnNames(#"Changed Type"))}) ) in NewOrderCopy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
2 Replies
Hello Anonymous
you have to apply a Table.ReorderColumns afterwards and calculate the new order dynmacially. Check out this solution. Use the variable IndexWhereToPlaceLastColumn the decide where to put your last column from your previous step.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i44FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t, D = _t, E = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", type text}, {"B", type text}, {"C", type text}, {"D", type text}, {"E", type text}}), IndexWhereToPlaceLastColumn = 2, NewOrder = Table.ReorderColumns ( #"Changed Type", List.InsertRange(List.RemoveLastN(Table.ColumnNames(#"Changed Type"), 1), IndexWhereToPlaceLastColumn, {List.Last(Table.ColumnNames(#"Changed Type"))}) ) in NewOrderCopy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
JimmyNo. It will always go to the end. The Table.AddColumn() function adds a column to the table in the first parameter of that function. You cannot change it. Note that the column position is irrelevant to Power BI. Once you load it into the model, the columns are all sorted alphabetically.
The only place a column position actually matters is an Excel table, and in that case, after you add the column you would either need to:
- Change the column position by rearranging them
- If you use a Remove Other Columns function, if you select the columns in the order you want them via CTRL-CLICK - Table.SelectColumns() will simultaneously remove extra columns and order them the way you want.
