如何在电源查询中执行COALESCE？

I have a table that has values for quarters and I need to add a new column that gives me the last not null value as 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  

2 个解决方案

#1

There are a couple formulas you can use when adding a custom column to the 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]

如果[Q4] <> null则为[Q4]否则如果[Q3] <> null则为[Q3]否则如果[Q2] <> null则为[Q2]否则为[Q1]

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

如果您不想编写这么多if语句，可以将列添加到列表中并过滤掉空值：

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

List.Last（List.Select（{[Q1]，[Q2]，[Q3]，[Q4]}，每个_ <> null））

#2

to find the rightmost value of a single row range that is not null you have two methods

要找到非空的单行范围的最右边的值，您有两种方法

1. if you know there are no blank values in between, then count all non blanks and use this value to offset from the range origin

   如果您知道它们之间没有空白值，则计算所有非空白值并使用此值偏移范围原点

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

2. if there might be interspersed nulls, use lookup in a given range modified to find all non blanks, and using again the given range as the result range

   如果可能存在散布的空值，则在给定范围内使用查找以修改所有非空白，并再次使用给定范围作为结果范围

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