Страницы: 1
RSS
График работ в PQ Продолжительность в месяцах
 
Друзья, здравствуйте.

По одному из роликов Н.Павлова нашел решение своей задачи в первом приближении.
Но в запросе есть потенциальная ошибка, на шаге "Список дат" в поле "Пользовательский" это видно:
1. Во-первых, каждые 4 года на пятый происходить смещение даты назад из-за високосного года.
2. Во-вторых, при продолжительности менее года приходится округлять продолжительность в днях в меньшую сторону.
Соответственно, если установить дату начала 02.06.2020 месяц будет меняться помаленьку на предыдущий, а этого не надо.
Можно ли в операторе #duration как-то использовать количество месяцев/лет?
Или можете посоветовать какой-другой алгоритм решения/функцию? Вообще, придумать не могу.

Заранее, спасибо, очень-очень.
 
inженер, показали бы и желаемый результат, а то вы там понашагали
 
Цитата
Михаил Л написал:
показали бы и желаемый результат
так он вроде и показан, требуется оптимизация  ;)
Цитата
inженер написал:
Можно ли в операторе #duration как-то использовать количество месяцев
нет
Цитата
inженер написал:
какой-другой алгоритм решения/функцию?
как вариант List.Generate(), ну и остальное по мелочи переписал:
неправильный код, правильный ниже
Изменено: buchlotnik - 24.05.2020 14:37:49
Соблюдение правил форума не освобождает от модераторского произвола
 
buchlotnik, здравствуйте!

Буду разбираться.
Для меня это другой уровень.
Спасибо, еще раз.
 
Упс, косяк нашёл - вот так надо:
Код
let
    from = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    typ = Table.TransformColumnTypes(from,{{"Начало", type date}, {"Окончание", type date}}),
    l1 = List.Skip(Table.ColumnNames(from),3),
    l2={"Действие","Продолжительность","Ед. изм."},
    fn = (d) => Date.MonthName(d) & " " & Text.From(Date.Year(d)),
    tbl = Table.CombineColumns(typ,l1,(l)=>Table.FromColumns(List.Zip(List.Split(List.RemoveNulls(l),3)),l2),"tmp"),
    exp = Table.ExpandTableColumn(tbl, "tmp",l2),
    add = Table.AddColumn(exp, "Даты", each 
                                [a=[Начало],b=[Продолжительность]*12,c=[Окончание], 
                                    d=List.Generate(()=>[i=0,t=fn(a)],
                                        each Date.AddMonths(a,[i]*b)<=c,
                                        each [i=[i]+1,t=fn(Date.AddMonths(a,([i]+1)*b))],
                                        each[t])][d]),
    to = Table.ExpandListColumn(add, "Даты")[[Название],[Действие],[Даты]]
in
    to
Изменено: buchlotnik - 24.05.2020 15:29:09
Соблюдение правил форума не освобождает от модераторского произвола
 
еще вариант
Код
let
    Источник   = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    Combine    = Table.CombineColumns(Источник,List.Skip(Table.ColumnNames(Источник),3),each List.Split(List.RemoveNulls(_),3),"x"),
    Years=(a) => Date.Year(a)+(Number.From(a)-Number.From(Date.StartOfYear(a)))/((Number.From(Date.EndOfYear(a))-Number.From(Date.StartOfYear(a)))),
    Transform  = Table.ReplaceValue(
                     Combine,
                     each [Начало],
                     each Years([Окончание])-Years([Начало]),
                     (a,b,c)=>
                         let 
                             list = List.TransformMany(
                                        a,
                                        each 
                                            let
                                                d = {List.Range(_,0,2)},
                                                e = Number.RoundUp(c/_{1}),
                                                f = Table.FromRows(List.Repeat(d,e)),
                                                g = Table.AddIndexColumn(f,"i"),
                                                h = Table.CombineColumns(g,{"i","Column2"},each Date.AddMonths(b,_{0}*_{1}*12),"x")
                                            in
                                                Table.ToRows(h),
                                        (x,y)=>y
                                    )
                         in 
                             Table.FromRows(list),
                     {"x"}
                 ),
    Expand     = Table.ExpandTableColumn(Transform, "x", {"Column1", "Column2"}),
    Typed      = Table.TransformColumnTypes(Expand,{{"Начало", type date}, {"Окончание", type date}, {"Column2", type date}}),
    Result     = Table.SelectRows(Typed, each ([Column2] <= [Окончание]))
in
    Result
Изменено: Андрей Лящук - 24.05.2020 18:39:14
 
