let
a = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
Index = Table.AddIndexColumn(a, "Индекс", 0, 1),
Type = Table.TransformColumnTypes(Index,{{"Количество", Int64.Type}, {"Цена", Int64.Type}, {"product_id", type text}, {"Создано", type date}}),
b=Excel.CurrentWorkbook(){[Name="Таблица2"]}[Content],
Type2 = Table.TransformColumnTypes(b,{{"Количество", Int64.Type}, {"Цена", Int64.Type}, {"product_id", type text}, {"Сумма", Int64.Type}, {"Создано", type date}}),
Merged = Table.NestedJoin(Type,{"product_id"},Type2,{"product_id"},"b",JoinKind.LeftOuter),
Expanded = Table.ExpandTableColumn(Merged, "b", {"Количество", "Сумма", "Создано"}, {"Количество.1", "Сумма", "Создано.1"}),
Custom = Table.AddColumn(Expanded, "c", each [Создано.1]<=[Создано]),
Filtered = Table.SelectRows(Custom, each ([c] = true)),
Grouped = Table.Group(Filtered, {"Количество","Цена","product_id","Создано", "Индекс"}, {{"sum", each List.Sum([Сумма]), type number}, {"col", each List.Sum([Количество.1]), type number}}),
Custom1 = Table.AddColumn(Grouped, "x", each [sum]/[col]*[Количество]),
Removed = Table.RemoveColumns(Custom1,{"Индекс", "sum", "col"})
in
Removed
|