PowerQuery - Creating consolidation column referencing one of two columns based on third column

Copper Contributor


I'm trying to create a consolidation column where the value is either the row value of Column 1 or the row value of Column 2 based on the row value in Column 3. 


The data might look like this:

Col1     Col2    Col3     Consolidation

10         5          1          10

15         10        2           10

7           12        3           12


In Excel this would be IF(COL3=1,Col1,COL2).  I've tried doing an earlier function, like:

This results in a an error that the EARLIER/EARLIEST refers to an earlier row context which doesn't exist. 

Does anyone have any suggestions for how to effectuate this?

2 Replies


if [Col3]=1 then [Col1] else [Col2]


You can add a conditional column as shown in the attached file.


Do we speak about Power Query or about DAX (data model). EARLIER() and EARLIEST() are in the latest. In your case (if we are in DAX) you don't need them since you are in the same row context. You have no filters, thus no multiple row contexts, thus no outer row contexts which EARLIER() could return.

In general these functions are outdated, you may use VAR to fix row context.

In your case you need nothing of above, simple IF works both in Power Query and DAX.