Hello, my name is Matthew. My goal is to classify a Date+Time range into 3 categories: Day, Maintenance, and Night. The issue is that day shifts run for 10 hours, night shifts run for 10 hours, and maintenance runs for 4 hours and my Date+Time range many times throughout my data list goes over the 24 hour mark. Due to this issue, I am hoping to aggregate rows beneath so that I may have multiple rows with the time split like so:
Currently, a single row looks like this:
I have categorized shifts like this and I know how to make them a relationship in the Model View but I only know how to connect their data when using filters on the presentation view in Power BI. When it comes to using any of the coding sequences I am very new and do not know much. Below is a picture of how the shifts are categorized.
If anyone has any ideas please let me know. My thought process is that I somehow need to nest in a List that meets the conditions mentioend above then aggregate that list into rows and use Fill Down to fill in the rest potentially. I have no idea how to do that after spending several hours on YouTube and message boards attemptign to remedy this. Please let me know if I need to do any further explanations, thank you.
3 ACCEPTED SOLUTIONS
Given this data:
If you want this for results:
See my Shifts.pbix file.
Note it includes three queries
Shift Times
With Source Data set up a bit different from your example:
let
Source = Table.FromColumns({
{#time(0,0,0),#time(7,0,0),#time(17,0,0),#time(21,0,0)},
{#time(6,59,59), #time(16,59,59),#time(20,59,59),#time(23,59,59)},
{"Night","Day","Maintenance","Night"}},
type table[State Start Time=time, State End Time=time, Shift=text]),
//Add list of times by the second
//Times List needs to cover two days to account for shifts overlapping days
#"Times List" = Table.AddColumn(Source,"Times", (r)=>
List.Sort(
List.Combine(
List.TransformMany(
List.DateTimes(
DateTime.From(r[State Start Time]),
Duration.TotalSeconds(r[State End Time]-r[State Start Time]) + 1,
#duration(0,0,0,1)),
each {Date.AddDays(_,1)},
(x,y)=> {x,y})), Order.Ascending)
,type {datetime}),
#"Removed Columns" = Table.RemoveColumns(#"Times List",{"State Start Time", "State End Time"}),
#"Expanded Times" = Table.ExpandListColumn(#"Removed Columns", "Times")
in
#"Expanded Times"
fnShiftDateTimes
(startDt as date)=>
Table.TransformColumns(
ShiftTimes,
{"Times",
each DateTime.From(Number.From(startDt) + Number.From(_)),
type datetime})
Shifts
Main Query
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NcrLCQAhDAXAViRnIXn5yJJWxP7bMC56HWZOCgZYRb3BMzwlqF/0ppbAodUnGb+IRKRaRWM9D1LpvfHeSCmXaj9V+66stQE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"State Start Time" = _t, #"State End Time" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{
{"State Start Time", type nullable datetime}, {"State End Time", type nullable datetime}}),
#"Add allTimes" = Table.AddColumn(#"Changed Type", "allTimes", each
List.DateTimes([State Start Time], Duration.TotalSeconds([State End Time]-[State Start Time])+1,#duration(0,0,0,1)),
type {datetime}),
#"Removed Columns" = Table.RemoveColumns(#"Add allTimes",{"State Start Time", "State End Time"}),
#"Add Shifts" = Table.AddColumn(#"Removed Columns","Shifts", (r)=>
let
#"Expanded allTimes" = Table.Sort(Table.FromList(r[allTimes],Splitter.SplitByNothing(),{"allTimes"}),"allTimes"),
#"Shift Times" = fnShiftDateTimes(Date.From(#"Expanded allTimes"[allTimes]{0})),
#"Join" = Table.NestedJoin(#"Expanded allTimes","allTimes", #"Shift Times","Times","Joined",JoinKind.LeftOuter),
#"Extract Shift" = Table.ExpandTableColumn(Join, "Joined", {"Shift"}, {"Shift"}),
#"Grouped Rows" = Table.Group(#"Extract Shift", {"Shift"}, {
{"Shift Start Time", each List.Min([allTimes]), type nullable datetime},
{"Shift End Time", each List.Max([allTimes]), type nullable datetime}},
GroupKind.Local)
in
#"Grouped Rows"
),
#"Removed Columns1" = Table.RemoveColumns(#"Add Shifts",{"allTimes"}),
#"Expanded Shifts" = Table.ExpandTableColumn(#"Removed Columns1", "Shifts", {"Shift", "Shift Start Time", "Shift End Time"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Shifts",{{"Shift", type text}, {"Shift End Time", type datetime}})
in
#"Changed Type1"
Hi @Anonymous, I'm also adding another version of code 😉 --> this one is faster than my previous.
You can still edit shifts here:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc/JDcAgDETRViLOkRhvWdwKov82YgJWcv16DNBasUpUGawbqZs6rOwr6sbiRCP1vRWpCcnJnCWgVB6OECjdke4YDAj2pmCXK0aZTCeLS4B0thqvtgaRi/wtyu94unzh7TjjNxP+4uVylt4f", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"State Start Time" = _t, #"State End Time" = _t]),
Custom1 = if UseRepeatValue = "Yes" then Table.Repeat(Source, RepeatValue) else Source,
_Shifts = #table(type table[Shift=text, Start=time, End=time],
{ { "Night", #time( 0,0,0), #time( 6,59,59) },
{ "Day", #time( 7,0,0), #time(16,59,59) },
{ "Maintenance", #time(17,0,0), #time(20,59,59) },
{ "Night", #time(21,0,0), #time(23,59,59) } }),
BufferedShifts = List.Buffer(Table.ToRows(_Shifts)),
StepBack = Custom1,
ChangedType_US = Table.TransformColumnTypes(StepBack,{{"State Start Time", type datetime}, {"State End Time", type datetime}}, "en-US"),
Ad_Helper = Table.AddColumn(ChangedType_US, "Helper", each
[ StartDate = Date.From([State Start Time]),
StartTime = Time.From([State Start Time]),
EndDate = Date.From([State End Time]),
EndTime = Time.From([State End Time]),
Dates = List.Dates(StartDate, Duration.TotalDays(EndDate-StartDate)+1, #duration(1,0,0,0)), //Buffer?
DateTimes = List.Combine(List.Transform(Dates, (x)=> {{ x, if x = StartDate then StartTime else #time(0,0,0), if x = EndDate then EndTime else #time(23,59,59) }} )) //separate list for each day
// Buffered = List.Buffer(DateTimes) //not needed
][DateTimes], type record),
Ad_DateTimes = Table.AddColumn(Ad_Helper, "DateTimes", each Table.FromRows(List.Transform(List.Select(List.Combine(List.Transform([Helper], (h)=>
List.Accumulate(
BufferedShifts,
{},
(s,c)=> s & { {c{0}} & {h{0}} & ( if h{0} > List.First([Helper]){0} and h{0} < List.Last([Helper]){0} then {c{1}, c{2}} else
{ (if h{2} >= c{1} then if h{1} <= c{2} then if h{1} >= c{1} then h{1} else c{1} else null else null),
(if c{1} <= h{2} then if c{2} < h{1} then null else if h{2} <= c{2} then h{2} else c{2} else null) } ) }
))), (x)=> List.NonNullCount(x) = 4), (y)=> { y{0}, y{1} & y{2}, y{1} & y{3} }), type table[Shift=text, Start=datetime, End=datetime]), type table),
DateTimes = Table.Combine(Ad_DateTimes[DateTimes]),
GroupedRowsLocal = Table.Group(DateTimes, {"Start", "End"}, {
{"Merged", each #table(type table[Shift=text, Start=datetime, End=datetime], {{Table.SelectColumns(Table.FirstN(_, 1), {"Shift"}){0}[Shift], List.Min([Start]), List.Max([End]) }}), type table }},
GroupKind.Local,
(x,y)=> Number.From( (Time.From(x[End]) <> #time(23,59,59) and Time.From(y[Start]) <> #time(0,0,0)) or Duration.TotalSeconds(y[Start]-x[End]) > 1 ) ),
Merged = Table.Combine(GroupedRowsLocal[Merged])
in
Merged
Hi @Anonymous, I'm also adding another version of code 😉 --> this one is faster than my previous.
You can still edit shifts here:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc/JDcAgDETRViLOkRhvWdwKov82YgJWcv16DNBasUpUGawbqZs6rOwr6sbiRCP1vRWpCcnJnCWgVB6OECjdke4YDAj2pmCXK0aZTCeLS4B0thqvtgaRi/wtyu94unzh7TjjNxP+4uVylt4f", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"State Start Time" = _t, #"State End Time" = _t]),
Custom1 = if UseRepeatValue = "Yes" then Table.Repeat(Source, RepeatValue) else Source,
_Shifts = #table(type table[Shift=text, Start=time, End=time],
{ { "Night", #time( 0,0,0), #time( 6,59,59) },
{ "Day", #time( 7,0,0), #time(16,59,59) },
{ "Maintenance", #time(17,0,0), #time(20,59,59) },
{ "Night", #time(21,0,0), #time(23,59,59) } }),
BufferedShifts = List.Buffer(Table.ToRows(_Shifts)),
StepBack = Custom1,
ChangedType_US = Table.TransformColumnTypes(StepBack,{{"State Start Time", type datetime}, {"State End Time", type datetime}}, "en-US"),
Ad_Helper = Table.AddColumn(ChangedType_US, "Helper", each
[ StartDate = Date.From([State Start Time]),
StartTime = Time.From([State Start Time]),
EndDate = Date.From([State End Time]),
EndTime = Time.From([State End Time]),
Dates = List.Dates(StartDate, Duration.TotalDays(EndDate-StartDate)+1, #duration(1,0,0,0)), //Buffer?
DateTimes = List.Combine(List.Transform(Dates, (x)=> {{ x, if x = StartDate then StartTime else #time(0,0,0), if x = EndDate then EndTime else #time(23,59,59) }} )) //separate list for each day
// Buffered = List.Buffer(DateTimes) //not needed
][DateTimes], type record),
Ad_DateTimes = Table.AddColumn(Ad_Helper, "DateTimes", each Table.FromRows(List.Transform(List.Select(List.Combine(List.Transform([Helper], (h)=>
List.Accumulate(
BufferedShifts,
{},
(s,c)=> s & { {c{0}} & {h{0}} & ( if h{0} > List.First([Helper]){0} and h{0} < List.Last([Helper]){0} then {c{1}, c{2}} else
{ (if h{2} >= c{1} then if h{1} <= c{2} then if h{1} >= c{1} then h{1} else c{1} else null else null),
(if c{1} <= h{2} then if c{2} < h{1} then null else if h{2} <= c{2} then h{2} else c{2} else null) } ) }
))), (x)=> List.NonNullCount(x) = 4), (y)=> { y{0}, y{1} & y{2}, y{1} & y{3} }), type table[Shift=text, Start=datetime, End=datetime]), type table),
DateTimes = Table.Combine(Ad_DateTimes[DateTimes]),
GroupedRowsLocal = Table.Group(DateTimes, {"Start", "End"}, {
{"Merged", each #table(type table[Shift=text, Start=datetime, End=datetime], {{Table.SelectColumns(Table.FirstN(_, 1), {"Shift"}){0}[Shift], List.Min([Start]), List.Max([End]) }}), type table }},
GroupKind.Local,
(x,y)=> Number.From( (Time.From(x[End]) <> #time(23,59,59) and Time.From(y[Start]) <> #time(0,0,0)) or Duration.TotalSeconds(y[Start]-x[End]) > 1 ) ),
Merged = Table.Combine(GroupedRowsLocal[Merged])
in
Merged
Here is a modification of my last query, using the same algorithm but expanded to allow for workspans of 24 hours or longer.
It uses a separate function to calculate the shift times adjusted to account for the dates involved.
It also uses Parameters to be the source for the shift times instead of hard-coding them.
The "ShiftNames" list is hard-coded within the separate function.
The Parameters are named "Day", "Maintenance" and "Night" and contain the Start Time for each shift (in this case 7:00:00, 17:00:00, 21:00)
Function (name: fnShiftDateTimes)
(dttStart as datetime, dttEnd as datetime)=>
let
days = Number.RoundAwayFromZero(Duration.TotalDays(dttEnd-dttStart))+2,
S0 = Date.From(dttStart) & #time(0,0,0),
shiftList = List.Generate(
()=>[S1=Date.From(dttStart) & Day,
S2=Date.From(dttStart) & Maintenance,
S3=Date.From(dttStart) & Night,
idx=0],
each [idx] < days,
each [S1=Date.AddDays(Date.From(dttStart) & Day,[idx]+1),
S2=Date.AddDays(Date.From(dttStart) & Maintenance,[idx]+1),
S3=Date.AddDays(Date.From(dttStart) & Night,[idx]+1),
idx =[idx]+1],
each {[S1],[S2],[S3]}),
shiftNames = {{"Night"} & List.Repeat({"Day","Maintenance","Night"},days)}
in
{{S0} & List.Combine(shiftList)} & shiftNames
Main Query
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc/JDcAgDETRViLOkRhvWdwKov82YgJWcv16DNBasUpUGawbqZs6rOwr6sbiRCP1vRWpCcnJnCWgVB6OECjdke4YDAj2pmCXK0aZTCeLS4B0thqvtgaRi/wtyu94unzh7TjjNxP+4uVylt4f", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"State Start Time" = _t, #"State End Time" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{
{"State Start Time", type nullable datetime}, {"State End Time", type nullable datetime}}),
#"Add Shifts" = Table.AddColumn(#"Changed Type","Shift List",(r)=>
let
//Generate a list of adjusted shift date times (ast) and the corresponding shifts (sh)
//to be used in the List.Generate function
sdd = List.Buffer(fnShiftDateTimes(r[State Start Time], r[State End Time])),
sh = sdd{1},
ast = sdd{0},
splits = List.Generate(
()=> [S=if r[State Start Time] < ast{1} and r[State End Time] > ast{0}
then List.Max({ast{0},r[State Start Time]}) else null,
E = if r[State End Time] > ast{0} and r[State Start Time] < ast{1}
then List.Min({ast{1}, r[State End Time]}) else null,
sft = sh{0},
idx=0],
each [idx] < (List.Count(ast) -1),
each [S=if r[State Start Time] < ast{[idx]+2} and r[State End Time] > ast{[idx]+1}
then List.Max({ast{[idx]+1},r[State Start Time]}) else null,
E = if r[State End Time] > ast{[idx]+1} and r[State Start Time] < ast{[idx]+2}
then List.Min({ast{[idx]+2}, r[State End Time]}) else null,
sft = sh{[idx]+1},
idx=[idx]+1],
each Record.FromList({[S],[E],[sft]},{"Start","End","Shift"}))
in
List.Select(splits, each Record.Field(_,"Start") <> null),
type {[Start=datetime, End=datetime, Shift=text]}
),
#"Expanded Shift List" = Table.ExpandListColumn(#"Add Shifts", "Shift List"),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Shift List",{"State Start Time", "State End Time"}),
#"Expanded Shift List1" = Table.ExpandRecordColumn(#"Removed Columns", "Shift List", {"Start", "End", "Shift"})
in
#"Expanded Shift List1"
Source (courtesy of @dufoq3 )
Results
See also my shifts.pbix file, if you like.
Hi @Anonymous, another solution
@ronrsnfld used times with seconds, but I decided to consider only hours and make some if statements to solve this task. This query should be faster, but it is up to you to test it. I spent few hours to make this query, but it was good exercise.
Before
After
You can edit shifts here:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc/JDcAgDETRViLOkRhvWdwKov82YgJWcv16DNBasUpUGawbqZs6rOwr6sbiRCP1vRWpCcnJnCWgVB6OECjdke4YDAj2pmCXK0aZTCeLS4B0thqvtgaRi/wtyu94unzh7TjjNxP+4uVylt4f", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"State Start Time" = _t, #"State End Time" = _t]),
_Shifts = #table(type table[Shift=text, Start=time, End=time],
{ { "Night", #time( 0,0,0), #time( 6,59,59) },
{ "Day", #time( 7,0,0), #time(16,59,59) },
{ "Maintenance", #time(17,0,0), #time(20,59,59) },
{ "Night", #time(21,0,0), #time(23,59,59) } }),
Ad_Hours = Table.AddColumn(_Shifts, "Hours", each
[ start = Time.StartOfHour([Start]),
end = Time.EndOfHour([End]),
a = List.Times(start, Duration.TotalHours(end-start), #duration(0,1,0,0))
][a], type list),
ExpandedHours = Table.ExpandListColumn(Ad_Hours, "Hours"),
TransformHours = Table.TransformColumns(ExpandedHours, {{"Hours", Time.Hour, Int64.Type}}),
StepBack = Source,
ChangedType_US = Table.TransformColumnTypes(StepBack,{{"State Start Time", type datetime}, {"State End Time", type datetime}}, "en-US"),
AddedIndex = Table.AddIndexColumn(ChangedType_US, "Index", 0, 1, Int64.Type),
Ad_HourStart = Table.AddColumn(AddedIndex, "HourStart", each Time.Hour([State Start Time]), Int64.Type),
Ad_HourEnd = Table.AddColumn(Ad_HourStart, "HourEnd", each Time.Hour([State End Time]), Int64.Type),
Ad_Datetimes = Table.AddColumn(Ad_HourEnd, "DateTimes", each
[ start = Date.From([State Start Time]) & #time([HourStart], 0, 0),
end = Date.From([State End Time]) & #time([HourEnd], 0, 0),
a = List.DateTimes(start, Duration.TotalHours(end-start)+1, #duration(0,1,0,0)),
b = try {1} & List.Repeat({0}, List.Count(a)-2) & {1} otherwise {1},
c = Table.FromColumns({a, b}, type table[DateTimes=datetime, StartEnd=Int64.Type])
][c], type table ),
ExpandedDateTimes = Table.ExpandTableColumn(Ad_Datetimes, "DateTimes", {"DateTimes", "StartEnd"}),
Ad_WorkedHours = Table.AddColumn(ExpandedDateTimes, "WorkedHours", each Time.Hour([DateTimes])),
MergedQueriesByHours = Table.NestedJoin(Ad_WorkedHours, {"WorkedHours"}, TransformHours, {"Hours"}, "Shifts", JoinKind.LeftOuter),
ExpandedShifts = Table.ExpandTableColumn(MergedQueriesByHours, "Shifts", {"Shift", "Start", "End"}, {"Shift", "Shift.Start", "Shift.End"}),
Ad_WorkedDate = Table.AddColumn(ExpandedShifts, "WorkedDate", each DateTime.Date([DateTimes]), type date),
Ad_StateStartDate = Table.AddColumn(Ad_WorkedDate, "State Start Date", each DateTime.Date([State Start Time]), type date),
Ad_StateEndDate = Table.AddColumn(Ad_StateStartDate, "State End Date", each DateTime.Date([State End Time]), type date),
fn_Transform =
(myTable as table)=>
[
// _Detail = GroupedRowsGlobal{[Index=1]}[All],
_Detail = myTable,
_Sort = Table.Sort(_Detail, {{"DateTimes", Order.Ascending}}),
_Buffered = Table.Buffer(_Sort),
_GroupedRows = Table.Group(_Buffered, {"Shift"}, {{"All", each _, type table},
{ "Start", each if List.First([StartEnd]) = 1 and List.First([WorkedDate]) = List.First([State Start Date]) then List.Max({ List.First([State Start Time]), List.First([WorkedDate]) & List.First([Shift.Start]) }) else List.First([WorkedDate]) & List.First([Shift.Start]), type datetime},
{ "End", each if List.Last([StartEnd]) = 1 and List.Last([WorkedDate]) = List.Last([State End Date]) then List.Min({ List.Last([State End Time]), List.Last([WorkedDate]) & List.Last([Shift.End]) }) else List.Last([WorkedDate]) & List.Last([Shift.End]), type datetime} }, GroupKind.Local),
_RemovedColumns = Table.RemoveColumns(_GroupedRows,{"All"})
][_RemovedColumns],
GroupedRowsGlobal = Table.Group(Ad_StateEndDate, {"Index"}, {{"All", each _, type table}, {"Transformed", fn_Transform, type table}}),
CombinedTransformed = Table.Combine(GroupedRowsGlobal[Transformed])
in
CombinedTransformed
I have ensured the datetime values are correct and to my knowledge I do not believe this is the error. Here is a set of data I am working with:
Given this data:
If you want this for results:
See my Shifts.pbix file.
Note it includes three queries
Shift Times
With Source Data set up a bit different from your example:
let
Source = Table.FromColumns({
{#time(0,0,0),#time(7,0,0),#time(17,0,0),#time(21,0,0)},
{#time(6,59,59), #time(16,59,59),#time(20,59,59),#time(23,59,59)},
{"Night","Day","Maintenance","Night"}},
type table[State Start Time=time, State End Time=time, Shift=text]),
//Add list of times by the second
//Times List needs to cover two days to account for shifts overlapping days
#"Times List" = Table.AddColumn(Source,"Times", (r)=>
List.Sort(
List.Combine(
List.TransformMany(
List.DateTimes(
DateTime.From(r[State Start Time]),
Duration.TotalSeconds(r[State End Time]-r[State Start Time]) + 1,
#duration(0,0,0,1)),
each {Date.AddDays(_,1)},
(x,y)=> {x,y})), Order.Ascending)
,type {datetime}),
#"Removed Columns" = Table.RemoveColumns(#"Times List",{"State Start Time", "State End Time"}),
#"Expanded Times" = Table.ExpandListColumn(#"Removed Columns", "Times")
in
#"Expanded Times"
fnShiftDateTimes
(startDt as date)=>
Table.TransformColumns(
ShiftTimes,
{"Times",
each DateTime.From(Number.From(startDt) + Number.From(_)),
type datetime})
Shifts
Main Query
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NcrLCQAhDAXAViRnIXn5yJJWxP7bMC56HWZOCgZYRb3BMzwlqF/0ppbAodUnGb+IRKRaRWM9D1LpvfHeSCmXaj9V+66stQE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"State Start Time" = _t, #"State End Time" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{
{"State Start Time", type nullable datetime}, {"State End Time", type nullable datetime}}),
#"Add allTimes" = Table.AddColumn(#"Changed Type", "allTimes", each
List.DateTimes([State Start Time], Duration.TotalSeconds([State End Time]-[State Start Time])+1,#duration(0,0,0,1)),
type {datetime}),
#"Removed Columns" = Table.RemoveColumns(#"Add allTimes",{"State Start Time", "State End Time"}),
#"Add Shifts" = Table.AddColumn(#"Removed Columns","Shifts", (r)=>
let
#"Expanded allTimes" = Table.Sort(Table.FromList(r[allTimes],Splitter.SplitByNothing(),{"allTimes"}),"allTimes"),
#"Shift Times" = fnShiftDateTimes(Date.From(#"Expanded allTimes"[allTimes]{0})),
#"Join" = Table.NestedJoin(#"Expanded allTimes","allTimes", #"Shift Times","Times","Joined",JoinKind.LeftOuter),
#"Extract Shift" = Table.ExpandTableColumn(Join, "Joined", {"Shift"}, {"Shift"}),
#"Grouped Rows" = Table.Group(#"Extract Shift", {"Shift"}, {
{"Shift Start Time", each List.Min([allTimes]), type nullable datetime},
{"Shift End Time", each List.Max([allTimes]), type nullable datetime}},
GroupKind.Local)
in
#"Grouped Rows"
),
#"Removed Columns1" = Table.RemoveColumns(#"Add Shifts",{"allTimes"}),
#"Expanded Shifts" = Table.ExpandTableColumn(#"Removed Columns1", "Shifts", {"Shift", "Shift Start Time", "Shift End Time"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Shifts",{{"Shift", type text}, {"Shift End Time", type datetime}})
in
#"Changed Type1"
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - January 2025
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
Fabric Community Update - January 2025
Find out what's new and trending in the Fabric community.
