Shift a column down by one line

[medmbchr1989]

Hi

In powerquery, I would like to insert a row in a specific column which is a copy of another one, I want to create a shift between them, how can I do it without using code as it requires lots of memory.

Output (target is column 2, X is random just to create an additionnal cell in my column):

Column 1 - Column2

A - X

B - A

C - B

D - C

E - D

[Irwan · Accepted solution]

hello @medmbchr1989 

please check if this accomodate your need.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1YlWcgKTzmDSBUy6KsXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1, Int64.Type),
#"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Index"}, #"Added Index1", {"Index.1"}, "Added Index1", JoinKind.LeftOuter),
#"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Column1"}, {"Column1.1"}),
#"Sorted Rows" = Table.Sort(#"Expanded Added Index1",{{"Column1", Order.Ascending}})
in
#"Sorted Rows"

The steps area :

1. create index column start with 0

2. create another index column start with 1 (basically to shift 1 row)

3. Merge Query between those two index column to return value in matched index.

Hope this will help.

Thank you.

[ThxAlot · Accepted solution]

Easy enough,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1YlWcgKTzmDSBUy6KsXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    Column1 = Source[Column1],
    ToTable = Table.FromColumns({Column1, {"DUMMY"} & List.RemoveLastN(Column1,1)})
in
    ToTable

[Irwan · Accepted solution]

hello @medmbchr1989 

please check if this accomodate your need.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1YlWcgKTzmDSBUy6KsXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1, Int64.Type),
#"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Index"}, #"Added Index1", {"Index.1"}, "Added Index1", JoinKind.LeftOuter),
#"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Column1"}, {"Column1.1"}),
#"Sorted Rows" = Table.Sort(#"Expanded Added Index1",{{"Column1", Order.Ascending}})
in
#"Sorted Rows"

The steps area :

1. create index column start with 0

2. create another index column start with 1 (basically to shift 1 row)

3. Merge Query between those two index column to return value in matched index.

Hope this will help.

Thank you.