Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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 |
Solved! Go to 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
]
)"
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.
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.
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.
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?
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
]
)"