Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Power Query. Как объединить строки с пересекающимися интервалами по датам, создав одну строку и один интервал дат
 
#1
02.06.2020 12:53:53
Добрый день!
Дорогие участники форума, подскажите, как решить вот такую задачку на обработку в Power Query.
Есть таблица с ключевыми полями дата начала акции, дата окончания акции, товар и тд.
Среди таких акций встречаются пересекающиеся по периоду.
То есть, например, начало следующей акции попадает в интервал предыдущей, но дата окончания позже, позиция соответственно та же.
Нужно сделать из 2-ух строк одну, где будет в качестве ключевых полей даты начало акции первой и окончание акции второй.
Также, если дата окончания одной акции совпадает с датой начала другой акции, тоже нужно сделать одну строку с началом одной акции и окончанием второй.
Табличка пример во вложении. Вкладка исходный то из чего хочу получить, вторая вкладка то, что хотелось бы получить.

Буду очень благодарна если подскажите, или натолкнёте на решение, заранее спасибо!
 
 
 
#2
02.06.2020 14:57:20
Татьяна Комолова, такой вариант
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Дата начала акции", type date}, {"Дата окончания акции", type date}, {"Номер", type text}}),
    Group = Table.Group(Type, {"Товар"}, {{"a", each let a=_,b=Table.ToColumns(a),c= Table.AddIndexColumn(Table.FromColumns
    (b&{{Date.From(1)}&List.RemoveLastN(b{3},1)}),"id"),d= Table.FillDown(Table.AddColumn(c,"a", each if [Column6]>=[Column3]
     and [Column6]<>[Column4] then null else [id]),{"a"}) in Table.FromRecords(Table.Group(d, {"a"}, {{"b", each let a1=_,
     b1=Table.AddColumn(a1,"b", each List.Max(a1[Column4])) in b1{0}[[Column1],[Column2],[Column3],[b],[Column5]]}})[b])}})[a],
    Custom3 = Table.Combine(Group),
    Custom1 = Table.RenameColumns(Custom3,List.Zip({Table.ColumnNames(Custom3),Table.ColumnNames(Source)}))
in
    Custom1
НА МЯСО В ПРОФИЛЕ
 
 
 
#3
02.06.2020 15:41:43
еще вариант
Код
let
    Source  = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    Typed   = Table.TransformColumnTypes(Source,{{"Дата начала акции", type date}, {"Дата окончания акции", type date}}),
    Grouped = Table.Group(Typed, {"Товар"}, {{"_", 
                  each let 
                      Sorted    = Table.Buffer(Table.Sort(_,{{"Товар", Order.Ascending}, {"Дата начала акции", Order.Ascending}, {"Дата окончания акции", Order.Ascending}})),
                      Columns   = Table.ColumnNames(Sorted)&{"x"},
                      AddOffset = Table.FromColumns(Table.ToColumns(Sorted)&{{null}&List.RemoveLastN(Sorted[Дата окончания акции],1)},Columns),
                      Grouped   = Table.Group(AddOffset, Columns, 
                                      {{"_", 
                                          each [
                                              a=Table.Min(_,"Дата начала акции")[Дата начала акции],
                                              b=Table.Max(_,"Дата окончания акции")[Дата окончания акции]
                                          ], type []}},
                                      0,
                                      (a,b)=>if b[x]=null then 1 else if b[Дата начала акции]<=b[x] then 0 else 1
                                  ),
                      Remove    = Table.RemoveColumns(Grouped,{"Дата начала акции", "Дата окончания акции","x"}),
                      Expand    = Table.ExpandRecordColumn(Remove, "_", {"b", "a"}, {"Дата окончания акции", "Дата начала акции"}),
                      Reorder   = Table.ReorderColumns(Expand,{"Дата начала акции", "Товар"})
 
                  in Reorder, 
              type table}}),
    Result  = Table.Combine(Grouped[_])
in
    Result
Изменено: Андрей Лящук - 02.06.2020 16:38:23
 
 
 
#4
02.06.2020 15:44:09
Доброе время суток
Цитата
Татьяна Комолова написал:
Есть таблица с ключевыми полями дата начала акции, дата окончания акции, товар и тд.
Что вы подразумеваете под выражением ключевые поля?
Почему в "Исходный" 4 строка не объединена с 5 и 6 строкой - по датам же пересекается с 5 строкой?
 
 
 
#5
02.06.2020 16:16:48
Ух, ты!
Михаил Л, Андрей Лящук, Спасибо Вам огромное, буду пробовать и разбираться в Вашем коде!
Андрей VG, у меня большая табличка, скопировала кусочек для примера, эту строчку надо было удалить, чтобы не путать пример.
 
 
 
#6
02.06.2020 16:25:38
Цитата
Татьяна Комолова написал:
эту строчку надо было удалить, чтобы не путать пример
Только учтите что в моем варианте проверка: если две акции начались в один день, то они не группируются  
НА МЯСО В ПРОФИЛЕ
 
 
 
#7
02.06.2020 16:31:06
Михаил Л, спасибо огромное, подойдёт!
 
 
 
#8
02.06.2020 16:41:43
а в моем коде еще 1 правка, пробовал группировать с GroupKind.Global, потом решил сделать с GroupKind.Local, а 1 на 0 поменять забыл :(
 
 
 
#9
02.06.2020 16:45:00
Андрей Лящук, спасибо большущее!
мне ещё далеко до такого кода, но теперь знаю к чему стремиться!
 
 
 
#10
02.06.2020 16:52:50
Андрей Лящук,вы смогли прикрутить пятый аргумент к Table.Group, а я тыкался-тыкался и сделал, как обычно у меня, через индекс и т.д.
НА МЯСО В ПРОФИЛЕ
 
 
 
Страницы: 1
Наверх