Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 applied when there is a gap between visits attended to on the same day.
Condition1.
The visit start time ( null values ) should contain the end time of the previous visit when there is a gap between visits
Condition2.
The visit end time ( null values ) should be the addition of the new start time based on condition 1 , plus the value in quantity field ( not sure if converting to duration output is the correct option for the quantity column )
Below is the expected output when manually entering these values.
Appreciate the assistance
Solved! Go to Solution.
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"
edited and updated M code. I do not understand the >= 60 minute condition.
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.FillDown(changeType,{"Visit start date", "Visit end date"}),
null_index =
let
buffer_date = List.Buffer(priorStep[Visit start date]),
buffer_time = List.Buffer(priorStep[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_time),
each
{
_{0}+1,
if buffer_date{_{0}+1} <> _{3} then List.Count(_{2}) + Byte.From(buffer_time{_{0}} <> null)
else if buffer_time{_{0}} <> null then _{1}
else _{1} + 1,
if buffer_time{_{0}} <> null 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_time{_{0}} <> null then null else buffer_time{_{2}{_{1}}}
),
add_null_index =
Table.Buffer(
Table.FromColumns(
Table.ToColumns(priorStep)&{null_index},
Table.ColumnNames(priorStep)&{"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"
Regarding the 60 minute condition, i'll use the example below taken from the attached file
There are 3 visits on this particular day
Visit 1: 9:00 - 10:00
Visit 2: 11:30 - 12:30
Visit 3: 13:00 - 15:00
From the above scenario there are 2 gaps in time
Visit 1 to Visit 2 = 90 minutes
Visit 2 to Visit 3 = 30 minutes
The TTBV is a condition that allows a worker to paid whilst travelling to their next job but only if the gap between the next job is less than 60 minutes so in the example above the TTBV condition should trigger between visit 2 and visit 3 which would mean the start time for the TTBV would be:
12:30pm
Hope that makes sense
Do we fill in the dates for service type "Reimburse KMs Travelled"? Can I only take end time's from Service Type = "CSW"?
Hi
Dates for reimburse km is not necessary
Correct, the end times should only be taken from CSW
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"
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
combine
If the rows DO matter, you need to add an INDEX before the filtering, then do the above and then sort by the index column.
edit: duplicate post
edit: duplicate post
Hi Spinfuzer
Thank you for providing the sample.
Have some feedback upon checking the data.
The last value has an input of 5:00pm, the expected output should have 2:15pm ( red )
The blue colored values are correct as a comparison
15/11/2023 | 15/11/2023 | 8:00:00 AM | 10:00:00 AM | CSW | CSW | 2 | Hours | 0 | 0 | ||
15/11/2023 | 15/11/2023 | 10:30:00 AM | 1:00:00 PM | CSW | CSW | 2.5 | Hours | 0 | 0 | ||
15/11/2023 | 15/11/2023 | 10:00:00 AM | Time Travel BV | TTBV | 5.67 | Amount | 0 | 0 | 10:00:00 AM | ||
15/11/2023 | 15/11/2023 | 1:15:00 PM | 2:15:00 PM | CSW | CSW | 1 | Hours | 0 | 0 | ||
15/11/2023 | 15/11/2023 | 1:00:00 PM | Time Travel BV | TTBV | 12.28 | Amount | 0 | 0 | 1:00:00 PM | ||
15/11/2023 | 15/11/2023 | 2:30:00 PM | 5:00:00 PM | CSW | CSW | 2.5 | Hours | 0 | 0 | ||
15/11/2023 | 15/11/2023 | 5:00:00 PM | Time Travel BV | TTBV | 9.02 | Amount | 0 | 0 | 5:00:00 PM |
There is also a rule that was not mentioned in original scope.
Rule: If the travel time =>60 minutes the condition does not apply and would apply to the next condition and off the table below the input should be 1:45:00 PM. Apologies for not including
15/11/2023 | 15/11/2023 | 8:00:00 AM | 10:00:00 AM | CSW | CSW | 2 | Hours | |
15/11/2023 | 15/11/2023 | 11:45:00 AM | 1:45:00 PM | CSW | CSW | 2 | Hours | |
15/11/2023 | 15/11/2023 | 2:15:00 PM | 3:45:00 PM | CSW | CSW | 1.5 | Hours | |
15/11/2023 | 15/11/2023 | 10:00:00 AM | Time Travel BV | TTBV | 15.98 | Amount |
I have included a link to a copy of the payfile if it helps.
Many thanks again
An simple question in the context of DAX. Let's wait and see what you'll get in PQ.
Expertise = List.Accumulate( {Days as from Today}, {Skills and Knowledge}, (Current, Everyday) => Current & Day.LeanAndPractise(Everyday) ) |
10:04:97 AM is not a valid timestamp.
I might have to look at an alternate solution for my second condition.
However, if the first condition can acheived that would be a great help
What does your Quantity column represent? Minutes and Seconds or Minutes and Minute Fractions? 10:04:97 AM is not a valid timestamp.
Hi
The quantity column represents "minutes and seconds".
Adding sample table with desired outputs in red
17/11/2023 | 17/11/2023 | 8:00:00 AM | 10:00:00 AM | CS1 | CSW | 2 |
17/11/2023 | 17/11/2023 | 10:15:00 AM | 11:15:00 AM | CS1 | CSW | 1 |
17/11/2023 | 17/11/2023 | 10:00:00 AM | 10:10:43 AM | TTBV | TTBV | 10.43 |
17/11/2023 | 17/11/2023 | 11:30:00 AM | 1:30:00 PM | CS1 | CSW | 2 |
17/11/2023 | 17/11/2023 | 11:15:00 AM | 11:38:18 AM | TTBV | TTBV | 23.18 |
17/11/2023 | 17/11/2023 | 1:45:00 PM | 3:15:00 PM | CS1 | CSW | 1.5 |
17/11/2023 | 17/11/2023 | 1:30:00 PM | 1:40:10 PM | TTBV | TTBV | 10.1 |
17/11/2023 | 17/11/2023 | 3:15:00 PM | 4:45:00 PM | CS1 | CSW | 1.5 |
13/11/2023 | 13/11/2023 | 9:00:00 AM | 10:00:00 AM | CS1 | CSW | 1 |
13/11/2023 | 13/11/2023 | 10:30:00 AM | 12:30:00 PM | CS1 | CSW | 2 |
13/11/2023 | 13/11/2023 | 10:00:00 AM | 10:04:97 AM | TTBV | TTBV | 4.97 |
Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.
If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.