Create new column with data from another column previous row - using groups of data

[jaco1951]

Dear all

I am trying to pull the previous row data from another column to create a new column called "FromDate".

The column I already got is called "ToDate", but I need to know from what date the period of payment is calculated. I have seen similar questions here, but I have not been able to replicate the answers to fit into my model.

I have groups of Securities, so that the first row in each will use the "IssuedDate", as there are no previous row. Then the next row will continue with previous row data from column ToDate +1 day.

There are also the possiblility for extra payments, but these will be recognized in many ways, one is that there are no InterestRate related to such payments, I think therefore it is possible to filter them away based on that criterea.

Here is an example:

If anyone could assist me on how to create a formula that will solve this I would be very thankful.

I am not sure if it is better to do this within the load of data, or if it should be done at the front end creating a new column there.

Kind regards

Espen

[MarcelBeug · Accepted solution]

I would do it in Power Query.

In general, the trick to get data from the previous row on the current row, is to add indices (starting with 0 and 1) and then merging the table with itself, like in the query below.

In this case, also the records with null percentages are temporarily removed and later added back.

Notice that #"Added Index" continues with "RemovedNulls"; "RemovedRecords" contains the records that will later be added back. 

let
    Source = Securities,
    AddedOriginalSort = Table.AddIndexColumn(Source, "OriginalSort", 1, 1),
    RemovedNulls = Table.SelectRows(AddedOriginalSort, each ([InterestRate] <> null)),
    RemovedRecords = Table.SelectRows(AddedOriginalSort, each ([InterestRate] = null)),
    #"Added Index" = Table.AddIndexColumn(RemovedNulls, "Index", 0, 1),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index"},#"Added Index1",{"Index.1"},"Previous",JoinKind.LeftOuter),
    #"Expanded Previous" = Table.ExpandTableColumn(#"Merged Queries", "Previous", {"Security", "ToDate"}, {"Previous.Security", "Previous.ToDate"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Previous",{{"Index", Order.Ascending}}),
    #"Added Custom" = Table.AddColumn(#"Sorted Rows", "FromDate", each if [Previous.Security] <> [Security] then [IssuedDate] else Date.AddDays([Previous.ToDate],1), type date),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index", "Index.1", "Previous.Security", "Previous.ToDate"}),
    #"Appended Query" = Table.Combine({#"Removed Columns", RemovedRecords}),
    #"Sorted Rows1" = Table.Sort(#"Appended Query",{{"OriginalSort", Order.Ascending}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows1",{"OriginalSort"})
in
    #"Removed Columns1"

[jaco1951]

Thank you very much!!!

Excellent solution. I have spend some time studying it now, making it fit into my data load, and it seems to work very well.