WAS shift-column-data-in-a-group-of-data-and-null-oldest ...

[Anonymous]

I had read a message with a request to transform a column of dates that would shift the elements down by one position.
If it is useful to someone, I will post a proposal for a solution

[Vijay_A_Verma]

Here is my solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"JobStartdate", type date}, {"Serial", Int64.Type}, {"Footage", Int64.Type}, {"Hours", Int64.Type}, {"CountOfRuns", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Serial"}, {{"Temp", each _, type table [Serial=nullable number, JobStartdate=nullable date, Footage=nullable number, Hours=nullable number, CountOfRuns=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Temp],"Index")),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"JobStartdate", "Footage", "Hours", "CountOfRuns", "Index"}, {"JobStartdate", "Footage", "Hours", "CountOfRuns", "Index"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom", each if [Index]=0 then null else #"Expanded Custom"[JobStartdate]{[Index]-1}),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Index", "Temp"})
in
    #"Removed Columns"

[Vijay_A_Verma]

Another solution is to use this formula referring to a 0 based Index

try if #"Added Index"[Serial]{[Index]-1} = [Serial] then #"Added Index"[JobStartdate]{[Index]-1} else null otherwise null

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"JobStartdate", type date}, {"Serial", Int64.Type}, {"Footage", Int64.Type}, {"Hours", Int64.Type}, {"CountOfRuns", Int64.Type}}),
    Custom1 = #"Changed Type",
    #"Added Index" = Table.AddIndexColumn(Custom1, "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each try if #"Added Index"[Serial]{[Index]-1} = [Serial] then #"Added Index"[JobStartdate]{[Index]-1} else null otherwise null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"})
in
    #"Removed Columns"

[v-yingjl]

Hi @Anonymous ,

Thanks for your sharing, it would help others who may have the same issue on the community if you can kindly share the solution about it.

Best Regards,
Community Support Team _ Yingjie Li

[Anonymous · Accepted solution]

Hi @v-yingjl,

my solution to the problem is already in the attached file. however, the data file that I am attaching now was missing.
The message in which the request was made has been deleted (perhaps by the author!?!). So I don't have any particular details other than what I've already written.