Forum Discussion

Frequent Visitor

4 years ago

Solved

Conditional list.generate (like a nested if)

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 Sta...

Icon for Community Champion rank

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
]

)"

Related content