Clever Geek Handbook

How can I execute COALESCE on a force request?

I have a table that has quarter values ​​and I need to add a new column that gives me the last non-zero value in the last quarter. for example

ID | Project  | Q1   | Q2   | Q3   | Q4   | Current Quarter Value  
1  | bal bal  | 23   | 32   | 34   | null | 34  
2  | cuz cuz  | 43   | 56   | null | null | 56
+3


source to share


2 answers


There are several formulas that you can use when adding a custom column to a table (accessible from the Transform ribbon tab). Here's one:

if [Q4] <> null then [Q4] else if [Q3] <> null then [Q3] else if [Q2] <> null then [Q2] else [Q1]



If you don't want to write as many if statements, you can add columns to the list and filter out null values:

List.Last(List.Select({[Q1], [Q2], [Q3], [Q4]}, each _ <> null))

+7


source


to find the rightmost value of a range of strings that is not null, you have two methods.

  • if you know there are no blank values ​​between spaces, then count all unfilled spaces and use that value to offset from the start of the range

    =OFFSET(C2,0,COUNTA(C2:F2)-1)

enter image description here



  1. if there can be embedded zeros, use a search in the given range, modified to find all nonblank characters, and again using the given range as the range of results

    =LOOKUP(2,1/(C2:F2<>""),C2:F2)

enter image description here

-2


source






More articles:


All Articles