Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
PQ определить длительность интервала последнего события
 
#1
23.04.2019 05:59:24
Здравствуйте!
В прилагаемом примере надеюсь понятнее, тем не менее:
подскажите, как можно просчитать длительность последнего из однотипных событий.
То есть: есть последовательность одинаковых событий, они чередуются с другими. Интересует возможность посчитать длительность только последнего события.
 
 
 
#2
23.04.2019 06:51:56
Доброе время суток
Цитата
игорь сергеев написал:
Интересует возможность посчитать длительность только последнего события
На порядке Гос.№, Дата делаете локальную группировку (GroupKind.Local), с подсчётом числа дней и максимальной даты. Фильтруете по ремонт и для каждого Гос.№ выбираете запись с максимальной датой - окончание последнего ремонта.
 
 
 
#3
23.04.2019 07:17:35
Во вложении решение с доп.столбцом
Изменено: WS27 - 23.04.2019 07:25:48
 
 
 
#4
23.04.2019 08:03:58
чето много шагов
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Дата", type date}}),
   #"Добавлен индекс" = Table.AddIndexColumn(#"Changed Type", "Индекс", 0, 1),
    #"Добавлен индекс1" = Table.AddIndexColumn(#"Добавлен индекс", "Индекс.1", 1, 1),
    #"Объединенные запросы" = Table.NestedJoin(#"Добавлен индекс1",{"Индекс"},#"Добавлен индекс1",{"Индекс.1"},"Предыдущая",JoinKind.LeftOuter),
    #"Expanded {0}" = Table.ExpandTableColumn(#"Объединенные запросы", "Предыдущая", {"Состояние"}, {"Состояние.1"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded {0}", "Пользовательская", each if [Состояние] = "ремонт" and [Состояние.1] <> "ремонт" then [Индекс] else null),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Пользовательская"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each [Состояние] = "ремонт"),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Гос.№", "Гос"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Пользовательская.1", each List.NonNullCount(#"Renamed Columns"[Состояние])),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Пользовательская"}, {  {"Дата отчета", each List.Max([Дата]), type date}, {"Гос.№", each List.Distinct(_[Гос])},{"всего в ремонте за период",each List.Distinct(_[Пользовательская.1])},{"дней в ремонте (с последней постановки на ремонт)", each Table.RowCount(_), type number}}),
    #"Kept Last Rows" = Table.LastN(#"Grouped Rows", 1),
    #"Extracted Values" = Table.TransformColumns(#"Kept Last Rows", {"Гос.№", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Extracted Values1" = Table.TransformColumns(#"Extracted Values", {"всего в ремонте за период", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values1",{"Пользовательская"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"всего в ремонте за период", Int64.Type}})
in
    #"Changed Type1"
 
 
 
#5
23.04.2019 08:18:51
Цитата
artyrH написал:
чето много шагов
Так мышкаж ;)
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    setDataTypes = Table.TransformColumnTypes(Source,{{"Гос.№", Text.Type}, {"Дата", Date.Type}}),
    setNeededOrder = Table.Sort(setDataTypes,{{"Гос.№", Order.Ascending}, {"Дата", Order.Ascending}}),
    stateReport = Table.Group(setNeededOrder, {"Гос.№", "Состояние"}, {
        {"Last", each List.Max([Дата]), Date.Type},
        {"Count", each List.Count([Дата]), Int64.Type}
    }, GroupKind.Local),
    outReport = Table.Group(stateReport, {"Гос.№"}, {
        {"Дата отчёта", each List.Max([Last]), Date.Type},
        {"Всего в ремонте за период", each List.Sum(Table.SelectRows(_, each [Состояние] = "ремонт")[Count]), Int64.Type},
        {"Дней в ремонте (с последней постановки на ремонт)", each Table.Max(Table.SelectRows(_, each [Состояние] = "ремонт"), "Last")[Count], Int64.Type}
    })
in
    outReport
 
 
 
#6
23.04.2019 08:41:33
Андрей VG, спасибо за науку
вопросик есть: возможно ли добавить при группировке два столбца без имен( имена по умолчанию) и значений?
 
 
 
#7
23.04.2019 09:45:47
Цитата
artyrH написал:
возможно ли добавить при группировке два столбца без имен( имена по умолчанию) и значений?
Не уловил, зачем, но как-то так
Код
let
    groupDefs = List.Transform({1..2}, each {"Column" & Text.From(_), (subTable) => null, type nullable number}),
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    group = Table.Group(Source, {"Состояние"}, groupDefs)
in
    group
 
 
 
#8
23.04.2019 10:10:12
Цитата
Андрей VG написал:
Не уловил, зачем
это я намудрил. скорее всего,при загрузке данных от запроса появились два пустых столбца. и я думал что они были изначально.
а так да, эти столбцы не нужны.
 
 
 
#9
24.04.2019 03:39:32
Спасибо большое всем за решения!
Андрей VG,  особенно - его решение как всегда на высоте.
artyrH, ваше решение неправильно работает если добавляю несколько Гос.№ (это в условии не оговаривалось,  поэтому это, естественно, не претензия :))
У Андрей VG, именно универсальное решение для разных случаев.
Единственно, я так понимаю, необходима сортировка перед группировкой - иначе не срабатывает. Не всегда удобно сортировать, но то такое... Уже мелочи.
Ещё раз спасибо всем помогающим.
 
 
 
 
#10
24.04.2019 05:24:07
Цитата
игорь сергеев написал:
ваше решение неправильно работает если добавляю несколько Гос.№
а что же вы не добавили в пример несколько Гос.№ ? чтоб потом сказать что не правильно работает?  
 
 
 
#11
24.04.2019 05:45:20
Цитата
игорь сергеев написал:
Не всегда удобно сортировать, но то такое... Уже мелочи.
что сортировать? Это не то?
Цитата
Андрей VG написал:
setNeededOrder = Table.Sort...
 
 
 
#12
25.04.2019 23:47:56
Вариант с использованием DAX.
В PQ сделал таблицу дат и запрос к данным.
В PP в основной таблице сделал дополнительные столбцы для возможности группировки, вычисления количества разных состояний и подсчета дней.
Прошу сильно не критиковать  сделал быстро для понимания можно ли решить такую задачу через другой инструмент анализа данных
Изменено: DrillPipq - 26.04.2019 00:01:23
 
 
 
Страницы: 1
Наверх