Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Power Query: Последний выполненный этап, Последняя дата выполнения проекта в Power Query
 
#1
22.04.2018 13:37:10
Всем привет! Нужен совет знатоков.Задача найти последний выполненный этап (milestone) по объекту, его день и статус в PQ. В примере сделал с помощью добавления условного столбца. Пытался делать по другому Unpivot всех кроме Site ID, потом Group, List.Max...но в таком случае есть значение отличные от дат (MS Status) :(. Есть ли какой-то другой способ (do while, перебирать данные с отдельного списка этапов которые только важные или по всем столбцам сразу), потому как в таблице может быть до 50 таких столбцов и прописывать все 50 с помощью условного столбика как-то до неприличия некрасиво
Опыт и практика - великое дело! Век живи, Век учись!
 
 
 
#2
22.04.2018 14:04:24
Нет под рукой экселя, но если на пальцах, то в чем проблема после анпивота отфильтровать строки со словом "status" потом преобразовать данные в тип date, и через группировку внутри каждой группы по фильтру оставить строку с максимальной датой? Таким образом у вас сразу получится и название майлстоуна оставить и его дату.
Вот горшок пустой, он предмет простой...
 
 
 
#3
22.04.2018 14:23:12
Так и делал, дублировал запрос, в другом запросе делал Unpivot  группировка по Site ID,  List.Max по дате, конечно убрал с названием Status. Потом merge 2 таблиц. Но получалось у меня следующее  некоторый майлстоуны были с одинаковой максимальной датой, поэтому уже не подходило мне  так как в таком случае нужно будет отбирать какой из майлстоунов последний. Я не очень силен в функциях PQ. Поэтому была идея вывести список майлстоунов в отдельный запрос и сделать им индексацию чтобы понимать какой майлстоунов идёт за каким. Бывают майлстоуны которые могут идти параллельно основным и дата у них может быть максимальный чем у последних майлстоунов. Поэтому я и начал задачу решать перебором от последнего к первому  
Опыт и практика - великое дело! Век живи, Век учись!
 
 
 
#4
22.04.2018 17:19:26
Доброе время суток.
Цитата
sanych09 написал:
Так и делал
Однако, что-то не увидел тех попыток. Как и писал коллега, PooHkrd, решение
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    grouped = Table.Group(Source, {"Site ID", "MS 4 STATUS"}, {"temp", each Table.Last(Table.UnpivotOtherColumns(_, {"Site ID", "MS 4 STATUS"}, "Last MS", "Last Date"))[[#"Last MS"], [#"Last Date"]]    }),
    expanded = Table.ExpandRecordColumn(grouped, "temp", {"Last MS", "Last Date"})
in
    Table.ReplaceErrorValues(expanded, List.Transform({"Last MS", "Last Date"}, each {_, null}))

Успехов.
 
 
 
#5
22.04.2018 19:43:16
Андрей, спасибо за ответ. Сейчас не за компьютером, посмотрю обязательно пример кода что вы написали. На данный момент не пойму, почему группировка идёт ещё и по MS 4 Status? У меня могут быть из 50 столбцов 14 со статусом (MS 4 Status, MS 15 Status ... ) , а остальные просто с датами.
Опыт и практика - великое дело! Век живи, Век учись!
 
 
 
#6
22.04.2018 20:38:53
Цитата
sanych09 написал:
14 со статусом (MS 4 Status, MS 15 Status ... )
ГДЕ это в примере, что есть и как должно получиться?
 
 
 
#7
23.04.2018 09:04:40
Цитата
Андрей VG написал:
ГДЕ это в примере, что есть и как должно получиться?
Результатом должно быть добавление 3 столбцов: Last MS, Last Date, Status. (MS со статусом может быть несоколько, в примере указал только один). Столбец со статусом будет обновлен в случае - если следующие после него майлстоуны пустые. Сейчас займусь изучением Вашего когда
Скрин и файл прикрепил. Исходные данные берутся с листа DPR. в этом листе могут быть до 50 колонок с номерами майлстоунов. Лист Result это как должно получится и я его сделал с Условным столбцом, прописать до 10 условий (10 майлтоунов) еще можно таким образом - но если больше, то решение 100% другое должно быть
Опыт и практика - великое дело! Век живи, Век учись!
 
 
 
#8
23.04.2018 12:07:53
Ну так, не особо оптимизируя, почти все кнопками:
Код
// Table3
let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    Unpivoted = Table.UnpivotOtherColumns(Source, {"Site ID"}, "Атрибут", "Значение"),
    Grouped = Table.Group(Unpivoted, {"Site ID"}, {{"tab", each Table.Sort(_, {"Атрибут"}), type table}}),
    AddedMSOnly = Table.AddColumn(Grouped, "tab_MS_only", each Table.SelectRows([tab], each not Text.Contains(_[Атрибут], "STATUS"))),
    AddedLastMS = Table.AddColumn(AddedMSOnly, "Last MS", each List.Last([tab_MS_only][Атрибут])),
    AddedLastMSDate = Table.AddColumn(AddedLastMS, "Last MS Date", each List.Last([tab_MS_only][Значение])),
    AddedLastStatus = Table.AddColumn(AddedLastMSDate, "Status", each if Text.Contains(List.Last([tab][Атрибут]), "STATUS") then List.Last([tab][Значение]) else null),
    Removed = Table.SelectColumns(AddedLastStatus,{"Site ID", "Last MS", "Last MS Date", "Status"}),
    Merged = Table.NestedJoin(Source,{"Site ID"},Removed,{"Site ID"},"Removed",JoinKind.LeftOuter),
    Expanded = Table.ExpandTableColumn(Merged, "Removed", {"Last MS", "Last MS Date", "Status"}, {"Last MS", "Last MS Date", "Status"}),
    Replaced = Table.ReplaceValue(Expanded,null,"no progress",Replacer.ReplaceValue,{"Last MS"}),
    MStoDate = List.Select(List.Skip(Table.ColumnNames(Source),1), each not Text.Contains(_, "STATUS")) & {"Last MS Date"},
    ListToTypes = List.Transform(MStoDate, each {_, type date}),
    ChangedDateType = Table.TransformColumnTypes(Replaced,ListToTypes),
    ChangedTextType = Table.TransformColumnTypes(ChangedDateType,{{"Site ID", type text}, {"Last MS", type text}, {"Status", type text}})
in
    ChangedTextType
F1 творит чудеса
 
 
 
#9
23.04.2018 13:24:54
Максим Зеленский, спасибо! то что нужно. Андрей VG, Ваш код тоже четко работает для определения Last MS, Last Date. Низкий поклон, господа!
Когда делал Unpivot (еще до того как запостить) обнаружил чтo почему-то пропалал один Site ID в котором были все строки "null" и он у меня во время группировки терялся. А по вашим кодам - все ОК.
Для себе отмечу, что очень круто когда есть возможность кнопками все сделать... а потом некоторые шаги можно в Редакторе объединить.  
Опыт и практика - великое дело! Век живи, Век учись!
 
 
 
Страницы: 1
Наверх