Conditional list.generate (like a nested if)

[Porkey]

Hi

(First time posting, sorry if making any faux pas)

Im trying to generate a list of start times for slots in a Rota, where it checks to see if the Rota name has changed, the data only has the Start of the rota, so it needs to add to the start time in increments from the slot duration. 

I cant quite see what ive missed

"= List.Generate(
()=>[
CR = 0,
NR = 1,
EX = 2,
CRRotaName = #"Rota_Data (2)"{CR}[ROTA],
NRRotaName = #"Rota_Data (2)"{NR}[ROTA],
CRTime = #"Rota_Data (2)"{CR}[TimeStart],
NRTime = #"Rota_Data (2)"{NR}[TimeStart]
],

each [CR] < List.Max(#"Rota_Data (2)"[Index]),

each
if(
[CRRotaName] = [NRRotaName]
)
then [
CR = [CR] +1,
NR = [NR] +1,
EX = [EX] +1,
CRRotaName = #"Rota_Data (2)"{CR}[ROTA],
NRRotaName = #"Rota_Data (2)"{NR}[ROTA],
CRTime = [CRTime] + #"Rota_Data (2)"{CR}[Actual Duration],
NRTime = #"Rota_Data (2)"{NR}[TimeStart]
]

else [
CR = [CR] +1, //Not sure if these should be incremental increases or each
NR = [NR] +1,
EX = [EX] + 1,
CRRotaName = #"Rota_Data (2)"{NR}[ROTA],
NRRotaName = #"Rota_Data (2)"{EX}[ROTA],
CRTime = [NRTime],
NRTime = #"Rota_Data (2)"{EX}[TimeStart]
]

)"

My data looks like this

ROTA	DateStart	TimeStart	RotaSlotType	Duration	Actual Position	Index	Actual Duration
L2 - Virtual - Benoy,01/12/2021,08:30	01/12/2021	08:30:00	Image	30	1	1	0.00:30:00
L2 - Virtual - Benoy,01/12/2021,08:30	01/12/2021	08:30:00	Physio admin	30	2	2	0.00:30:00
L2 - Virtual - Benoy,01/12/2021,15:30	01/12/2021	15:30:00	Telephone	15	1	3	0.00:15:00
L2 - Virtual - Benoy,01/12/2021,15:30	01/12/2021	15:30:00	Telephone	15	2	4	0.00:15:00
L2 - Virtual - Benoy,01/12/2021,15:30	01/12/2021	15:30:00	Paper	60	3	5	0.01:00:00
L2 - Virtual - Benoy,01/12/2021,15:30	01/12/2021	15:30:00	Physio admin	30	4	6	0.00:30:00

and I would like it to look like this (the TimeStart column is the change)

ROTA	DateStart	TimeStart	RotaSlotType	Duration	Actual Position	Index	Actual Duration
L2 - Virtual - Benoy,01/12/2021,08:30	01/12/2021	08:30:00	Image	30	1	1	0.00:30:00
L2 - Virtual - Benoy,01/12/2021,08:30	01/12/2021	09:00:00	Physio admin	30	2	2	0.00:30:00
L2 - Virtual - Benoy,01/12/2021,15:30	01/12/2021	15:30:00	Telephone	15	1	3	0.00:15:00
L2 - Virtual - Benoy,01/12/2021,15:30	01/12/2021	15:45:00	Telephone	15	2	4	0.00:15:00
L2 - Virtual - Benoy,01/12/2021,15:30	01/12/2021	16:00:00	Paper	60	3	5	0.01:00:00
L2 - Virtual - Benoy,01/12/2021,15:30	01/12/2021	17:00:00	Physio admin	30	4	6	0.00:30:00

[Porkey · Accepted solution]

HI HotChilli

I have gotten it to work now, thnak you for looking.

The solution was to change the if condition to measure against a number, so I changed it to look at the actual position column based on if it was <>1 my code is below for anyone interested, I did have to change other parts of the code to reflect that change also.

