let
Source = Excel.CurrentWorkbook(){[Name="Таблица13"]}[Content],
tableCombine =
Table.Combine( {
Table.FromColumns( { Source[Дата], Source[команда_1], Source[#"в/п_1"] } ),
Table.FromColumns( { Source[Дата], Source[команда_2], Source[#"в/п_2"] } )
} ),
tableRenameColumns =
Table.RenameColumns(
tableCombine, {
{"Column1", "Дата"},
{"Column2", "Команда"},
{"Column3", "Исход"}
}
),
tableSelectRows =
Table.SelectRows(
tableRenameColumns,
(x) => x[Команда] <> null
),
tableGroup =
Table.Group(
tableSelectRows,
{"Команда"},
{
{
"temp",
(x) => [
tableSort = Table.Sort(x, {"Дата", Order.Ascending}),
tableAddIndexColumn = Table.AddIndexColumn( tableSort, "Index", -1 ),
list_Исход = List.Buffer( tableAddIndexColumn[Исход] ),
list_Count = List.Count( list_Исход ),
fResult = ( count, result, prev_result, last_result ) => [
condition =
if result = last_result
then count + 1
else
if prev_result <> last_result
then 0
else count
][ condition ],
listGenerate =
List.Generate(
() => [
id = 0,
winner_count = fResult( 0, "В", 0, list_Исход{ id } ),
lose_count = fResult( 0, "П", 0, list_Исход{ id } )
],
each [ id ] < list_Count,
each [
id = [ id ] + 1,
winner_count = fResult( [ winner_count ], "В", list_Исход{ id - 1 }, list_Исход{ id } ),
lose_count = fResult( [ lose_count ], "П", list_Исход{ id - 1 }, list_Исход{ id } )
],
each [
[ id ], [ winner_count ], [ lose_count ]
]
),
tableFromRecords = Table.FromRecords( listGenerate ),
tableAddColumn_Последовательность =
Table.AddColumn(
tableFromRecords,
"Последовательность",
(x) => if x[winner_count] <> 0 then Text.From(x[winner_count]) & "В" else Text.From(x[lose_count]) & "П"
),
tableJoin =
Table.Join(
tableAddIndexColumn, "Index",
tableAddColumn_Последовательность, "id"
),
tableAddColumn_Ключ =
Table.AddColumn(
tableJoin,
"Ключ",
(y) => Text.From(Date.From(y[Дата])) & "_" & y[Команда]
),
tableSelectColumns =
Table.SelectColumns(
tableAddColumn_Ключ, {
"Ключ", "Последовательность"
}
)
][ tableSelectColumns ]
}
}
),
tableRemoveColumns = Table.RemoveColumns( tableGroup, {"Команда"} ),
tableExpandTableColumn =
Table.ExpandTableColumn(
tableRemoveColumns,
"temp",
{"Ключ", "Последовательность"},
{"Ключ", "Последовательность"}
)
in
tableExpandTableColumn
|
