Страницы: 1
RSS
Расчет разницы между датами из одной колонки в Power Query, Описание приемов и способов поиска минимальной и максимальной даты с последующим определением разницы в месяцах
 

Здравствуйте!

У моей модели в PBI есть одна колонка с датами, даты расположены хаотично, но это не принципиально.

В Power Query необходимо создать пользовательскую колонку, в которой будет рассчитано количество месяцев между минимальной и максимальной датой.

Как это сделать? Желательно в двух вариантах:

1.      в котором не нужно будет создавать дубликат столбца с датами, т.е. минимальная и максимальная дата определяется только из одного столбца

2.      с созданием дубликата столбца дат и поиска в одном столбце минимальную дату, в дубликате искать максимальную дату и в итоге на разнице дат, определять количество месяцев.

 
Цитата
Георгий Шкуро написал:
даты расположены хаотично
Умею писать хаотичный код, но не уверен что вам это поможет.
Вы бы в файлике хоть примерно структуру источника отобразили бы. А так я даже не знаю чем помочь.
Изменено: PooHkrd - 27.05.2020 17:35:21
Вот горшок пустой, он предмет простой...
 
Георгий Шкуро, грубо:
Код
add = Table.AddColumn(YourTable, "diff", each let a = YourTable[DatesColumn] in Number.RoundDown(Number.From((List.Max(a) - List.Min(a))/30)))
 
Цитата
PooHkrd написал:
Вы бы в файлике хоть примерно структуру источника отобразили бы.
Изменено: Георгий Шкуро - 27.05.2020 23:30:44
 
Aleksei_Zhigulin,
Попробовал, что-то пошло не так.
 
Доброе время суток.
Цитата
Георгий Шкуро написал:
что-то пошло не так.
Естественно, где
Цитата
Aleksei_Zhigulin написал:
YourTable
:qstn:
 
Цитата
Георгий Шкуро написал:
Попробовал
Плохо попробовали.
Формулу подрихтовал для быстродействия. Так пробуйте:
Код
= Table.AddColumn(
        #"Измененный тип2", 
        "Пользовательская", 
        let a = List.Buffer(#"Измененный тип2"[Дата транзакции]), 
             max = List.Max(a), 
             min = List.Min(a) 
        in each Number.RoundDown( Number.From(max - min)/30 ) )
Вот горшок пустой, он предмет простой...
 
PooHkrd, доброго дня,

Довольно редко сталкиваюсь с необходимостью оптимизировать быстродействие запросов, но разве List.Buffer здесь что-то ускоряет? Мне казалось, что при конструкции let a = list in List.Max(a) - List.Min(a) движок будет обращаться к списку a только один раз, неважно, сколько раз этот список будет встречаться в выражении in.
 
Aleksei_Zhigulin, это да, но если вы обращаетесь к списку после each то обращений будет столько же сколько строк в таблице * 2, т.к. вы считаете и мин и макс. если же сначала посчитать все данные, которые не зависят от контекста строки (во загнул :D ), и в each передавать уже посчитанные скалярные переменные, то обращений будет всего 2. Буфер в моем случае чуть-чуть ускорит процесс для очень большой таблицы, тогда к нему будет обращение 1 раз вместо двух.
Тут ведь как, мы сейчас обсуждаем сферического коня в вакууме, а по факту эти вычисления могут быть уже 20-тым по счету шагом, и обращение к столбцу из-за ленивки могут рассчитываться очень долго.
Вот горшок пустой, он предмет простой...
 
PooHkrd, точно, спасибо! Порядок each упустил из виду. Вот эту встроенную конструкцию неправильно вспомнил:
Код
= Table.SelectRows(Source, let earliest = List.Min(Source[Col1]) in each [Col1] = earliest)
Изменено: Aleksei_Zhigulin - 28.05.2020 14:42:01
 
Aleksei_Zhigulin, в вашей формуле из поста №10, нет нельзя.
В чем финт первой конструкции, которая формируется кодом по кнопке фильтрации самой ранней даты? В том, что результатом, который присваивается переменной earliest является скалярное значение, которое согласно спецификации языка рассчитывается один раз и сразу кладется в память. В вашей второй конструкции вы присваиваете переменной а элемент составного типа, который рассчитывается лениво, т.е. каждый раз при обращении к нему. Что будет происходить? При каждой итерации будет рассчитывать мин/макс списка а, при этом транслятор когда будет видеть переменную а, будет действовать так: это список, а как его посчитать? Ага вон код, и будет прогонять вообще весь расчет всех предыдущих шагов, для получения одного и того же списка. Т.е. функция вашей конструкции только в том, чтобы укоротить код. Чтобы увеличить производительность нужно как минимум положить список в память, а еще лучше до each посчитать оба скалярных значения, сразу же их разность и в each  передавать одно готовое скалярное значение. Вот это самое оптимальное.
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
Так пробуйте
Добрый день.
Уже теплее, результат определен правильно, в модели 19 месяцев, только мне нужно чтобы это значение было расположено именно в столбце.
 
Цитата
Георгий Шкуро написал:
Уже теплее
А вы все никак не научитесь копировать решения в свой код. Где в моей формуле вы увидели вот это?

Я про этот самый each распинаюсь тут уже час в обсуждении с Алексеем, а вы это все игнорируете.
Изменено: PooHkrd - 28.05.2020 15:31:56
Вот горшок пустой, он предмет простой...
 
PooHkrd, моя вторая конструкция с вопросом провисела всего пару минут, но Вы таки успели на него ответить  :D

Возможно, Вы правы, и List.Buffer действительно ускоряет быстродействие. Но в мою картину мира это пока не укладывается  :) .  Прогнал на миллионе строк - оба запроса обновляются одинаковое время (~ 20 сек.). Правда, проверял на числах, а не датах.
Код
= Table.AddColumn(types, "aaa", let a = types[Столбец2] in each List.Max(a) - List.Min(a))
Код
= Table.AddColumn(types, "aaa", let a = List.Buffer(types[Столбец2]), b = List.Max(a), c = List.Min(a) in each b - c)

Это наводит на крамольные мысли, что лист a запоминается один раз, а не пересчитывается в каждой строке.
 
Цитата
PooHkrd написал:
А вы все никак не научитесь копировать решения в свой код.
Виноват, не заметил. Теперь результат идеальный!
Изменено: Георгий Шкуро - 28.05.2020 16:22:40
 
PooHkrd, есть еще вопрос: как в Power Query посчитать количество строк по каждому участнику, к примеру сколько строк по номеру 9465776802285679 и т.д. по каждому номеру.
 
Цитата
Георгий Шкуро написал:
есть еще вопрос
Это в ветке работа за деньги так можно в одной теме накидывать. А в бесплатной ветке Один вопрос - одна тема.
Цитата
Aleksei_Zhigulin написал:
Это наводит на крамольные мысли
Я давно замечал, что итератор в AddColumns как-то иначе работает чем в остальных функциях. Но чтобы не запоминать где и чем оно отличается всегда такие расчеты делаю до each а потом результат уже передаю в функцию. Еще ни разу не подводило.
Изменено: PooHkrd - 28.05.2020 16:59:28
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
итератор в AddColumns как-то иначе работает чем в остальных функциях
Да, по-моему, на форуме уже поднималась эта тема, потому и запомнилось. Спасибо!
 
Цитата
PooHkrd написал:
А в бесплатной ветке Один вопрос - одна тема.
Услышал. Благодарю за решение. Спасибо!
Страницы: 1
Наверх