Forum Discussion
Last non Blank Value threw Colums
Hello together,
I am looking for a function that searches column by column for the last non-empty value and displays it.
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(_),{""}))
4 Replies
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(_),{""}))Hi Anonymous
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.
AlexisOlson Can you still tell me how to get the second last value in the same way?
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))
