Last non Blank Value threw Colums

[Daniel28-DH]

Hello together,
I am looking for a function that searches column by column for the last non-empty value and displays it.

[Daniel28-DH]

@AlexisOlson Can you still tell me how to get the second last value in the same way?

[AlexisOlson]

You could remove the last item and then take the new last item.

List.Last(List.RemoveLastN(List.RemoveItems(Record.ToList(_),{""}),1))

Or you could take the first of the last two items:

List.First(List.LastN(List.RemoveItems(Record.ToList(_),{""}),2))

[AlexisOlson · Accepted solution]

You can also do this in Power Query by converting each row into a list, removing blanks, and taking the last item.

Stick this into the Custom Column box:

List.Last(List.RemoveItems(Record.ToList(_),{""}))

[AlB]

Hi @Daniel28-DH 

You could do it with a series of nested IFs, or  a SWITCH() starting by the rightmost column. Something like:

NewCol =
SWITCH (
    TRUE (),
    NOT ISBLANK ( Table1[S6-Neu] ), Table1[S6-Neu],
    NOT ISBLANK ( Table1[S5-Neu] ), Table1[S5-Neu],
    NOT ISBLANK ( Table1[S4-Neu] ), Table1[S4-Neu],
    NOT ISBLANK ( Table1[S3-Neu] ), Table1[S3-Neu],
    NOT ISBLANK ( Table1[S2-Neu] ), Table1[S2-Neu],
    NOT ISBLANK ( Table1[S1-Neu] ), Table1[S1-Neu]
)

but this is not really scalable. I would change the structure of your table by unpivoting the columns

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.