Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1 2 След.
RSS
Power query сделать непересекаемые периоды дат
 
#1
23.10.2018 07:19:22
Добрый день, друзья.
Помогите реализовать в power query такую ситуацию.
Есть таблица: первый столбец статус, второй столбец дата.
Статус 1 начинает свой отсчет с 01.10.2018 и автоматом получает продление до конца месяца.
Статус 2 начинает свой отсчет со 02.10.2018 и автоматом получает продление до конца месяца.
Статус 3 начинает свой отсчет с 05.10.2018 и вручную получает окончание 02.11.2018
Мне нужно получить таблицу как во вложении под шапкой "Что нужно получить".

Помогите советом. :-)
 
 
 
#2
23.10.2018 08:52:07
добрый день.
Что то логики я не понял.  
В жизни нет ничего невозможного! Есть только недостаток знаний и умений.
 
 
 
#3
23.10.2018 09:09:37
Цитата
Александр написал:
Что то логики я не понял.
Периоды пересекаются. Мне нужно понять, с какой даты начинается следующий статус.
Изначально, каждый статус имеет свое начало, но корректной даты окончания нет. Мне нужно отрезать от статуса 1 те даты, когда уже идет статус 2.
А от статуса 2 отсечь даты, когда уже идет статус 3.
 
 
 
#4
23.10.2018 09:48:40
Как-то так:
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Измененный тип" = Table.TransformColumnTypes(Источник,{{"Код", type text}, {"Дата", type date}}),
    #"Сгруппированные строки" = Table.Group(#"Измененный тип", {"Код"}, {{"Дата", each List.Min([Дата]), type date}}),
    #"Добавлен индекс" = Table.AddIndexColumn(#"Сгруппированные строки", "Индекс", 0, 1),
    #"Добавлен индекс1" = Table.AddIndexColumn(#"Добавлен индекс", "Индекс.1", 1, 1),
    #"Объединенные запросы" = Table.NestedJoin(#"Добавлен индекс1",{"Индекс.1"},#"Добавлен индекс1",{"Индекс"},"Пред",JoinKind.LeftOuter),
    #"Развернутый элемент Пред" = Table.ExpandTableColumn(#"Объединенные запросы", "Пред", {"Дата"}, {"Пред.Дата"}),
    #"Замененное значение" = Table.ReplaceValue( #"Развернутый элемент Пред", null, List.Max(#"Измененный тип"[Дата]) + #duration(1, 0, 0, 0), Replacer.ReplaceValue,{"Пред.Дата"}),
    #"Добавлен пользовательский объект" = Table.AddColumn(#"Замененное значение", "СписокДат", each List.Dates([Дата], Int64.From([Пред.Дата] - [Дата]), #duration(1, 0, 0, 0))),
    #"Развернутый элемент СписокДат" = Table.ExpandListColumn(#"Добавлен пользовательский объект", "СписокДат"),
    #"Другие удаленные столбцы" = Table.SelectColumns(#"Развернутый элемент СписокДат",{"Код", "СписокДат"}),
    #"Измененный тип1" = Table.TransformColumnTypes(#"Другие удаленные столбцы",{{"СписокДат", type date}})
in
    #"Измененный тип1"
Вот горшок пустой, он предмет простой...
 
 
 
#5
23.10.2018 10:02:06
PooHkrd,
:-) спасибо. Все-таки в PQ логика отличается. Надо учиться мыслить другими категориями.

Мне, конечно, очень неловко занимать Ваше время, но может быть натолкнете на мысль, как реализовать такой вариант. (во вложении столбца A-D - дано, G-I результат)
Изменено: Вильдан volfman - 23.10.2018 11:25:12
 
 
 
