Create new column based on values from 2 rows

[RICHARDCHIANG1]

hi,

I have a table with a 'Date' column and wish to create column called 'DaysToNext' which is derived from the current row's Date and next row's date 

i.e. DaysToNext = Next Row's Date - Current Row's Date 

Can someone help here? 

[ferryv · Accepted solution]

You could use an Index column and find the date with the next index and subtract the date of the current row from the one of the next row.

For example, in this example:

- I replicated the column as a number or text (just to recreate the column - you might not need this)

- Added an index

- Converted the date to type date (as it was text or number) - in your case you could also just subtract the numbers of course, but thought this was cleaner

- Stored the maximum index number in "variable" Max

- Subtracted the current date from the date with the next index number (step Days) - note the if statement as it will cause an error if not added due to it trying to subtract a date from a value that does not exist for the last row. I made the last row value for DateToNext 0, but could be any number or null. 

- Removed the redundant "helper "columns

let Source = Table.FromRecords({
[DateText = 20240501],
[DateText = 20240502],
[DateText = 20240503],
[DateText = 20240507],
[DateText = 20240508],
[DateText = 20240509],
[DateText = 20240510],
[DateText = 20240513]
}),
Index = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
Date = Table.AddColumn(Index,"Date", each Date.From(Text.From([DateText])),type date),
Max = List.Max(Date[Index]),
Days = Table.AddColumn(Date, "DaysToNext", each if [Index] <Max then Date[DateText]{[Index]+1} - Date[DateText]{[Index]} else 0),
RemovedRedundantCols = Table.RemoveColumns(Days,{"DateText", "Index"})
in
RemovedRedundantCols

Hope this helps

[RICHARDCHIANG1]

Hi All,

It turns out that the problem is a bit more complicated and I'm not sure the Index solution works. We can only calculate 'DaysToNext' from 2 rows, if they both have other common attributes. For example, if the 2 rows have the column 'Name' in common

For example, row 2 (with Name = 'John'), the 'DaysToNext' should be 5. The reason is that we have to skip row 3 (which has Name = 'Peter') ...

Can someone pls advise further? thank you! 

Date	Name	DaysToNext
20240501	John	1
20240502	John	5
20240503	Peter	NA
20240507	John	4
20240508	Jen	2
20240510	Jen	7
20240517	Jen	NA

[ferryv]

The key here is to use grouping, which enables tre creation of indices by group (in this case per name).

For instance, if you use the following as your starting point:

You can use something like this:

//This is just the initial source info, and might be different for you 
let Source = Table.FromRecords({ 
   [DateText = 20240501, Name="John"], 
   [DateText = 20240502, Name="John"], 
   [DateText = 20240503, Name="Peter"], 
   [DateText = 20240507, Name="John"], 
   [DateText = 20240508, Name="Jen"], 
   [DateText = 20240510, Name="Jen"], 
   [DateText = 20240517, Name="Jen"] 
}), 

//Main Index added for sorting later if needed
MainIndex = Table.AddIndexColumn(Source,"MainIndex",0), 

//Rows grouped to enable name specific indexing
GroupRows = Table.Group(MainIndex, {"Name"}, {{"Count", each _, type table [DateText=number, Name=text, Date=date,MainIndex=number]}}), 

//Adding the index for each name
IndexPlaceholder = Table.AddColumn(GroupRows, "IndexPlaceholder", each Table.AddIndexColumn([Count],"Index",0)),

//Getting the maximum index per name
MaxPlaceholder = Table.AddColumn(IndexPlaceholder,"Max", each List.Max([IndexPlaceholder][Index])),

//Expanding the grouped table
Expand = Table.ExpandTableColumn(MaxPlaceholder, "IndexPlaceholder", {"DateText", "MainIndex", "Index"}, {"DateText", "MainIndex", "Index"}),

//Adding the number of days to the next entry with the same name
Days = Table.AddColumn(Expand, "DaysToNext", each if [Index] <[Max] and Expand[Name]{[Index]+1} = Expand[Name]{[Index]} then Expand[DateText]{[Index]+1} - Expand[DateText]{[Index]} else "NA"),

//Sorting the table back to the original order (as it created an ordering per name)
Sort = Table.Sort(Days,{{"MainIndex", Order.Ascending}}),

//Remove redundant columns
Remove = Table.RemoveColumns(Sort,{"Count", "MainIndex", "Index", "Max"})

in Remove

Your output would be something like this:

the 3 "NA"'s are the last date for each name...

[RICHARDCHIANG1]

thank you all

[slorin]

