Forum Discussion
James_Galis1
2 years agoHelper II
Fill Down on conditions
Hello I have a table with with null values where i wish to populate the data based on conditions and i am not sure if possible or how to go about it Note: The earning code ( TTBV ) is a...
- 2 years ago
See file for example.
let Source = Excel.CurrentWorkbook(){[Name="pay_test"]}[Content], changeType = Table.TransformColumnTypes(Source,{{"Emp. #", type text}, {"Employment Type", type text}, {"Type", type text}, {"Visit ID", Int64.Type}, {"Employee Cost Centre", type any}, {"Cost Centre Description", type any}, {"Visit start date", type text}, {"Visit end date", type text}, {"Visit start time", type time}, {"Visit end time", type time}, {"Service type", type text}, {"Earning Code", type text}, {"Quantity", type number}, {"Premium Quantity", type any}, {"Units", type text}, {"Override rate", Int64.Type}, {"Pay amount", Int64.Type}}), priorStep = Table.SelectRows(changeType, each ([Service type] <> "Reimburse KMs Travelled")), eligile_index = let buffer_date = List.Buffer(priorStep[Visit start date]), buffer_start = List.Buffer(priorStep[Visit start time]), buffer_end = List.Buffer(priorStep[Visit end time]) in List.Generate( () => {0}, // current, index each _{0} < List.Count(buffer_start) - 1, each { _{0} + 1}, each let x = (buffer_start{_{0}+1} ?? buffer_start{_{0}+2}) - buffer_end{_{0}} in if buffer_start{_{0}} <> null then Duration.TotalMinutes(x) < 60 and Duration.TotalMinutes(x) >= 0 else null ), add_eligible_index = Table.Buffer( Table.FromColumns( Table.ToColumns(priorStep)&{eligile_index}, Table.ColumnNames(priorStep)&{"Eligible"} ) ), /* #"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 0, 1, Int64.Type), #"Added Eligible" = let buffer = #"Added Index"[Visit start time] in Table.AddColumn(#"Added Index", "Eligible", each if [Visit end time] <> null then Duration.TotalMinutes( (buffer{[Index]+1} ?? buffer{[Index]+2}) - [Visit end time]) < 60 else null), */ priorStep2 =Table.FillDown(add_eligible_index,{"Visit start date", "Visit end date"}), null_index = let buffer_emp = List.Buffer(priorStep2[#"Emp. #"]), // needed for employee id condition buffer_date = List.Buffer(priorStep2[Visit start date]), buffer_start = List.Buffer(priorStep2[Visit start time]), buffer_code = List.Buffer(priorStep2[Earning Code]), buffer_eligible = List.Buffer(priorStep2[Eligible]), buffer_end = List.Buffer(priorStep2[Visit end time]) in List.Generate( () => {0,0,{},buffer_date{0}}, // current row, null index, non null list, current date each _{0} < List.Count(buffer_end), each { _{0}+1, if buffer_date{_{0}+1} <> _{3} or buffer_emp{_{0}+1} <> buffer_emp{_{0}} then List.Count(_{2}) else if buffer_code{_{0}} = "TTBV" then _{1} + 1 else _{1}, if try buffer_code{_{0}} <> "TTBV" and (buffer_eligible{_{0}} = true and buffer_eligible{_{0}} <> null) otherwise false then List.Buffer(_{2} & {_{0}}) else List.Buffer(_{2}), buffer_date{_{0}+1} }, // create list of all non null values. create index to select from non null values. increase index when null value is found or take last non null value when visit start date changes so you can pick the first non null value after the date change. each if buffer_code{_{0}} <> "TTBV" then null else try buffer_end{_{2}{_{1}}} otherwise null ), add_null_index = Table.Buffer( Table.FromColumns( Table.ToColumns(priorStep2)&{null_index}, Table.ColumnNames(priorStep2)&{"replace_start"} ) ), #"Changed Type" = Table.TransformColumnTypes(add_null_index,{{"replace_start", type time}}), #"Replaced Value" = Table.ReplaceValue(#"Changed Type",each [Visit start time],each [replace_start] ?? [Visit start time],(origV,oldV,newV) as time => newV,{"Visit start time"}), #"Added Custom" = Table.AddColumn(#"Replaced Value", "end_replace", each [Visit end time] ?? Time.From( Number.Mod( Number.From([Visit start time]) + Number.RoundDown([Quantity],0)/(24*60) + Number.Mod([Quantity],1)*100/(24*60*60) ,1) ), type time), #"Replaced Value1" = Table.ReplaceValue(#"Added Custom",each [Visit end time],each [end_replace],(origV,oldV,newV) as time => newV,{"Visit end time"}) in #"Replaced Value1"
James_Galis1
2 years agoHelper II
Hi
Dates for reimburse km is not necessary
Correct, the end times should only be taken from CSW
spinfuzer
2 years agoSolution Sage
See file for example.
let
Source = Excel.CurrentWorkbook(){[Name="pay_test"]}[Content],
changeType = Table.TransformColumnTypes(Source,{{"Emp. #", type text}, {"Employment Type", type text}, {"Type", type text}, {"Visit ID", Int64.Type}, {"Employee Cost Centre", type any}, {"Cost Centre Description", type any}, {"Visit start date", type text}, {"Visit end date", type text}, {"Visit start time", type time}, {"Visit end time", type time}, {"Service type", type text}, {"Earning Code", type text}, {"Quantity", type number}, {"Premium Quantity", type any}, {"Units", type text}, {"Override rate", Int64.Type}, {"Pay amount", Int64.Type}}),
priorStep = Table.SelectRows(changeType, each ([Service type] <> "Reimburse KMs Travelled")),
eligile_index =
let
buffer_date = List.Buffer(priorStep[Visit start date]),
buffer_start = List.Buffer(priorStep[Visit start time]),
buffer_end = List.Buffer(priorStep[Visit end time])
in
List.Generate(
() => {0}, // current, index
each _{0} < List.Count(buffer_start) - 1,
each { _{0} + 1},
each
let x = (buffer_start{_{0}+1} ?? buffer_start{_{0}+2}) - buffer_end{_{0}}
in if buffer_start{_{0}} <> null
then Duration.TotalMinutes(x) < 60 and Duration.TotalMinutes(x) >= 0
else null
),
add_eligible_index =
Table.Buffer(
Table.FromColumns(
Table.ToColumns(priorStep)&{eligile_index},
Table.ColumnNames(priorStep)&{"Eligible"}
)
),
/* #"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 0, 1, Int64.Type),
#"Added Eligible" = let buffer = #"Added Index"[Visit start time] in Table.AddColumn(#"Added Index", "Eligible", each if [Visit end time] <> null
then Duration.TotalMinutes(
(buffer{[Index]+1}
?? buffer{[Index]+2}) - [Visit end time]) < 60 else null),
*/
priorStep2 =Table.FillDown(add_eligible_index,{"Visit start date", "Visit end date"}),
null_index =
let
buffer_emp = List.Buffer(priorStep2[#"Emp. #"]), // needed for employee id condition
buffer_date = List.Buffer(priorStep2[Visit start date]),
buffer_start = List.Buffer(priorStep2[Visit start time]),
buffer_code = List.Buffer(priorStep2[Earning Code]),
buffer_eligible = List.Buffer(priorStep2[Eligible]),
buffer_end = List.Buffer(priorStep2[Visit end time])
in
List.Generate(
() => {0,0,{},buffer_date{0}}, // current row, null index, non null list, current date
each _{0} < List.Count(buffer_end),
each
{
_{0}+1,
if
buffer_date{_{0}+1} <> _{3}
or buffer_emp{_{0}+1} <> buffer_emp{_{0}}
then List.Count(_{2})
else if buffer_code{_{0}} = "TTBV" then _{1} + 1 else _{1},
if
try buffer_code{_{0}} <> "TTBV"
and (buffer_eligible{_{0}} = true and buffer_eligible{_{0}} <> null)
otherwise false
then List.Buffer(_{2} & {_{0}})
else List.Buffer(_{2}),
buffer_date{_{0}+1}
}, // create list of all non null values. create index to select from non null values. increase index when null value is found or take last non null value when visit start date changes so you can pick the first non null value after the date change.
each if buffer_code{_{0}} <> "TTBV" then null else try buffer_end{_{2}{_{1}}} otherwise null
),
add_null_index =
Table.Buffer(
Table.FromColumns(
Table.ToColumns(priorStep2)&{null_index},
Table.ColumnNames(priorStep2)&{"replace_start"}
)
),
#"Changed Type" = Table.TransformColumnTypes(add_null_index,{{"replace_start", type time}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type",each [Visit start time],each [replace_start] ?? [Visit start time],(origV,oldV,newV) as time => newV,{"Visit start time"}),
#"Added Custom" = Table.AddColumn(#"Replaced Value", "end_replace", each [Visit end time] ??
Time.From(
Number.Mod(
Number.From([Visit start time])
+ Number.RoundDown([Quantity],0)/(24*60)
+ Number.Mod([Quantity],1)*100/(24*60*60)
,1)
), type time),
#"Replaced Value1" = Table.ReplaceValue(#"Added Custom",each [Visit end time],each [end_replace],(origV,oldV,newV) as time => newV,{"Visit end time"})
in
#"Replaced Value1"
- James_Galis12 years agoHelper IIHi Spinfuzer I have checked and double checked the data. There was one slight adjustment to your code where i entered <=60 to the below line. That seemed to fix some gaps in the output where the exact distance in travel = 60 then Duration.TotalMinutes(x) <= 60 and Duration.TotalMinutes(x) >= 0 Im pleased with the output and marking this as complete. However, if possible, without creating too much effort on your part can we re-introduce the "Reimburse KM travelled" as the last step? These values do not need any timestamps included just needed for the final output file. If it mucks up the code, then i can work with it as is Sensation effort and i really appreciate the assistance
- spinfuzer2 years agoSolution Sage
Do the rows need to be in the same order? If not you could just do something like
combine = Table.Combine( { #"Replaced Value1", Table.SelectRows(changeType, each ([Service type] = "Reimburse KMs Travelled")) } ) in combineIf the rows DO matter, you need to add an INDEX before the filtering, then do the above and then sort by the index column.