Finding First/Last non null value in a row in Power Query M Language

[Vijay_A_Verma]

Use Case - There are scenarios where you will be required to find the first/last non null value in a row. For example, to answer the questions:

1. What was the first sales amount done by a sales person? - This means that you will have to find first non-null from 2017 to 2022. For example, for Greg this is 688.

2. What was the last sales amount done by a sales person - This means that you will have to find last non-null from 2017 to 2022. Hence for Laura, this is 753.

Of course, if more years are added in the beginning or end, the formula should support the flexibility of Power Query i.e. if more columns are added, the result should include those columns as well.

Solution -

1. To find the first non-null, following formula can be used

= List.First(List.RemoveNulls(List.RemoveFirstN(Record.ToList(_),1)))

2. To find the last non-null, following formula can be used

= List.Last(List.RemoveNulls(List.RemoveFirstN(Record.ToList(_),1)))

Explanation of the formulas - 

Let's look into working of above formulas

1. Record.ToList(_) will take a record and generate a list containing its values. Hence for first record, following list will be generated

Greg
null
null
null
688
707
752

2. List.RemoveFirstN will remove first entry from the list i.e. Sales person's name will be removed. Hence, Greg will be removed from above list and following list will be remaining

null
null
null
688
707
752

3. List.RemoveNulls will remove all nulls from above list and we will be left with below list

688
707
752

4. List.First will pick up first element. Hence, it will pick up 688.

5. List.Last will pick up last element. Hence, it will pick up 752.

The excel file containing above working can be downloaded from https://1drv.ms/x/s!Akd5y6ruJhvhuUEdwFk7GWFHnFWv?e=GU0XHR 

[talk2frank]

How woudl this work from only selected columns rather than the entire record?

[viorelcazacu]

If the records were on Columns instead of Rows, how would the formula be?

[Vijay_A_Verma]

You would need to replace Record.ToList(_) with list name.

Hence, if there is a column named ColumnA and previous step was say #"Previous Step", then replace Record.ToList(_) with #"Previous Step"[ColumnA]

[Sairam]

In the first case (Row) it will continue to be a table in the second case(Column) it will be a scalar value

[alphab3at]

hi, thanks for this.

Is there a way also to get the Column Name of the First/Last Non Null Values in a row?

In you example, for first row
FirstNonNull: 2020, since 688 is in 2020
LastNonNull: 2022, since 752 is in 2022