Get Previous Value in Column

[bernate]

Hello, I have a Date column with the following values:

I want to create a new column named "Start Date" that would return the following. The 12/1/2010 value would be a Min Date from another table.

My current attempt is the M code below, but it does it on a row-by-row basis. I'm guessing I need to group by the Date field?

#"SortedRows" = Table.Sort(#"Changed Type1",{{"Date", Order.Ascending}}),
#"ShiftedDates" = {null} & List.RemoveLastN( Table.Column( SortedRows, "Date" ), 1 ),
#"ListOfLists" = Table.ToColumns( SortedRows ) & { ShiftedDates },
#"LastStep"= Table.FromColumns(
ListOfLists,
Table.ColumnNames( SortedRows ) & { "Previous Date" }
)
in
LastStep

[dufoq3]

Hi @bernate, I'm not sure why are you trying to shift rows when you need min date from another table. You can add this code as custom column:

List.Min(Another_Table[ColumnName_WithDateYouWant])

[bernate]

I will need the min date eventually but for now it can be left as null.

[dufoq3 · Accepted solution]

I'm not sure if you want this and what is your source, but if you only want to shift 3 rows, you can do that this way.

let
    fnShift = (tbl as table, col as text, shift as nullable number, optional newColName as text, optional _type as type) as table =>
        //v 3. parametri zadaj zaporne cislo ak chces posunut riadky hore, kladne ak dole, 4. je nepovinny (novy nazov stlpca), 5. je nepovinny typ
        let
            a = Table.Column(tbl, col),
            b = if shift = 0 or shift = null then a else if shift > 0
                then List.Repeat({null}, shift) & List.RemoveLastN(a, shift)
                else List.RemoveFirstN(a, shift * -1) & List.Repeat({null}, shift * -1),    
            c = Table.FromColumns(Table.ToColumns(tbl) & {b}, Table.ColumnNames(tbl) &
                ( if newColName <> null then {newColName} else
                    if shift = 0 then {col & "_Duplicate"} else
                    if shift > 0 then {col & "_PrevValue"} 
                    else              {col & "_NextValue"} )),
            d = Table.TransformColumnTypes(c, {List.Last(Table.ColumnNames(c)), if _type <> null then _type else type any})
        in
            d,
            
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDSNzLUN7RUitVB51jqAxFpHENDfQMTwhwDoCWm+kYGVODEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    GroupedRows = Table.Group(Source, {"Date"}, {{"All", each _, type table}}),
    Ad_DatePrevValue = fnShift(GroupedRows, "Date", 1),
    Ad_Data = Table.AddColumn(Ad_DatePrevValue, "Data", each Table.AddColumn([All], "Start Date", (x)=> [Date_PrevValue]), type table),
    Data = Table.Combine(Ad_Data[Data])
in
    Data

[bernate]

Sorry I should have been more clear, the 3 rows was for example purposes. 02/21/19 has 48 rows, 09/09/19 has 51, etc. The number of rows varies by the date value, which is why I thought doing something to group by Date would let me reference the previous Date value and add it to the new column.

[dufoq3]

Hi, I've edited my previous post. Check it.

[bernate]

Thank you for your help!

[dufoq3]

You're welcome.