Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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?
Solved! Go to Solution.
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
NewOrder
Copy 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
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
NewOrder
Copy 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
No. 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:
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting