Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
jaco1951
Helper III
Helper III

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

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:

 

Capture.JPG

 

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

1 ACCEPTED SOLUTION
MarcelBeug
Community Champion
Community Champion

I would do it in Power Query. Smiley Happy

 

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"

 

Specializing in Power Query Formula Language (M)

View solution in original post

2 REPLIES 2
MarcelBeug
Community Champion
Community Champion

I would do it in Power Query. Smiley Happy

 

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"

 

Specializing in Power Query Formula Language (M)

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.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.