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

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.

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

4 Replies

v-yingjl
Community Support
4 years ago
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
  Not applicable
  4 years ago
  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.
  ExampleData.xlsx11 KB

Vijay_A_Verma

Most Valuable Professional

4 years ago

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

Most Valuable Professional

4 years ago

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"

Forum Discussion

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