Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello together,
I am looking for a function that searches column by column for the last non-empty value and displays it.
Solved! Go to 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(_),{""}))
@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))
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 @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. |