Только у меня запросы не работают?
Копирую код и вставляю в расширенном редакторе, выгружаю на лист, выходят сообщения об ошибке.  
 
у меня оба без ошибок отрабатывают
 
Цитата
Михаил Л написал:
выходят сообщения об ошибке
Поделитесь?
Вот горшок пустой, он предмет простой...
 
Цитата
Михаил Л написал:
Только у меня запросы не работают?
у меня работают и мой, и Андрея
Соблюдение правил форума не освобождает от модераторского произвола
 
Цитата
Андрей Лящук написал:
у меня оба без ошибок отрабатывают
Вот этот файл скачал и наскриншотил. Еле в лимит уложился)
Проблема, я так понимаю, в моем PQ. Попробую позже снести PQ и вновь установить.
Или не только у меня четыре списка вместо трех?
 
Ну дык это List.Split, у меня в excel 2010 и 2013 почти постоянно приходилось его оборачивать в
Код
List.RemoveMatchingItems(list,{{}})
Проверил в 2013 - оба запроса вывалили ошибку
так работает без ошибок
Код
let
    Источник   = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    Combine    = Table.CombineColumns(Источник,List.Skip(Table.ColumnNames(Источник),3),each List.RemoveMatchingItems(List.Split(List.RemoveNulls(_),3),{{}}),"x"),
    Years=(a) => Date.Year(a)+(Number.From(a)-Number.From(Date.StartOfYear(a)))/((Number.From(Date.EndOfYear(a))-Number.From(Date.StartOfYear(a)))),
    Transform  = Table.ReplaceValue(
                     Combine,
                     each [Начало],
                     each Years([Окончание])-Years([Начало]),
                     (a,b,c)=>
                         let 
                             list = List.TransformMany(
                                        a,
                                        each 
                                            let
                                                d = {List.Range(_,0,2)},
                                                e = Number.RoundUp(c/_{1}),
                                                f = Table.FromRows(List.Repeat(d,e)),
                                                g = Table.AddIndexColumn(f,"i"),
                                                h = Table.CombineColumns(g,{"i","Column2"},each Date.AddMonths(b,_{0}*_{1}*12),"x")
                                            in
                                                Table.ToRows(h),
                                        (x,y)=>y
                                    )
                         in 
                             Table.FromRows(list),
                     {"x"}
                 ),
    Expand     = Table.ExpandTableColumn(Transform, "x", {"Column1", "Column2"}),
    Typed      = Table.TransformColumnTypes(Expand,{{"Начало", type date}, {"Окончание", type date}, {"Column2", type date}}),
    Result     = Table.SelectRows(Typed, each ([Column2] <= [Окончание]))
in
    Result

Код
let
    from = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    typ = Table.TransformColumnTypes(from,{{"Начало", type date}, {"Окончание", type date}}),
    l1 = List.Skip(Table.ColumnNames(from),3),
    l2={"Действие","Продолжительность","Ед. изм."},
    fn = (d) => Date.MonthName(d) & " " & Text.From(Date.Year(d)),
    tbl = Table.CombineColumns(typ,l1,(l)=>Table.FromColumns(List.Zip(List.RemoveMatchingItems(List.Split(List.RemoveNulls(l),3),{{}})),l2),"tmp"),
    exp = Table.ExpandTableColumn(tbl, "tmp",l2),
    add = Table.AddColumn(exp, "Даты", each 
                                [a=[Начало],b=[Продолжительность]*12,c=[Окончание], 
                                    d=List.Generate(()=>[i=0,t=fn(a)],
                                        each Date.AddMonths(a,[i]*b)<=c,
                                        each [i=[i]+1,t=fn(Date.AddMonths(a,([i]+1)*b))],
                                        each[t])][d]),
    to = Table.ExpandListColumn(add, "Даты")[[Название],[Действие],[Даты]]
in
    to
 
Андрей Лящук, спасибо. Придется List.Split держать в карантине. Сам то я буду пользоваться, а делиться запросами уже опасно.
Сделаю запрос с применением List.Split на PQ2010, поделюсь запросом с соседом, а у соседа в Excel2016 List.RemoveMatchingItems будет съедать последнее значение :D  
 
Михаил Л, ну не последнее, а лишнее пустое :)
 
Проснулся и захотел просмотреть темы - что там в продолжение.
Так все мои сегодняшние публикации удалены.
Что это?(Вопрос модераторам)
 
Михаил Л, сайт упал, подняли из резервной копии от часа ночи сегодня.
Вот горшок пустой, он предмет простой...
Страницы: 1
Наверх