Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
Data
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.
Thank you for your help!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.