let
Source = Excel.Workbook(File.Contents("C:\abc\Расписание учителей 0112.xlsx"), null, true),
#"2_Sheet" = Source{[Item="2",Kind="Sheet"]}[Data],
#"Removed Blank Rows" = Table.SelectRows(#"2_Sheet", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
#"Removed Top Rows" = Table.Skip(#"Removed Blank Rows",2),
#"Transposed Table" = Table.Transpose(#"Removed Top Rows"),
#"Filled Down" = Table.FillDown(#"Transposed Table",{"Column1", "Column2"}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Filled Down", {{"Column2", type text}}, "ru-RU"),{"Column1", "Column2", "Column3"},Combiner.CombineTextByDelimiter("=", QuoteStyle.None),"Сведено"),
#"Promoted Headers" = Table.PromoteHeaders(#"Merged Columns", [PromoteAllScalars=true]),
#"Promoted Headers1" = Table.PromoteHeaders(#"Promoted Headers", [PromoteAllScalars=true]),
#"Replaced Value" = Table.ReplaceValue(#"Promoted Headers1",null,0,Replacer.ReplaceValue,Table.ColumnNames( #"Promoted Headers1")),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Replaced Value", {"Учитель=="}, "Атрибут", "Значение"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Учитель==", Splitter.SplitTextByDelimiter("=", QuoteStyle.Csv), {"Учитель==.1", "Учитель==.2", "Учитель==.3"}),
Pivoted = Table.Pivot(#"Split Column by Delimiter", List.Distinct(#"Split Column by Delimiter"[#"Учитель==.3"]), "Учитель==.3", "Значение"),
Неделя = Table.FromRecords({[a = "Понедельник", b = 1, 1="Пн"], [a = "Вторник", b = 2, 1="Вт"],[a = "Среда", b = 3, 1="Ср"],[a = "Четверг", b = 4, 1="Чт"],[a = "Пятница", b = 5, 1="Пт"],[a = "Суббота", b = 6, 1="Сб"],[a = "Воскресенье", b = 7, 1="Вс"]}),
#"Merged Queries" = Table.Join(Pivoted,{"Учитель==.1"},Неделя,{"a"},JoinKind.LeftOuter),
#"Changed Type" = Table.TransformColumnTypes(#"Merged Queries",{{"Учитель==.2", Int64.Type}, {"Атрибут", type text}, {"Класс", type text}, {"Каб.", type text}, {"b", Int64.Type}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Атрибут", Order.Ascending}, {"b", Order.Ascending}, {"Учитель==.2", Order.Ascending}}),
Removed = Table.SelectColumns(#"Sorted Rows",{"b", "Учитель==.2", "Класс", "Каб.", "Атрибут"}),
Source2 = Excel.Workbook(File.Contents("C:\abc\Расписание классов 0112.xlsx"), null, true),
#"Added Custom" = Table.AddColumn(Source2, "D", each Table.PromoteHeaders(Table.Skip([Data],3)))[[D]],
#"Added Custom1" = Table.AddColumn(#"Added Custom", "a", each let
#"Added Index" = Table.AddIndexColumn([D], "Индекс"),
#"Added Conditional Column" = Table.AddColumn(#"Added Index", "a", each if [Column2] = 1 then [Индекс] else null),
#"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Column1", "Column2","a"}),
#"Grouped Rows" = Table.Group(#"Filled Down", {"Column1", "Column2","a"}, {{"b", each Table.AddIndexColumn(_, "Индекс2"), type table}})[[b]],
#"Expanded {0}" = Table.ExpandTableColumn(#"Grouped Rows", "b", Table.ColumnNames(#"Grouped Rows"[b]{0})),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Expanded {0}", {{"Column2", type text}, {"Индекс2", type text}}, "ru-RU"),{"Column1", "Column2", "Индекс2"},Combiner.CombineTextByDelimiter("=", QuoteStyle.None),"Сведено"),
#"Removed Columns" = Table.RemoveColumns(#"Merged Columns",{"Индекс", "a"}),
#"Reordered Columns" =Table.ReorderColumns(#"Removed Columns",{"Сведено"}&List.RemoveLastN( Table.ColumnNames(#"Removed Columns"),1)),
#"Transposed Table" = Table.Transpose(#"Reordered Columns"),
#"Filled Down1" = Table.FillDown(#"Transposed Table",{"Column1"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Filled Down1", [PromoteAllScalars=true]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"День=#=0", "День=#=1"}, "Атрибут", "Значение"),
#"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[#"День=#=1"]), "День=#=1", "Значение"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Pivoted Column", "Атрибут", Splitter.SplitTextByDelimiter("=", QuoteStyle.Csv), {"Атрибут.1", "Атрибут.2", "Атрибут.3"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Атрибут.1", type text}, {"Атрибут.2", type text}, {"Атрибут.3", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Атрибут.2] <> "#"))
in
#"Filtered Rows")[[a]],
Expanded = Table.ExpandTableColumn(#"Added Custom1", "a", {"День=#=0", "Атрибут.1", "Атрибут.2", "Предмет", "Каб."}),
Custom1 = Table.Join(Expanded,{"Атрибут.1"},Неделя,{"1"},JoinKind.LeftOuter),
#"Changed Type1" = Table.TransformColumnTypes(Custom1,{{"Атрибут.2", Int64.Type}, {"b", Int64.Type}}),
#"Merged Queries1" = Table.NestedJoin(Removed,{"b", "Учитель==.2", "Класс","Каб."},#"Changed Type1",{"b", "Атрибут.2","День=#=0","Каб."},"Expanded",JoinKind.LeftOuter),
#"Expanded {0}" = Table.ExpandTableColumn(#"Merged Queries1", "Expanded", {"Предмет"})
in
#"Expanded {0}"
|