Привет У меня есть таблица с датами операций по менеджерам. Мне интересно высчитывать перерыв между операциями. Например, в данном случае для менеджера 1 нужно посчитать разницу между 4 строчкой и 2, 6 и 4, 8 и 6. То есть мне нужен перерыв между текущей строчкой и предыдущей операцией. Для менеджера 2 соответственно разницу между 5 и 3,7 и 5. Разницу вывести в отдельный столбец и сделать это в PQ. Помогите пожалуйста, благодарю!
let
from = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
group = Table.Group(from, {"Менеджер"}, {{"temp", each [#"Дата операции"], type table}}),
join = Table.NestedJoin(from,"Менеджер",group,"Менеджер","All"),
lists = Table.TransformColumns(join,{{"All", each [temp]{0}}}),
diff = Table.AddColumn(lists, "Difference", each try [Дата операции]-List.Skip([All],List.PositionOf([All],[Дата операции])-1){0} otherwise #duration(0,0,0,0)),
to = Table.RemoveColumns(diff,{"All"})
in
to
Юрий М, Не совсем понял Ваш вопрос. Дата=ДатаВремя, пытаюсь высчитать разницу между двемя ДатойВременем для одинаковых менеджеров. buchlotnik, спасибо огромное! Разбираюсь!
Выглядит более громоздко чем у buchlotnik, но на больших массивах такой алгоритм просто летает по сравнению с джойнами и выборкой из списка по условию.
Код
let
Source = Table.TransformColumnTypes( Excel.CurrentWorkbook(){[Name="Table1"]}[Content], {{"Менеджер", type text}, {"Дата операции", type datetime}} ),
GroupedRows = Table.Group(Source, {"Менеджер"}, {{"таб", each
[a = Table.Sort(_,{{"Дата операции", Order.Ascending}}),
b = Table.ToColumns( a ) & { {null} & List.FirstN( a[Дата операции], Table.RowCount(a) - 1 ) },
c = Table.FromColumns( b, Table.ColumnNames( a ) & {"Пред." & List.Last( Table.ColumnNames( a ) )} )][c], type table}}),
Custom1 = Table.Combine( GroupedRows[таб] ),
ChangedType = Table.TransformColumnTypes(Custom1,{{"Пред.Дата операции", type datetime}}),
AddedCustom = Table.AddColumn(ChangedType, "Разность", each [Дата операции] - [Пред.Дата операции], type duration),
SortedRows = Table.Sort(AddedCustom,{{"Дата операции", Order.Ascending}})
in
SortedRows
Для себя из ссылки сварганил такую вот универсальную функцию по добавлению к таблице столбцов со смещением.
Скрытый текст
Функция, которая добавляет в таблицу (указанную как первый аргумент) копию столбца (указанного во втором аргументе) со смещением элементов на 1 строку вверх или вниз (в третьем аргументе 0 - вниз / 1 - вверх). Вместо выбывающего элемента добавляется null.
Код
fnTableAddOffsetColumn = ( tbl as table, clmn as text, dir as number ) =>
if dir = 0
then Table.FromColumns(
Table.ToColumns( tbl ) & Table.ToColumns( #table( {clmn}, {{null}} ) & Table.RemoveLastN( Table.SelectColumns( tbl, {clmn}), 1 ) ),
Table.ColumnNames( tbl ) & {"Пред."&clmn} )
else Table.FromColumns(
Table.ToColumns( tbl ) & Table.ToColumns( Table.RemoveFirstN( Table.SelectColumns( tbl, {clmn}), 1 ) & #table( {clmn}, {{null}} ) ),
Table.ColumnNames( tbl ) & {"След."&clmn} )
PooHkrd, спасибо за ссылку! Единственное - в данном конкретном случае сортировка вроде как не нужна - данные в хронологии идут
Скрытый текст
Код
let
from = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
group = Table.Group(from, {"Менеджер"}, {{"таб", each
[a = _,
b = Table.ToColumns(a) & {{null} & List.FirstN(a[Дата операции], Table.RowCount(a)-1)},
c = Table.FromColumns(b,Table.ColumnNames(a)&{"Пред дата"})][c], type table}}),
combine = Table.Combine(group[таб]),
typ = Table.TransformColumnTypes(combine,{{"Дата операции", type datetime},{"Пред дата", type datetime}}),
add = Table.AddColumn(typ, "Разность", each [Дата операции] - [Пред дата], type duration),
to = Table.Sort(add,{"Дата операции", Order.Ascending})
in
to