let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type any}, {"Column2", type any}, {"Column3", type text}, {"Column4", type text}, {"Column5", type any}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}}),
#"Transposed Table" = Table.Transpose(#"Changed Type"),
#"Filled Down" = Table.FillDown(#"Transposed Table",{"Column1"}),
#"Transposed Table1" = Table.Transpose(#"Filled Down"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Заказ №", Int64.Type}, {"Сумма", Int64.Type}, {"Оформили", type text}, {"Оформили_1", type text}, {"Оформили_2", type any}, {"Делали", type text}, {"Делали_3", type text}, {"Делали_4", type text}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Заказ №", "Сумма"}, "Attribute", "Value"),
#"Extracted Text Before Delimiter" = Table.TransformColumns(#"Unpivoted Other Columns", {{"Attribute", each Text.BeforeDelimiter(_, "_"), type text}}),
#"Grouped Rows" = Table.Group(#"Extracted Text Before Delimiter", {"Заказ №", "Attribute"}, {{"PerfCount", each Table.RowCount(_), Int64.Type}}),
Custom1 = #"Extracted Text Before Delimiter",
#"Merged Queries" = Table.NestedJoin(Custom1, {"Заказ №", "Attribute"}, #"Grouped Rows", {"Заказ №", "Attribute"}, "Custom2", JoinKind.LeftOuter),
#"Expanded Custom2" = Table.ExpandTableColumn(#"Merged Queries", "Custom2", {"PerfCount"}, {"PerfCount"}),
#"Added Custom" = Table.AddColumn(#"Expanded Custom2", "ЗП", each ([Сумма]/[PerfCount])*0.06),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Attribute", "Value", "ЗП"}),
#"Pivoted Column" = Table.Pivot(#"Removed Other Columns", List.Distinct(#"Removed Other Columns"[Attribute]), "Attribute", "ЗП", List.Sum),
#"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Оформили", "ЗП 6% от оформленных заказов"}, {"Делали", "ЗП 6% от выполненных заказов"}})
in
#"Renamed Columns"
|