let
Источник = Excel.Workbook(File.Contents("G:\AIS_RU\KA_Voronezh\Fakturierung-Produktivierung\GJ 2018\Factura\Projects.xlsm"), null, true),
Source = Источник{[Item="Проекты",Kind="Table"]}[Data],
#"commonHeader" = Table.ColumnNames(Source),
#"newHeader" = {"Month","SL","Model","Manager","Admin", "PO", "Balance"},
#"staticHeader" = List.Range(commonHeader, 0, 11),
#"varHeaderTable" = Table.TransformColumns(Table.AddIndexColumn(Table.FromColumns({List.Range(commonHeader, 11)}, {"head"}), "idx", 0), {"idx", each Number.IntegerDivide(_, 7)}),
#"builder" = List.Transform(List.Distinct(varHeaderTable[idx]), (id) =>
let
subIdTable = Table.SelectRows(varHeaderTable, each [idx] = id),
neededCols = Table.SelectColumns(Source, staticHeader & subIdTable[head])
in
Table.RenameColumns(neededCols, List.Zip({subIdTable[head], newHeader}))
),
#"Proverka" = Table.Combine(builder),
#"Измененный тип" = Table.TransformColumnTypes(Proverka,{{"Start Date", type date}, {"Finish Date", type date}, {"Month", type date}}),
#"Переупорядоченные столбцы" = Table.ReorderColumns(#"Измененный тип",{"WBS", "Order", "Status", "Start Date", "Finish Date", "Closing Date", "Debitor", "Contact Person", "Currency", "P/N", "Description", "Month", "SL", "Model", "Manager", "Admin", "Balance", "PO"}),
#"Объединенные запросы" = Table.NestedJoin(#"Переупорядоченные столбцы",{"Description", "Month"},Factura,{"Description", "Month"},"Factura",JoinKind.LeftOuter),
#"Развернутый элемент Factura" = Table.ExpandTableColumn(#"Объединенные запросы", "Factura", {"A/S", "Total", "Travels, RUB", "Comments"}, {"A/S", "Total", "Travels, RUB", "Comments"}),
#"Переупорядоченные столбцы1" = Table.ReorderColumns(#"Развернутый элемент Factura",{"WBS", "Order", "Status", "Start Date", "Finish Date", "Closing Date", "Debitor", "Contact Person", "Currency", "P/N", "Description", "Month", "SL", "Model", "Manager", "Admin", "Balance", "PO", "A/S", "Total", "Travels, RUB", "Comments"})
in
#"Переупорядоченные столбцы1" |