Блог

Я загружаю данные с сервера Team Foundation Server, которые выходят в родительском дочернем формате.

Я получаю первые три поля из своего подключения к данным. для последних двух я хочу показать идентификатор истории даже для задач, где идентификатор истории является ближайшим идентификатором истории над записью в списке, для задач и идентификатором истории, если запись является историей.

Второе поле, которое мне нужно, чтобы суммировать оставшиеся часы для задач в этой истории.

Предположим, что записи не будут отсортированы и останутся в родительском дочернем формате. В каждой истории может быть несколько задач. Я хочу сделать это в power query в excel.

Пожалуйста, посоветуйте.

В powerquery,

Добавьте пользовательский столбец с именем Родительская история с формулой

 = if [Work Item Type]="Story" then [ID] else null
 

Щелкните правой кнопкой мыши этот новый столбец и заполните его

Группа по родительской истории и сумма оставшихся часов. Добавьте пользовательский столбец с именем Тип рабочего элемента с формулой = «История».

Объедините эту таблицу с результатами до создания группы, используя Внешнее соединение слева, сопоставив поля Типа рабочего элемента и Родительской истории между таблицами. То, как я реализовал это, потребовало изменений кода вручную в разделе » Главная…дополнительно…».

Увеличьте количество часов

Пример кода, который предполагает, что ваши исходные данные из 3 столбцов находятся в таблице диапазонов1. Вы можете вставить код в PowerQuery в домашних условиях…Расширенный редактор…

 let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Work Item Type", type text}, {"Remaining Hours", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Parent Story", each if [Work Item Type]="Story" then [ID] else null),
#"Filled Down" = Table.FillDown(#"Added Custom",{"Parent Story"}),

// group the data to sum hours
#"Grouped Rows" = Table.Group(#"Filled Down", {"Parent Story"}, {{"TotalHours", each List.Sum([Remaining Hours]), type number}}),
#"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Work Item Type", each "Story"),

// merge the data back in
#"Merged Queries" = Table.NestedJoin(#"Filled Down",{"Work Item Type", "Parent Story"},#"Added Custom1",{"Work Item Type", "Parent Story"},"Table3",JoinKind.LeftOuter),
#"Expanded Table3" = Table.ExpandTableColumn(#"Merged Queries", "Table3", {"TotalHours"}, {"Remaining Hours (Agg)"})
in #"Expanded Table3"
 

Альтернативный код, использующий пользовательскую функцию вместо слияния.

 let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Work Item Type", type text}, {"Remaining Hours", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Parent Story", each if [Work Item Type]="Story" then [ID] else null),
#"Filled Down" = Table.FillDown(#"Added Custom",{"Parent Story"}),
countem = (i)=>List.Sum(Table.SelectRows(#"Filled Down" , each [Parent Story]=i) [Remaining Hours]),
#"Added Custom1" = Table.AddColumn(#"Filled Down", "Remaining Hours (Agg)", each if [Work Item Type] = "Task" then null else countem([Parent Story] ))
in #"Added Custom1"