CODEWITHSUNDEEP
excelpowerqueryexcel-2016
Max
Max

Reputation: 449

power query - how to move rows down within a specific column

I need to move a specific column 4 rows down, is this possible to do with power query? If possibile, how can I achieve this? References are appreciated.

Upvotes: 1

Views: 5186

Answers (2)

MarcelBeug
MarcelBeug

Reputation: 2967

Apologies, on second thought, it CAN be achieved using menu options. In this version, all column names of the test table are hard coded.

let
    #"QUERY SUMMARY" =
        "In this query, column ""ColToMove"" is shifted down 4 positions.#(lf)" &
        "The code is mainly generated via the menu options in the Query Editor.#(lf)" &
        "Index columns are added, starting with 0 and with 4.#(lf)" &
        "Merging the table with itself on the different Index columns, cause the merged version to be shifted down 4 positions.#(lf)" &
        "The original sort is restored, columns are removed and reordered.",
    Source = Table1,
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 4, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index"},#"Added Index1",{"Index.1"},"Added Index1",JoinKind.FullOuter),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"ColToMove"}),
    #"Expanded Added Index1" = Table.ExpandTableColumn(#"Removed Columns", "Added Index1", {"ColToMove", "Index.1"}, {"ColToMove", "Index.1.1"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Added Index1",{{"Index.1.1", Order.Ascending}, {"Index.1", Order.Ascending}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"Index", "Index.1", "Index.1.1"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"ID", "ColToMove", "Col3", "Col4", "Col5", "Col6", "Col7", "Col8", "Col9", "Col10"})
in
    #"Reordered Columns"

Upvotes: 1

MarcelBeug
MarcelBeug

Reputation: 2967

I don't think it can be achieved by using the menu options in the Query Editor, but it can be done with coding in the Advanced Editor.

If the column you want to move down is called "ColToMove" you can use the code below. Notice that the other columns will get nulls in the last 4 row of the result.

let
    #"**** QUERY SUMMARY ****" =
        "This query moves column ""ColToMove"" 4 rows down, by:#(lf)" &
        "1. Saving the original column sequence and table type.#(lf)" &
        "2. Drilling down into ""ColToMove"".#(lf)" &
        "3. Adding 4 nulls at the start of the resulting list.#(lf)" &
        "4. Removing the ""ColToMove"" from the original table." &
        "5. Transforming the resulting table in a list of list.#(lf)" &
        "6. Adding the list from step 3.#(lf)" &
        "7. Transforming the result into a table, with the moved column as last column.#(lf)" &
        "8. Reordering the columns to the original sequence.#(lf)" &
        "9. Restoring the original table type.",

    Source = Table1,
    OriginalColumnSequence = Table.ColumnNames(Source),
    OriginalTableType = Value.Type(Source),

    ColToMove = Source[ColToMove],
    MovedCol = List.Repeat({null},4)&ColToMove,

    RemovedColToMov = Table.RemoveColumns(Source,{"ColToMove"}),
    TableToColumns = Table.ToColumns(RemovedColToMov),
    AddedMovedCol = TableToColumns&{MovedCol},

    ColumnNames = Table.ColumnNames(RemovedColToMov)&{"ColToMove"},
    TableFromColumns = Table.FromColumns(AddedMovedCol,ColumnNames),

    ReorderedColumns = Table.ReorderColumns(TableFromColumns,OriginalColumnSequence),
    RestoredOriginalTableType = Value.ReplaceType(ReorderedColumns,OriginalTableType)
in
    RestoredOriginalTableType

Upvotes: 1

Related Questions