Hier der zweite Teil:
KE = (eingabeTabelle as table) as table =>
let
result = Table.FromRecords(
List.Transform(
List.Positions(Table.ToRecords(eingabeTabelle)),
(i) =>
let
r = Table.ToRecords(eingabeTabelle){i},
previous = if i > 0 then Table.ToRecords(eingabeTabelle){i - 1} else null
in
List.Accumulate(
{
each if _[Mitarbeiter1] = "U" and _[Mitarbeiter4] = "Frei"
then Record.TransformFields(_, {{"Mitarbeiter4", each "L"}}) else _,
each if _[Mitarbeiter1] = "U" and List.Contains({"HK","HK + LK"}, _[Mitarbeiter2])
then Record.TransformFields(_, {{"Mitarbeiter3", each "HL"}}) else _,
each if _[Mitarbeiter1] = "U" and _[Mitarbeiter3] = "HL"
then Record.TransformFields(_, {{"Mitarbeiter2", each "HK"}}) else _,
each if _[Mitarbeiter1] = "U" and _[Mitarbeiter4] = "LK"
then Record.TransformFields(_, {{"Mitarbeiter2", each "HK + LK"}}) else _,
each if _[Mitarbeiter1] = "U" and (try Text.Upper(Text.Trim(r[GV][Wt])) otherwise null) = "DI"
then _ & [Mitarbeiter2 = "HL", Mitarbeiter3 = "HK"] else _,
each if _[Mitarbeiter1] = "U" and (Text.Start(Date.DayOfWeekName(r[Datum], "de-DE"), 2)) = "Do"
then Record.TransformFields(_, {{"Mitarbeiter2", each "HL"}, {"Mitarbeiter3", each "HK"}}) else _,
each if _[Mitarbeiter4] = "U"
then Record.TransformFields(_, {{"Mitarbeiter2", each "L"}}) else _,
each if _[Mitarbeiter4] = "U" and _[Mitarbeiter3] = "Frei"
then Record.TransformFields(_, {{"Mitarbeiter3", each "HK"}}) else _,
each if _[Mitarbeiter4] = "U" and _[Mitarbeiter1] = "Frei"
then Record.TransformFields(_, {{"Mitarbeiter1", each "HK"}}) else _,
each if _[Mitarbeiter4] = "U" and _[Mitarbeiter3] = "W"
then Record.TransformFields(_, {{"Mitarbeiter4", each "L"}}) else _,
each if previous <> null
and Text.Start(Date.DayOfWeekName(previous[Datum], "de-DE"), 2) = "Fr"
and previous[Mitarbeiter4] = "U"
and Text.Start(Date.DayOfWeekName(r[Datum], "de-DE"), 2) = "Sa"
and r[Mitarbeiter4] = "HL"
then Record.TransformFields(_, {{"Mitarbeiter2", each "HL"}, {"Mitarbeiter4", each "X"}}) else _,
each if previous <> null
and Text.Start(Date.DayOfWeekName(previous[Datum], "de-DE"), 2) = "Fr"
and previous[Mitarbeiter4] = "U"
and Text.Start(Date.DayOfWeekName(r[Datum], "de-DE"), 2) = "Sa"
and r[Mitarbeiter5] = "HL"
then Record.TransformFields(_, {{"Mitarbeiter3", each "HL"}, {"Mitarbeiter5", each "X"}}) else _,
each if previous <> null
and Text.Start(Date.DayOfWeekName(previous[Datum], "de-DE"), 2) = "Fr"
and previous[Mitarbeiter4] = "U"
and Text.Start(Date.DayOfWeekName(r[Datum], "de-DE"), 2) = "Sa"
and r[Mitarbeiter5] = "HL"
then Record.TransformFields(_, {{"Mitarbeiter3", each "HL"}, {"Mitarbeiter5", each "X"}})
else if Date.Month(r[Datum]) = 1
and Date.DayOfWeek(r[Datum], Day.Monday) = 0
and Date.Day(r[Datum]) <= 7
and r[Mitarbeiter4] = "U" or (Date.Month(r[Datum]) = 1
and Date.DayOfWeek(r[Datum], Day.Saturday) = 0
and Date.Day(r[Datum]) <= 7
and r[Mitarbeiter5] = "HL")
then Record.TransformFields(_, {{"Mitarbeiter3", each "HL"}, {"Mitarbeiter5", each "X"}}) else _,
each if _[Mitarbeiter3] = "W" and _[Mitarbeiter1] = "HK"
then Record.TransformFields(_, {{"Mitarbeiter2", each "HL"}}) else _,
each if _[Mitarbeiter3] = "W" and _[Mitarbeiter1] = "HL"
then Record.TransformFields(_, {{"Mitarbeiter2", each "HK"}}) else _,
each if _[Mitarbeiter3] = "W" and _[Mitarbeiter1] = "Frei"
then Record.TransformFields(_, {{"Mitarbeiter1", each "HK"}}) else _,
each if _[Mitarbeiter3] = "W" and _[Mitarbeiter4] = "LK"
then Record.TransformFields(_, {{"Mitarbeiter2", each "HK + LK"}}) else _,
each if (try Text.Upper(Text.Trim(r[GV][Wt])) otherwise null) = "FR" and previous <> null and Text.Upper
(Text.Trim(previous[GV][Wt])) = "DO" and previous[Mitarbeiter3] = "W"
then Record.TransformFields(_, {{"Mitarbeiter3", each "Frei"}, {"Mitarbeiter4", each "L"}}) else _,
each if _[Mitarbeiter3] = "Frei" and _[Mitarbeiter1] = "HK"
then Record.TransformFields(_, {{"Mitarbeiter2", each "HL"}}) else _,
each if _[Mitarbeiter3] = "Frei" and _[Mitarbeiter1] = "HL" and _[Mitarbeiter2] = "L"
then Record.TransformFields(_, {{"Mitarbeiter2", each "HK"}}) else _,
each if _[Mitarbeiter3] = "U" and _[Mitarbeiter4] = "Frei"
then Record.TransformFields(_, {{"Mitarbeiter4", each "L"}}) else _,
each if _[Mitarbeiter3] = "U" and _[Mitarbeiter1] = "Frei"
then Record.TransformFields(_, {{"Mitarbeiter1", each "HK"}}) else _,
each if _[Mitarbeiter3] = "U" and _[Mitarbeiter2] = "Frei"
then Record.TransformFields(_, {{"Mitarbeiter2", each "HK"}}) else _,
each if _[Mitarbeiter3] = "U" and _[Mitarbeiter4] = "L"
then Record.TransformFields(_, {{"Mitarbeiter2", each "HL"}}) else _,
each if _[Mitarbeiter3] = "U" and _[Mitarbeiter1] = "HL"
then Record.TransformFields(_, {{"Mitarbeiter2", each "HK"}}) else _,
each if _[Mitarbeiter3] = "U" and _[Mitarbeiter4] = "LK"
then Record.TransformFields(_, {{"Mitarbeiter4", each "L"}}) else _,
each if _[Mitarbeiter3] = "U" and _[Mitarbeiter2] = "HK + LK"
then Record.TransformFields(_, {{"Mitarbeiter2", each "HL"}}) else _,
each if _[Mitarbeiter3] = "U" and (Date.DayOfWeek(DateTime.LocalNow())) = 5
then Record.TransformFields(_, {{"Mitarbeiter1", each "HL"}}) else _,
each if _[Mitarbeiter2] = "U" and List.Contains({"Frei","LK"}, _[Mitarbeiter4])
then Record.TransformFields(_, {{"Mitarbeiter4", each "L"}}) else _,
each if _[Mitarbeiter2] = "U" and (Text.Start(Date.DayOfWeekName(r[Datum], "de-DE"), 2)) = "Fr"
then Record.TransformFields(_, {{"Mitarbeiter1", each "HK"}, {"Mitarbeiter3", each "HL"}}) else _,
each if _[Mitarbeiter2] = "U" and _[Mitarbeiter1] = "Frei"
then Record.TransformFields(_, {{"Mitarbeiter1", each "HL"}}) else _,
each if _[Mitarbeiter2] = "U" and _[Mitarbeiter3] = "Frei"
then Record.TransformFields(_, {{"Mitarbeiter3", each "HK"}}) else _,
each if _[Mitarbeiter2] = "U" and (Text.Start(Date.DayOfWeekName(r[Datum], "de-DE"), 2)) = "Mi"
then Record.TransformFields(_, {{"Mitarbeiter1", each "HK"}, {"Mitarbeiter3", each "HL"}}) else _,
each if _[Mitarbeiter2] = "U" and (Text.Start(Date.DayOfWeekName(r[Datum], "de-DE"), 2)) = "Do"
then Record.TransformFields(_, {{"Mitarbeiter1", each "HL"}, {"Mitarbeiter3", each "HK"}}) else _,
each if _[Mitarbeiter1] = "K" and (Text.Start(Date.DayOfWeekName(r[Datum], "de-DE"), 2)) = "Mo"
then Record.TransformFields(_, {{"Mitarbeiter2", each "HL"}, {"Mitarbeiter3", each "HK"},
{"Mitarbeiter4", each "L"}}) else _,
each if _[Mitarbeiter1] = "K" and (Text.Start(Date.DayOfWeekName(r[Datum], "de-DE"), 2)) = "Di"
then Record.TransformFields(_, {{"Mitarbeiter2", each "HK + LK"}, {"Mitarbeiter3", each "HL"},
{"Mitarbeiter4", each "LK"}}) else _,
each if _[Mitarbeiter1] = "K" and (Text.Start(Date.DayOfWeekName(r[Datum], "de-DE"), 2)) = "Mi"
then Record.TransformFields(_, {{"Mitarbeiter2", each "HL"}, {"Mitarbeiter3", each "HK"},
{"Mitarbeiter4", each "L"}}) else _,
each if _[Mitarbeiter1] = "K" and (Text.Start(Date.DayOfWeekName(r[Datum], "de-DE"), 2)) = "Do"
then Record.TransformFields(_, {{"Mitarbeiter2", each "HK"}, {"Mitarbeiter3", each "HL"},
{"Mitarbeiter4", each "L"}}) else _,
each if _[Mitarbeiter1] = "K" and (Text.Start(Date.DayOfWeekName(r[Datum], "de-DE"), 2)) = "Fr"
then Record.TransformFields(_, {{"Mitarbeiter2", each "HL"}, {"Mitarbeiter3", each "HK"},
{"Mitarbeiter4", each "L"}}) else _,
each if _[Mitarbeiter1] = "K" and _[Mitarbeiter4] = "Frei" and (Text.Start(Date.DayOfWeekName(r[Datum],
"de-DE"), 2)) = "Fr"
then Record.TransformFields(_, {{"Mitarbeiter2", each "HL"}, {"Mitarbeiter3", each "HK"},
{"Mitarbeiter4", each "L"}}) else _,
each if _[Mitarbeiter1] = "Frei" and (Text.Start(Date.DayOfWeekName(r[Datum], "de-DE"), 2)) = "Mi"
then Record.TransformFields(_, {{"Mitarbeiter2", each "HK + LK"}, {"Mitarbeiter3", each "HL"}}) else _
},
r,
(state, rule) => rule(state))))
in
result,
CS = (lst as list) as list =>
let
n = List.Buffer(List.Transform(lst, each Number.From(_)))
in
List.Generate(() => [i = 0, s = 0],each [i] < List.Count(n),each [i = [i] + 1, s = [s] + n{[i]}],each [s]),
IW = (d as date) as number => let in
Number.IntegerDivide(Duration.Days(d -(Date.AddDays(#date(Date.Year(Date.AddDays(d, 3 -(Date.DayOfWeek(d, Day.Monday)))),
1, 4), -(Date.DayOfWeek(#date(Date.Year(Date.AddDays(d, 3 - (Date.DayOfWeek(d, Day.Monday)))), 1, 4), Day.Monday))))),
7) + 1,
R = () as table =>
let
Source = KE(Pl()),
B = Table.AddIndexColumn(Source, "Ix", 0, 1, Int64.Type),
U4 = List.Buffer(List.Transform(B[Mitarbeiter4], each if _ = "U" then 1 else 0)),
C4 = CS(U4),
recs = Table.ToRecords(B),
U3PosList = List.Buffer(List.Accumulate(List.Positions(recs),[prevDate = null as nullable date, prevU = false, pos = -1,
out = {}],(s, i) =>
let
r = recs{i},
d = r[Datum],
isU = (r[Mitarbeiter3] = "U"),
prevDate = s[prevDate],
isConsecutiveWorkday = prevDate <> null and (d = Date.AddDays(prevDate, 1) or (Date.DayOfWeek(prevDate,
Day.Monday) = 4 and d = Date.AddDays(prevDate, 3))),
newPos = if isU then if s[prevU] and isConsecutiveWorkday then s[pos] + 1 else 0 else null,
nextPos = if newPos = null then -1 else newPos
in
[prevDate = d,
prevU = isU,
pos = nextPos,
out = s[out] & { newPos }])[out]),
B2 = Table.AddColumn(B, "U3Pos", each U3PosList{[Ix]}, Int64.Type),
R1 = Table.AddColumn(B2, "M1", each
let
i = [Ix],
z4 = C4{i}
in
if [Mitarbeiter3] = "U" then if Number.Mod([U3Pos], 2) = 0 then "HL" else "HK"
else if [Mitarbeiter4] = "U" then if Number.Mod(z4, 2) = 0 then "HL" else "HK" else [Mitarbeiter1], type text),
R2 = Table.AddColumn(R1, "M2", each if [Mitarbeiter3] = "U" then if Number.Mod([U3Pos], 2) = 0 then "HK" else "HL" else
[Mitarbeiter2], type text),
R3 = Table.AddColumn(R2, "M3", each
let
i = [Ix],
z4 = C4{i}
in
if [Mitarbeiter4] = "U" then if Number.Mod(z4, 2) = 0 then "HK" else "HL" else [Mitarbeiter3], type text),
Ergebnis = Table.RenameColumns(Table.RemoveColumns(R3, {"Ix", "U3Pos", "Mitarbeiter1", "Mitarbeiter2", "Mitarbeiter3"}),
{{"M1", "Mitarbeiter1"}, {"M2", "Mitarbeiter2"}, {"M3", "Mitarbeiter3"}})
in
Ergebnis,
Ty = Table.TransformColumnTypes((Table.ExpandRecordColumn(R(),"GV",{"J","Mo","Wt"},{"J","Mo","Wt"})),{{"Datum", type date}}),
KW = Table.AddColumn(Ty, "KW", each IW([Datum]), Int64.Type),
MS = List.Buffer(List.Select(Table.ColumnNames(KW), each Text.StartsWith(_, "Mitarbeiter"))),
Mi = List.Sort(MS,(a,b) =>Number.From(Text.AfterDelimiter(a,"Mitarbeiter")) -Number.From(Text.AfterDelimiter(b,"Mitarbeiter"))),
Re = List.RemoveItems(Table.ColumnNames(KW), ({"J","KW","Mo","Datum","Wt"}) & Mi),
Or = Table.ReorderColumns(KW, ({"J","KW","Mo","Datum","Wt"}) & Mi & Re),
MN = Excel.CurrentWorkbook(){[Name="tblMitarbeiter"]}[Content][Name],
PC = List.Min({List.Count(Mi), List.Count(MN)}),
RP = List.Zip({List.FirstN(Mi, PC), List.FirstN(MN, PC)})
in
Table.Sort((Table.RenameColumns(Or, RP, MissingField.Ignore)), {{"Datum", Order.Ascending}})
