how to shift row value to next row

[Uzi2019]

Hi Community,

I have two column one is date another is values. I have tried a lot many approaches but not getting any complete result. 

So basicaly, value should be shifted to next row as per date and if same date is repeated so same values should be there. If i am not clear so below is the expected ouput.

Input is like below: 

Values	Date
138	1/1/2020
120	1/2/2020
80	1/3/2020
90	1/4/2020
110	1/5/2020
124	1/1/2020
100	1/3/2020

Expected Output:

Date	Values	Output
1/1/2020	138	138
1/1/2020	124	124
1/2/2020	120	124
1/3/2020	80	80
1/3/2020	100	100
1/4/2020	90	100
1/5/2020	110	90

TIA!

[MFelix · Accepted solution]

Hi @Uzi2019 ,

Not really sure how you are getting the sorting done but to get the same sorting I did the following steps in the query editor:

• Added an Index column
• Sorted by:
  • Ascending Dates
  • Ascending Index
• Deleted the Index column
• Added a new Index column
• Add the following calculated column

  if [Index] = 0
  then [Values]
  else if [Index] = List.Max(#"Added Index1"[Index]) then #"Added Index1"{[Index] - 1}[Values] else 
    if (#"Added Index1"{[Index]
      - 1}[Date]
      = [Date]
      = false and #"Added Index1"{[Index] + 1}[Date]
      = [Date]
      = false)
    then #"Added Index1"{[Index] - 1}[Values]
    else [Values]

Check full code below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjS2UNJRMjDUByIjAyMDpVgdoCCQBgoaoQhagMWMUcQswWImqJoNwYKmaCaaYLHGAN3IWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Values = _t, Date = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Index",{{"Values", Int64.Type}, {"Date", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date", Order.Ascending}, {"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"}),
    #"Added Index1" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index1", "Custom", each if [Index] = 0
  then [Values]
  else if [Index] = List.Max(#"Added Index1"[Index]) then #"Added Index1"{[Index] - 1}[Values] else 
    if (#"Added Index1"{[Index]
      - 1}[Date]
      = [Date]
      = false and #"Added Index1"{[Index] + 1}[Date]
      = [Date]
      = false)
    then #"Added Index1"{[Index] - 1}[Values]
    else [Values])
in
    #"Added Custom"

This may need adjustments based on the sorting of your data.