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]),eachif([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 eachNR = [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 ROTADateStartTimeStartRotaSlotTypeDurationActual PositionIndexActual DurationL2 - Virtual - Benoy,01/12/2021,08:3001/12/202108:30:00Image30110.00:30:00L2 - Virtual - Benoy,01/12/2021,08:3001/12/202108:30:00Physio admin30220.00:30:00L2 - Virtual - Benoy,01/12/2021,15:3001/12/202115:30:00Telephone15130.00:15:00L2 - Virtual - Benoy,01/12/2021,15:3001/12/202115:30:00Telephone15240.00:15:00L2 - Virtual - Benoy,01/12/2021,15:3001/12/202115:30:00Paper60350.01:00:00L2 - Virtual - Benoy,01/12/2021,15:3001/12/202115:30:00Physio admin30460.00:30:00 and I would like it to look like this (the TimeStart column is the change) ROTADateStartTimeStartRotaSlotTypeDurationActual PositionIndexActual DurationL2 - Virtual - Benoy,01/12/2021,08:3001/12/202108:30:00Image30110.00:30:00L2 - Virtual - Benoy,01/12/2021,08:3001/12/202109:00:00Physio admin30220.00:30:00L2 - Virtual - Benoy,01/12/2021,15:3001/12/202115:30:00Telephone15130.00:15:00L2 - Virtual - Benoy,01/12/2021,15:3001/12/202115:45:00Telephone15240.00:15:00L2 - Virtual - Benoy,01/12/2021,15:3001/12/202116:00:00Paper60350.01:00:00L2 - Virtual - Benoy,01/12/2021,15:3001/12/202117:00:00Physio admin30460.00:30:00

Frequent Visitor

4 years ago

Solved

Conditional list.generate (like a nested if)

(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

Need Help

Porkey
4 years ago
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

4 years ago

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.

4 Replies

HotChilli
Community Champion
4 years ago
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
  Frequent Visitor
  4 years ago
  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
Community Champion
4 years ago
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
  Frequent Visitor
  4 years ago
  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
  ]
  
  )"

Forum Discussion

Conditional list.generate (like a nested if)

4 Replies

Related content

Nested List.generate

Nested IF

List.Generate, make behave like Accumulate or XL REDUCE

Nested IF AND Statement/Conditional Column

Issue with List.Generate