Hi, @RICHARDCHIANG1 

Another solution without index

let
Source = YourSource,
Schema = Table.SelectRows(Table.Schema(Source), each [TypeName]<>"Any.Type"),
DateFrom = List.Transform(Source[Date], each Date.From(Text.From(_, "en-US"))),
AddDaysToNext = Table.FromColumns(
     Table.ToColumns(Source) &
        {List.Transform(List.Zip({DateFrom, List.Skip(DateFrom)}), each Number.From(_{1} - _{0}))}, 
     Table.ColumnNames(Source) & {"DaysToNext"}),
TransformTypes = Table.TransformColumnTypes(AddDaysToNext, 
     List.Zip({Schema[Name], Schema[TypeName]}) & {{"DaysToNext", Int64.Type}})
in
TransformTypes

Stéphane 

[v-jingzhan-msft · Accepted solution]

Hi @RICHARDCHIANG1 

Here is another Power Query solution for your reference. The idea is to:

1. add an index column starting from 1 and the second index column starting from 0, 

2. merge the current query to itself by two different index columns,

3. expand the merged result to get the next row date for each row, 

4. add a custom column to get the duration days between two days,

5. remove unnecessary columns. 

let 
Source = Table.FromRecords({
    [DateText = 20240501],
    [DateText = 20240502],
    [DateText = 20240503],
    [DateText = 20240507],
    [DateText = 20240508],
    [DateText = 20240509],
    [DateText = 20240510],
    [DateText = 20240513]
}),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DateText", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 0, 1, Int64.Type),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Index"}, #"Added Index1", {"Index.1"}, "Added Index1", JoinKind.LeftOuter),
    #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"DateText"}, {"DateText.1"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Added Index1", "DaysToNext", each Duration.Days(Date.From([DateText.1]) - Date.From([DateText]))),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"DateText", "DaysToNext"})
in
    #"Removed Other Columns"

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[RICHARDCHIANG1]

Hi, 

Thank you. A problem is that we can only calculate 'DaysToNext' from 2 rows, if they both have other common attributes. For example, if the 2 rows have the column 'Name' in common ... I tried adding in a further condition (as highlighted in bold) but this method assume the rows are consecutive so doesn't quite work ... 

let Source = Table.FromRecords({
[DateText = 20240501, Name="John"],
[DateText = 20240502, Name="John"],
[DateText = 20240503, Name="Peter"],
[DateText = 20240507, Name="John"],
[DateText = 20240508, Name="John"],
[DateText = 20240509, Name="John"],
[DateText = 20240510, Name="John"],
[DateText = 20240513, Name="John"]
}),
Index = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
Date = Table.AddColumn(Index,"Date", each Date.From(Text.From([DateText])),type date),
Name = Table.Column(Source, "Name") as list,
Max = List.Max(Date[Index]),
Days = Table.AddColumn(Date, "DaysToNext", each if [Index] <Max and Name{[Index]} = Name{[Index] + 1}
then Date[DateText]{[Index]+1} - Date[DateText]{[Index]} else 0),
RemovedRedundantCols = Table.RemoveColumns(Days,{"DateText", "Index"})

in
RemovedRedundantCols

[ferryv · Accepted solution]

You could use an Index column and find the date with the next index and subtract the date of the current row from the one of the next row.

For example, in this example:

- I replicated the column as a number or text (just to recreate the column - you might not need this)

- Added an index

- Converted the date to type date (as it was text or number) - in your case you could also just subtract the numbers of course, but thought this was cleaner

- Stored the maximum index number in "variable" Max

- Subtracted the current date from the date with the next index number (step Days) - note the if statement as it will cause an error if not added due to it trying to subtract a date from a value that does not exist for the last row. I made the last row value for DateToNext 0, but could be any number or null. 

- Removed the redundant "helper "columns

let Source = Table.FromRecords({
[DateText = 20240501],
[DateText = 20240502],
[DateText = 20240503],
[DateText = 20240507],
[DateText = 20240508],
[DateText = 20240509],
[DateText = 20240510],
[DateText = 20240513]
}),
Index = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
Date = Table.AddColumn(Index,"Date", each Date.From(Text.From([DateText])),type date),
Max = List.Max(Date[Index]),
Days = Table.AddColumn(Date, "DaysToNext", each if [Index] <Max then Date[DateText]{[Index]+1} - Date[DateText]{[Index]} else 0),
RemovedRedundantCols = Table.RemoveColumns(Days,{"DateText", "Index"})
in
RemovedRedundantCols

Hope this helps