#6
23.10.2018 10:54:22
Вот еще вариант со смещением столбцов без использования индексов. Спер отсюда. Преимущество данного метода - скорость работы на больших таблицах 5+ млн строк.
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Измененный тип" = Table.TransformColumnTypes(Источник,{{"Код", type text}, {"Дата", type date}}),
    #"Сгруппированные строки" = Table.Group(#"Измененный тип", {"Код"}, {{"Дата", each List.Min([Дата]), type date}}),
    Смещение = Table.ToColumns(#"Сгруппированные строки") & Table.ToColumns( Table.RemoveFirstN(Table.SelectColumns(#"Сгруппированные строки",{"Дата"}), 1 ) & #table({"Дата"}, {{ List.Max(#"Измененный тип"[Дата]) + Duration.From(1) }}) ),
    ДобавлениеСледующейДаты = Table.FromColumns( Смещение,  Table.ColumnNames(#"Сгруппированные строки") & {"След.Дата"} ),
    #"Добавлен пользовательский объект" = Table.AddColumn(ДобавлениеСледующейДаты, "СписокДат", each List.Dates([Дата], Int64.From([След.Дата] - [Дата]), #duration(1, 0, 0, 0)), type list),
    #"Развернутый элемент СписокДат" = Table.ExpandListColumn(#"Добавлен пользовательский объект", "СписокДат"),
    #"Другие удаленные столбцы" = Table.SelectColumns(#"Развернутый элемент СписокДат",{"Код", "СписокДат"}),
    #"Измененный тип1" = Table.TransformColumnTypes(#"Другие удаленные столбцы",{{"СписокДат", type date}})
in
    #"Измененный тип1"
Изменено: PooHkrd - 23.10.2018 10:55:08
Вот горшок пустой, он предмет простой...
 
 
 
#7
23.10.2018 11:26:33
PooHkrd,
Я в предыдущем посте, еще табличку вложил на вариации. Сижу, туплю :-( Как переучиться с VBA на PQ :-)
 
 
 
#8
23.10.2018 11:51:46
В рамках одного кода может быть только 2 статуса? Пример точно охватывает все варианты развития?
Вот горшок пустой, он предмет простой...
 
 
 
#9
23.10.2018 12:06:45
Цитата
PooHkrd написал:
Вот еще вариант со смещением столбцов без использования индексов
А можно вопрос?
Вот в этой конструкции = Table.ToColumns(#"Сгруппированные строки") & Table.ToColumns( Table.RemoveFirstN(Table.SelectColumns(#"Сгруппированные строки",{"Дата"}), 1 ) & #table({"Дата"}, {{ List.Max(#"Измененный тип"[Дата]) + Duration.From(1) }}) )
как работает выделенный жирным кусок? Задается фиксированная таблица, заголовок "Дата", а как PQ понимает, что каждому элементу списка нужно добавить duration без слова each?  :oops:  
 
 
 
#10
23.10.2018 12:18:38
Цитата
PooHkrd написал:
В рамках одного кода может быть только 2 статуса? Пример точно охватывает все варианты развития?
Может быть и более. :-(
Например,
статус 1 01.10.2018-31.10.2018
статус 2 03.10.2018-29.10.2018
статус 3 09.10.2018-17.10.2018
статус 4 25.10.2018-28.10.2018

Результат:
Статус 101.10.2018
Статус 102.10.2018
Статус 203.10.2018
Статус 204.10.2018
Статус 205.10.2018
Статус 206.10.2018
Статус 207.10.2018
Статус 208.10.2018
Статус 309.10.2018
Статус 310.10.2018
Статус 311.10.2018
Статус 312.10.2018
Статус 313.10.2018
Статус 314.10.2018
Статус 315.10.2018
Статус 316.10.2018
Статус 317.10.2018
Статус 218.10.2018
Статус 219.10.2018
Статус 220.10.2018
Статус 221.10.2018
Статус 222.10.2018
Статус 223.10.2018
Статус 224.10.2018
Статус 425.10.2018
Статус 426.10.2018
Статус 427.10.2018
Статус 428.10.2018
Статус 229.10.2018
Статус 130.10.2018
Статус 131.10.2018
Изменено: Вильдан volfman - 23.10.2018 12:19:08
 
 
 
#11
23.10.2018 12:26:17
Функция #table задает запись таблицы в явном виде:
Код
#table(
   {"Дата"}, 
   { { List.Max(#"Измененный тип"[Дата]) + Duration.From(1) } }
)

Первый аргумент это перечисление заголовков столбцов в виде списка, в данном случае это один столбец "Дата"
Второй аргумент это список строк таблицы, то что внутри списка обозначен еще один список означает, что в таблице будет всего одна строка, А то что внутри второго списка также всего один аргумент, это следствие того, что в таблице только один столбец, иначе пришлось бы перечислять значения для каждого столбца.
Это творчески мной переработанный из приведенной в том посте ссылки код:
Код
#table(NewColNames, List.Repeat({List.Repeat({null}, CountNewCols)}, Step_) )

Тут Имке создает строки и столбцы заполненные null при помощи генератора List.Repeat, Только я вместо null сразу записываю в эту ячейку значение максимальной даты из первоначального источника + 1 день, т.к. знаю что мне потребуется именно оно.
Цитата
Вильдан volfman написал:
Может быть и более. :-(
Тогда сразу давайте исчерпывающий пример, а то сочинишь чего и тут же переделывать придется.
Изменено: PooHkrd - 23.10.2018 17:42:39
Вот горшок пустой, он предмет простой...
 
 
 
#12
23.10.2018 12:50:04
Цитата
PooHkrd написал:
Вильдан volfman  написал:Может быть и более. :-(Тогда сразу давайте исчерпывающий пример, а то сочинишь чего и тут же переделывать придется.
Вот в последнем своем посте описал вроде все возможные ситуации.
Спасибо за разъяснения. Так гораздо продуктивнее осваивать новый инструмент :-)
 
 
 
#13
23.10.2018 12:58:41
Цитата
Вильдан volfman написал:
Вот в последнем своем посте описал вроде все возможные ситуации.
Из того что я вижу Статус с максимальным числом всегда имеет приоритет перед младшими? Т.е. если есть нахлест статуса 2, 3 и 4, то эту дату мы отдаем именно "Статус 4" и никак иначе?
Вот горшок пустой, он предмет простой...
 
 
 
#14
23.10.2018 13:26:33
Цитата
PooHkrd написал:
Из того что я вижу Статус с максимальным числом всегда имеет приоритет перед младшими? Т.е. если есть нахлест статуса 2, 3 и 4, то эту дату мы отдаем именно "Статус 4" и никак иначе?
Тут скорее логика такая: список статусов отсортирован по возрастанию начальной даты. Т.е. чем больше дата начала тот статус и важнее.
Статусы могут быть любыми событиями.
Например,
Пироги 01.10.2018-31.10.2018
Напитки 15.10.2018-16.10.2018
Семечки 10.10.2018-29.10.2018

Результат
Пироги с 01.10.2018 по 09.10.2018
Семечки с 10.10.2018 по 14.10.2018
Напитки с 15.10.2018 оп 16.10.2018
Семечки с 17.10.2018 по 29.10.2018
Пироги с 30.10.2018 по 31.10.2018

Т.е. сортировка внутри одного кода по дате начала, а потом уже пересечения дат  смотреть. Да уж, нетривиальная задача.
Тут как бы возвраты в предыдущее состояние некорректно проставлены.
 
 
 
#15
23.10.2018 13:50:08
Цитата
Вильдан volfman написал:
Да уж, нетривиальная задача.
Да нормальная задача. Сортировкой и индексом решается. Вопрос в том, каким может быть максимальное количество статусов в одном коде? Если их немного , то можно будет сравнительно просто и топорно решить, если нет, то придется заморочиться с циклами.
Вот горшок пустой, он предмет простой...
 
 
 
#16
23.10.2018 14:18:59
Цитата
PooHkrd написал:
Если их немного , то можно будет сравнительно просто и топорно решить, если нет, то придется заморочиться с циклами.
Предполагаю в пределах 4-6 штук. А нет ли под рукой примера цикла в PQ? Интересно посмотреть, как оно реализуется.

p.s. Причем внутри каждого кода статусов может быть разное количество. Это я пытаюсь алгоритм продумать и дописываю возможные варианты.
Изменено: Вильдан volfman - 24.10.2018 06:20:16
 
 
 
#17
23.10.2018 14:34:42
Цитата
Вильдан volfman написал:
А нет ли под рукой примера цикла в PQ?
List.Generate, List.Accumulate
Завтра поковыряю ваш пример, может еще кто присоединится.
Изменено: PooHkrd - 23.10.2018 18:27:10
Вот горшок пустой, он предмет простой...
 
 
 
#18
24.10.2018 11:10:36
Цитата
PooHkrd написал:
Завтра поковыряю ваш пример, может еще кто присоединится.
А если методом вытеснения.
Понятно, что сначала группируем таблицу по коду и сортируем по дате начала. А потом внутри кода проделываем следующее ( во вложении)
1 Итерация => получаем таблицу, содержащую список дат статуса 1 от даты начала до даты конца
2 Итерация => получаем таблицу, содержащую остаток списка дат статуса 1, которые не встречаются в списке дат статуса 2 и список дат статуса 2 от даты начала до даты конца
3 Итерация => получаем таблицу, содержащую остаток списка дат статуса 1 и статуса 2, которые не встречаются в списке дат статуса 3 и список дат статуса 3 от даты начала до даты конца
4 Итерация => получаем таблицу, содержащую остаток списка дат статуса 1, статуса 2 и статуса 3, которые не встречаются в списке дат статуса 4 и список дат статуса 4 от даты начала до даты конца
Думаю, итерации можно продолжать.
В итоге, внутри кода получим таблицу со статусами и оставшимися принадлежащими им датами.
Как это выразить на языке М? Верна ли моя логика? Вероятно List.Accumulate должен помочь, но никак не хватает знаний, как туда все это упаковать.
 
 
 
#19
24.10.2018 11:17:58
Я планировал не совсем так, хочу использовать один Экселевский подход с выводом статусов в заголовки таблицы в нужном порядке и по приоритетам для каждой строки вычислять название правильного статуса. Как чего путное выйдет выложу. Сейчас просто работы привалило - некогда.
Вот горшок пустой, он предмет простой...
 
 
 
#20
24.10.2018 15:33:31
В общем как-то так:
Скрытый текст

В запросе Таблица собственно обработка, в запросе ОбработкаСтатусов вы можете посмотреть как внутри группировки преобразуются данные для получения нужного результата. Заранее предупреждаю что вариант с List.Generate скорее всего будет более быстродейственным на больших массивах, но я пока до этой функции не дорос, поэтому решал задачу через то же место, через которое гинеколог в известном анекдоте движок в тракторе перебирал. :D
Вот горшок пустой, он предмет простой...
 
 
 
#21
25.10.2018 07:23:34
Цитата
PooHkrd написал:
В запросе Таблица собственно обработка, в запросе ОбработкаСтатусов вы можете посмотреть как внутри группировки преобразуются данные для получения нужного результата.
Огромное спасибо не только за решение задачи, а за развернутый ликбез по теме :-)
Никак не понимал, как завернуть во внутрь запроса еще запрос :-)

Конечно, интересно решение через List.Accumulate увидеть для общей ерундиции. :-)
Может быть Максим c Андреем что посоветуют. :-)
Изменено: Вильдан volfman - 25.10.2018 07:30:55
 
 
 
#22
25.10.2018 09:01:28
Вам никто не мешает попросить их в личке заглянуть в тему. Если время найдут и задачка им будет интересна - думаю что ответят.
Вот горшок пустой, он предмет простой...
 
 
 
#23
25.10.2018 09:08:29
Цитата
PooHkrd написал:
попросить их в личке
! в личку обычно за доп. плату  ;)
потому что ради интереса и так в ветку заходят... а личка всё-таки private территоря времени!... не нам же распоряжаться ей чужой... да и решение на добровольных началах может быть интересно и др. посетителям сайта...
обращение в личку - безвозмездное покушение на чужое время, да только для себя любимого - это эгоизм  :sceptic: имхо...
p.s.
давайте соблюдать рамки приличия...
Изменено: JeyCi - 25.10.2018 09:10:13
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
 
 
#24
26.10.2018 08:00:43
Пытаюсь освоить все-таки и возник вопрос.
Друзья, как обратиться к столбцу дата вот для такой конструкции?

result=List.Accumulate(СтолбецСоСпискомДат[Дата], {}, (st,cur)=>List.Combine(cur,List.Difference(st,cur)))

СтолбецСоСпискомДат                                                                                  
СтатусДата СДата ПОДата
Статус 101.10.201831.10.2018[List]
Статус 210.10.201829.10.2018[List]
Статус 315.10.201818.10.2018[List]
Статус 417.10.201828.10.2018[List]
Статус 522.10.201828.10.2018[List]
Изменено: Вильдан volfman - 26.10.2018 08:01:16
 
 
 
#25
26.10.2018 08:58:50
Посмотрите как оно используется на примере кумулятивных сумм.
Вот горшок пустой, он предмет простой...
 
 
 
#26
26.10.2018 09:45:40
Цитата
Вильдан volfman написал:
result=List.Accumulate(СтолбецСоСпискомДат[Дата], {}, (st,cur)=>List.Combine(cur,List.Difference(st,cur)))
а зачем вы делаете Combine, если у вас и так есть Accumulate?.. так что хотите на выходе такими манипуляциями - опишите логику шагов - для себя хотя бы  ;) ...
простой List.Accumulate - для получения нескольких аггрегированных результатов - как-то так:
Код
let
Source=Excel.CurrentWorkbook()
{[Name="Table1"]}[Content],
myList=Table.Column(Source, "vol"),
done=List.Accumulate(myList, [result1=0, result2=0, resultN=1],   //initial state & variables

(state, current)=>
// каждый последующий
[result1=Value.Add(state[result1],current),
result2=Value.Subtract(state[result2],current),
resultN=(state[resultN]*current)]
) 
in done
Изменено: JeyCi - 26.10.2018 11:32:48
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
 
 
#27
26.10.2018 11:27:47
Цитата
JeyCi написал:
опишите логику шагов - для себя хотя бы
Да, что-то я слегка запутался. Пойду читать. Спасибо за пример.
 
 
 
#28
26.10.2018 11:30:59
может вы всё усложняете?.. вам это надо ? (dateTable)
Код
let
    dateList = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(dateList,{{"Дата С", type date}, {"Дата ПО", type date}}),
    dateTable = Table.AddColumn(#"Changed Type", "dt_list", each  List.Dates(_[Дата С],Duration.Days(_[Дата ПО] - _[Дата С]) + 1, #duration(1, 0, 0, 0)), type datetime ),
    res=Table.ExpandListColumn(dateTable,"dt_list"),
    #"Changed Type1" = Table.TransformColumnTypes(res,{{"dt_list", type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Дата С", "Дата ПО"})
in #"Removed Columns"
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
 
 
#29
26.10.2018 11:46:08
Цитата
JeyCi написал:
может вы всё усложняете?.. вам это надо ?
Задача с примером в 18 посте. PooHkrd привел решение в 20.
А я пытаюсь найти свой нативный способ через List.Accumulate
 
 
 
#30
27.10.2018 11:03:41
да, каюсь не сразу осилила читать всю ветку...
по сути надо сравнивать с каждым предыдущим по Дата_ПО -- и удалять те, которые уже вошли датой в последний статус, а иные (у которых Дата_ПО превосходит Дата_ПО последнего статуса -- добавлять довесочком непересекающиеся дни (т.е. отсутствующие в последнем статусе и всех предыдущи)... вам не кажется - что вы могли бы пробовать ещё на этапе получения нач. табл. - разобраться с исключением пересечений?.. (любыми средствами - хотя бы sql)... чтобы потом не ковыряться в этом через PQ... не вижу смысла отталкиваться от кривой нач. структуры инструментом предназначенным для работы со структурой в целом, если для работы с частями от целого имеются иные более удобные инструменты... не вникаю в вашу задачу - не интересно отвёрткой забивать гвозди  8) ... но если вы изобретёте новую функцию EARLIER(DAX) для PQ - и ещё эффективно работающую... - порекомендую вас в Microsoft  ;) ... успехов вам... хотя сама всегда стараюсь изначально продумывать источник, чтобы потом для него выбирать инструмент и на него ложить код выбранным инструментом
p.s.
вы хотите задачу - обратную этой - и ещё убрать пересечение дат (для таких целей есть AntiJoin и List.Difference в PQ - не тестировала)
p.p.s с недающим вам спокойно жить - List.Accumulate - есть интересный пример Running Totals using the List.Accumulate() Function -- куммулятивного собирания листа (и совсем не List.Accumulate, а List.Range нужен)... с ходу не адаптируется под ваш случай -- неудобство: расковырять группы по кодам вашим (можно пробовать заворачивать UDF)... всё-таки PQ немного для иного -- как ETL инструмент, а не для групповых операций и хождению по строкам)... имхо... хотя и PQ работает с sql-запросами - но ваш вопрос всё-таки больше смахивает на ТЗ для разбора всех огрех вашей нач. структуры -- это уже не "Вопрос по XL"  ;)  
Изменено: JeyCi - 27.10.2018 12:31:42
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
 
 
Страницы: 1 2 След.
Наверх