Forum Discussion
Solved
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...
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
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])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 DataSorry 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.