"= List.Generate(
()=>[
CR = 0,
NR = 1,
EX = -1,
CRRotaName = #"Rota_Data (2)"{CR}[ROTA],
NRRotaName = #"Rota_Data (2)"{NR}[ROTA],
CRTime = #"Rota_Data (2)"{CR}[TimeStart],
NRTime = #"Rota_Data (2)"{NR}[TimeStart],
ActualPosition = 0
],

each [CR] < List.Max(#"Rota_Data (2)"[Index]),

each
if
[ActualPosition] <>1

then [
CR = [CR] +1,
NR = [NR] +1,
EX = [EX] +1,
CRRotaName = #"Rota_Data (2)"{CR}[ROTA],
NRRotaName = #"Rota_Data (2)"{NR}[ROTA],
CRTime = [CRTime] + #"Rota_Data (2)"{EX}[Actual Duration],
NRTime = #"Rota_Data (2)"{NR}[TimeStart],
ActualPosition = [ActualPosition]+1
]

else [
CR = [CR] +1, //Not sure if these should be incremental increases or each
NR = [NR] +1,
EX = [EX] +1,
CRRotaName = #"Rota_Data (2)"{CR}[ROTA],
NRRotaName = #"Rota_Data (2)"{NR}[ROTA],
CRTime = [NRTime],
NRTime = #"Rota_Data (2)"{NR}[TimeStart],
ActualPosition = [ActualPosition]+1
]

)"

[HotChilli · Accepted solution]

I've got a custom column with the duration (TotDuration) and then another custom column with the recalculated starttime of each session:

Table.AddColumn(#"Changed Type", "TotDuration", each List.Sum(Table.SelectRows(
                #"Changed Type",
                (row) =>
                    row[ROTA] = [ROTA]
                    and
                    row[Index] < [Index] 
            )
            [Actual Duration]))

and

if [TotDuration] = null then [TimeStart] else [TimeStart] + [TotDuration]

let me know how you get on.

[Porkey]

Thats alot more elegant, thank you so much, I will have a look at the row bits in more detail as ive not seen those before.

[HotChilli]

Can you post that as data please (not pictures) and show the desired result with an explanation of how you get there? 

Also, why are the data types of the columns not set correctly?

[Porkey · Accepted solution]

HI HotChilli

I have gotten it to work now, thnak you for looking.

The solution was to change the if condition to measure against a number, so I changed it to look at the actual position column based on if it was <>1 my code is below for anyone interested, I did have to change other parts of the code to reflect that change also.

"= List.Generate(
()=>[
CR = 0,
NR = 1,
EX = -1,
CRRotaName = #"Rota_Data (2)"{CR}[ROTA],
NRRotaName = #"Rota_Data (2)"{NR}[ROTA],
CRTime = #"Rota_Data (2)"{CR}[TimeStart],
NRTime = #"Rota_Data (2)"{NR}[TimeStart],
ActualPosition = 0
],

each [CR] < List.Max(#"Rota_Data (2)"[Index]),

each
if
[ActualPosition] <>1

then [
CR = [CR] +1,
NR = [NR] +1,
EX = [EX] +1,
CRRotaName = #"Rota_Data (2)"{CR}[ROTA],
NRRotaName = #"Rota_Data (2)"{NR}[ROTA],
CRTime = [CRTime] + #"Rota_Data (2)"{EX}[Actual Duration],
NRTime = #"Rota_Data (2)"{NR}[TimeStart],
ActualPosition = [ActualPosition]+1
]

else [
CR = [CR] +1, //Not sure if these should be incremental increases or each
NR = [NR] +1,
EX = [EX] +1,
CRRotaName = #"Rota_Data (2)"{CR}[ROTA],
NRRotaName = #"Rota_Data (2)"{NR}[ROTA],
CRTime = [NRTime],
NRTime = #"Rota_Data (2)"{NR}[TimeStart],
ActualPosition = [ActualPosition]+1
]

)"