let
Источник = Excel.Workbook(File.Contents(Excel.CurrentWorkbook(){[Name="source"]}[Content]{0}[Column1]), null, true){[Item="табель",Kind="Sheet"]}[Data],
Date = Excel.CurrentWorkbook(){[Name="date"]}[Content]{0}[Column1],
Headers = Table.ToRows(Table.TransformColumns(Table.Transpose(Table.Range(Источник,3,8)),List.Zip({List.Transform({1..8},each "Column"&Text.From(_)),List.Repeat({each Text.From(_)},8)}))),
NewHeaders = List.Generate(()=>[i=0,r=List.RemoveLastN(Headers{i},each _=null),n=0,v=r],each [i]<List.Count(Headers),each [i=[i]+1,r=List.RemoveLastN(Headers{i},each _=null),n=List.PositionOf(r,null,0,(a,b)=>a<>b),v=let prev=[v] in List.Generate(()=>[i=0,s=if r{i}=null or r{i}="Из них по причинам" then prev{i} else r{i}], each [i]<List.Count(r),each [i=[i]+1,s=if r{i}=null or r{i}="В т.ч." then prev{i} else r{i}], each try if [s]="Из них" then null else [s] otherwise null)],each Text.Combine([v],"|")),
AddHeaders = Table.PromoteHeaders(Table.InsertRows(Table.Skip(Источник,11),0,{Record.FromList(NewHeaders,Table.ColumnNames(Источник))})),
FilledDown = Table.FillDown(AddHeaders,{"№ п/п", "Фамилия И.О.", "Табельный номер"}),
ReplaceNull = Table.ReplaceValue(FilledDown,null,"",Replacer.ReplaceValue,Table.ColumnNames(FilledDown)),
Indexed = Table.TransformColumns(Table.AddIndexColumn(ReplaceNull, "Индекс", 1),{{"Индекс", each Number.Mod(_-1, 4)+1, type number}}),
Unpivot = Table.UnpivotOtherColumns(Indexed, {"№ п/п", "Фамилия И.О.", "Табельный номер","Индекс"}, "Атрибут", "Значение"),
ToDate = Table.SplitColumn(Unpivot, "Атрибут", each {try #date(Date.Year(Date),Date.Month(Date),Number.From(Text.AfterDelimiter(_,"|"))) otherwise null}, {"Дата"}),
Group = Table.Group(ToDate, {"№ п/п", "Фамилия И.О.", "Табельный номер", "Дата"}, {{"_",(t)=>let Rows=List.RemoveMatchingItems(List.Split(List.Transform({3,1,4,2},each t{[Индекс=_]}?[Значение]?),2),{{}}) in Table.SelectRows(Table.FromRows(Rows,{"Вид рабочего времени","Табельное время"}), each [Вид рабочего времени]<>null and [Вид рабочего времени]<>""), type table}}),
Filtered = Table.SelectRows(Group, each ([Дата] <> null) and Table.RowCount([_])>0),
Expand = Table.ExpandTableColumn(Filtered, "_", {"Вид рабочего времени", "Табельное время"}),
Result = Table.ReplaceValue(Expand,"",null,Replacer.ReplaceValue,Table.ColumnNames(Expand))
in
Result |