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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
RICHARDCHIANG1
Frequent Visitor

Create new column based on values from 2 rows

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? 

 

RICHARDCHIANG1_0-1715697951417.png

 

2 ACCEPTED SOLUTIONS
ferryv
Resolver I
Resolver I

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

 

ferryv_0-1715701083598.png

Hope this helps

View solution in original post

v-jingzhan-msft
Community Support
Community Support

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"

 

vjingzhanmsft_0-1715757379601.png

 

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

View solution in original post

7 REPLIES 7
RICHARDCHIANG1
Frequent Visitor

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! 

 

DateNameDaysToNext
20240501John1
20240502John5
20240503PeterNA
20240507John4
20240508Jen2
20240510Jen7
20240517JenNA

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:

ferryv_0-1716199322976.png

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:

 

ferryv_1-1716199713918.png

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

 

RICHARDCHIANG1
Frequent Visitor

thank you all

slorin
Super User
Super User

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
Community Support
Community Support

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"

 

vjingzhanmsft_0-1715757379601.png

 

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

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
Resolver I
Resolver I

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

 

ferryv_0-1715701083598.png

Hope this helps

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors