let
from = Excel.Workbook(File.Contents(Excel.CurrentWorkbook(){[Name="Параметры"]}[Content]{0}[Путь к исходным данным]), null, true),
filtr = Table.SelectRows(from, each ([Kind] = "Sheet")),
A1content = Table.AddColumn(filtr, "Содержимое A1", each [Data][Column1]{0}),
filtr2 = Table.SelectRows(A1content, each Text.Contains([Содержимое A1], "Специальность - ")),
select = Table.SelectColumns(filtr2,{"Name", "Data"}),
transf = Table.TransformColumns(select,{{"Data", each Table.PromoteHeaders(Table.SelectRows(_, each ([Column2] <> null and [Column2] <> " " and [Column2] <> "академический отпуск" and [Column2] <> "ДПИ" and [Column2] <> "ДПИ " and [Column2] <> "ЖИВ")))}}),
list = List.Select(List.Distinct(List.Combine(List.Transform(transf[Data],each Table.ColumnNames(_)))), each not Text.Contains(_,"Column")),
expand = Table.ExpandTableColumn(transf, "Data", list),
rename = Table.RenameColumns(expand,{{"Спец-ть", "Специальность"}, {"Name", "Группа"}}),
typ = Table.TransformColumnTypes(rename,{{"Дата рождения", type date}}),
age = Table.AddColumn(typ, "Возраст", each (Duration.TotalDays(Date.From(DateTime.LocalNow()) - [Дата рождения])/ 365), type number),
round = Table.TransformColumns(age,{{"Возраст", Number.RoundDown, Int64.Type}}),
to = Table.SelectColumns(round,{"Ф.И.О.", "Курс", "Стастус", "Специальность", "Дата рождения", "Примечание", "Группа", "Возраст"})
in
to |