PowerBI next row value to column base on unique column values

Question

My data in powerbi is this:

Order number	seconds
10	51
10	100
10	2
10	0
20	12
20	722
20	3
30	200
40	98
40	0
50	5
50	200
50	200

Based on the order number, I am trying to make a new column to get the next row's seconds.

The final result should be like this

Order number	seconds	Next seconds
10	51	100
10	100	2
10	2	0
10	0	'BLANK'
20	12	722
20	722	3
20	3	'BLANK'
30	200	'BLANK'
40	98	0
40	0	'BLANK'
50	5	200
50	200	200
50	200	'BLANK'

Answer 1

In PowerQuery:

1. Right-click on the Order number column header and select Group by.... Set New column name to Rows, and Operation to All rows.

2. Add a new step with the following:

Table.TransformColumns(
    #"Grouped rows", // <-- update to match previous step's name
    {"Rows", each Table.FromColumns(
        Table.ToColumns(_) & { List.RemoveFirstN([seconds] & {null}, 1) },
        Table.ColumnNames(_) & { "Next seconds" }
      )
    }
  )

3. Lastly, expand the Rows by click on the "split" arrows ↰↱, select seconds and Next seconds without Use original column name as prefix.

---

Here is the full PQ script:

let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRQ0lEyNVSK1YGyDQ0MEBwjBBMiagRWYoRgmxshcYzBTGOwTqgxJiCOpQWCDRE2BVuLYMKUIzixAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order number" = _t, seconds = _t]),
  #"Changed column type" = Table.TransformColumnTypes(Source, {{"Order number", Int64.Type}, {"seconds", Int64.Type}}, "en-US"),
  #"Grouped rows" = Table.Group(#"Changed column type", {"Order number"}, {{"Rows", each _, type nullable table[#"Order number" = nullable Int64.Type, seconds = nullable Int64.Type]}}),
  ShiftSecondsUp = Table.TransformColumns(
    #"Grouped rows",
    {"Rows", each Table.FromColumns(
        Table.ToColumns(_) & { List.RemoveFirstN([seconds] & {null}, 1) },
        Table.ColumnNames(_) & { "Next seconds" }
      )
    }
  ),
  #"Expanded Rows" = Table.ExpandTableColumn(ShiftSecondsUp, "Rows", {"seconds", "Next seconds"}, {"seconds", "Next seconds"})
in
  #"Expanded Rows"