Forum Discussion
Get Previous Value in Column
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
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
7 Replies
I will need the min date eventually but for now it can be left as null.
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
