Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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. |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.