Power Query - finding the last non blank value in a row

[batdog]

I have a table which mainly consists of a series of dates. One of the columns will always have an initial start date. The following 52 columns (nothing to do with the number of weeks in the year) may contain dates subsequent to the initial one. The dates will be populated from left to right (i.e. no gaps between dates). Some rows may not have any further dates after the first one; other rows could have any number of additional dates, but it will vary from row to row.

Example:

Row 1 - Company A | Start Date | Date 1 | Date 2 | Date 3 | Date 4
Row 2 - Company B | Start Date | Date 1
Row 3 - Company B | Start Date
Row 4 - Company B | Start Date | Date 1 | Date 2 | Date 3 | Date 4 | Date 5 | Date 6 | Date 7

What I want to do is find the duration in days between the start date and the most recent date for each row. I know how to find the duration between two dates in Power Query using Duration.Days, but only for fixed columns, so I'm initially trying to add a column at the end which effectively looks to the left in each row to identify the first non-null value (which should be the most recent date entered for that row). I can then perform a duration calculation between this and the initial date.

In the example above I would expect to get back the values Date 4, Date 1, Start Date and Date 7 for rows 1 to 4 respectively.

Any suggestions gratefully received.

 

[JGordon11]

You don't need to add a last non-null column, rather just add a duration column like this

= Table.AddColumn(PreviousStepName, "Duration", each let LastDate = List.Last(List.RemoveNulls(Record.ToList(_))) in Duration.Days(LastDate - [Start Date] ))

change PreviousStepName to the correct step name in your query. This assumes the start column is named "Start Date", if not then change [Start Date] to the correct column name.