if then depending on column with marked cell

[CornelisV]

Dear all,

I'm going to calculate industry process data and I'm trying to define a process phase based on the next table:

Time	Phase 1	Valve	Phase 2
01:00		0
02:00		0
03:00		1
04:00		0
05:00	Heating	0
06:00		0
07:00		0
08:00		0
09:00		1	Cooling
10:00		0

Phase 1 is already successfully assigned with 'Heating'.

The problem is assigning Phase 2: it must be marked with 'Cooling' if Valve = 1.

However, Add Custom Phase 2 in Power M Query will not provide the solution:

 if [Valve] = 1 then "Cooling" else null

This is not desired because, the column Phase 2 will mark two times 'Cooling' in the row where Valve = 1.

It must be only assigned in the column 'Phase 2' after 'Heating' in column Phase 1 has been passed. 

So, do you know a solution, preventing that 'Cooling' will not appear at time = 3:00 but only at time = 09:00 hour since Heating at 05:00 has been passed?

Thank you and best regards,

Cornelis

[rohit1991 · Accepted solution]

Hi @CornelisV 
Here’s how you can do it:

Add an Index Column to track row positions in Power Query. This helps to reference previous rows.

Create a Conditional Column (Phase 2) with a custom formula using the Table.AddColumn function and a List to check if "Heating" has appeared previously in the Phase 1 column.

let
Source = <YourSourceTable>,
AddIndex = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
AddPhase2 = Table.AddColumn(AddIndex, "Phase 2", each if [Valve] = 1 and
List.Contains(List.FirstN(AddIndex[Phase 1], [Index] - 1), "Heating")
then "Cooling" else null),
RemoveIndex = Table.RemoveColumns(AddPhase2, {"Index"})
in
RemoveIndex

[DataNinja777 · Accepted solution]

Hi @CornelisV ,

There are multiple ways to achieve your desired output. One approach is to use the Fill Down function in Power Query to fill down "Heating" in the Phase 1 column. Then, if a row in the Valve column has a value of 1, "Cooling" will be displayed in Phase 2 as shown below: 

I have attached an example pbix file for your reference.

Best regards,

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula in DAX works

Column = if(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Time]<=EARLIER(Data[Time])&&Data[Phase 1]="Heating"))>0,if(Data[Valve]=1,"Cooling",BLANK()))

Hope this helps.

[CornelisV]

Hello @Ashish_Mathur ,

The DAX approach is quite different in comparison to M Query approach from @DataNinja777, but the outcome is the same

Column = if(CALCULATE(COUNTROWS(Sheet1),FILTER(Sheet1,Sheet1[Time]<=EARLIER(Sheet1[Time])&&Sheet1[Phase 1]="Heating"))>0,if(Sheet1[Valve]=1,"Cooling",BLANK()))

Very interesting input and a good learning point for me.

Thank you!

Best regards,

Cornelis

[Ashish_Mathur]

Thank you.

[DataNinja777 · Accepted solution]

Hi @CornelisV ,

There are multiple ways to achieve your desired output. One approach is to use the Fill Down function in Power Query to fill down "Heating" in the Phase 1 column. Then, if a row in the Valve column has a value of 1, "Cooling" will be displayed in Phase 2 as shown below: 

I have attached an example pbix file for your reference.

Best regards,

[CornelisV]

Hella @DataNinja777 ,

Thank you for your valueable contribution. This is exact what I'm looking for and the steps in Power M Query is not difficult to apply. Copy column, fill down and then apply a custom calculation 'Add column' is the fastest way. 

Have a great day,

Cornelis

[rohit1991 · Accepted solution]

Hi @CornelisV 
Here’s how you can do it:

Add an Index Column to track row positions in Power Query. This helps to reference previous rows.

Create a Conditional Column (Phase 2) with a custom formula using the Table.AddColumn function and a List to check if "Heating" has appeared previously in the Phase 1 column.

let
Source = <YourSourceTable>,
AddIndex = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
AddPhase2 = Table.AddColumn(AddIndex, "Phase 2", each if [Valve] = 1 and
List.Contains(List.FirstN(AddIndex[Phase 1], [Index] - 1), "Heating")
then "Cooling" else null),
RemoveIndex = Table.RemoveColumns(AddPhase2, {"Index"})
in
RemoveIndex

[CornelisV]

Hi @rohit1991 ,

Also a good solution and a different approach in comparison to @Ashish_Mathur  and @DataNinja777 . Especially the 

List.Contains(List.FirstN(#"Added Index"[Phase 1]

is a new learning point for me.

Thank yoiu for your contribution.

Best regards,

Cornelis