#powerquery
#powerquery
Вопрос:
Я ищу способ оценить и отобразить графически рабочий процесс двух команд.
С одной стороны, у меня есть набор данных с проектами в виде строк и начальной и конечной датой для каждой из команд, A и B.
С другой стороны, у меня есть набор данных со всеми приращениями дней.
Чего бы я хотел, так это количества активных проектов каждый день для каждой команды. В SQL я бы попробовал подзапрос…
Спасибо за вашу помощь.
Ответ №1:
Я бы построил запрос к каждому набору данных (у вас, вероятно, они уже есть), затем я бы добавил пользовательский столбец к каждому, например
Dummy Merge Key
= 1
Затем я бы добавил шаг слияния к запросу «Проекты», чтобы объединить его с запросом «дни», присоединившись к фиктивному ключу слияния. Затем я бы расширил результирующий NewColumn, чтобы получить требуемые столбцы запроса «дни» (например, дата).
На этом этапе у вас есть перекрестное соединение (в терминах SQL). Затем я бы добавил пользовательский / условный столбец, например
Include Date
= [Start_TEAM_1] >= [Date] and [End_TEAM_1] <= [Date]
Затем вы можете выполнить фильтрацию по результирующему столбцу = true, чтобы сохранить только строки со значениями [Date] в пределах диапазонов от начала до конца.
Очевидно, что ваша структура данных «Проекты» (с дублированными начальными и конечными столбцами) неудобна для этой цели — я бы, вероятно, сначала открепил ее, чтобы избежать повторяющегося кода и запутанных критериев.
Ответ №2:
Интересно, требуется ли это по-прежнему (в любом случае, может быть, кто-то еще найдет это полезным). 🙂
Я могу предложить это решение:
let
//Create example source tables
Projects = Table.FromColumns(
{
{"P001","P002"},
{#date(2016,10,06), #date(2016,10,11)},
{#date(2016,10,11), #date(2016,10,16)},
{#date(2016,10,16), #date(2016,10,21)},
{#date(2016,10,24), #date(2016,10,29)}
},
{"Project_No", "Start_Team_1", "End_Team_1", "Start_Team_2", "End_Team_2"}),
Dates = Table.TransformColumnTypes(Table.FromColumns({List.Transform({1..31}, each #date(2016,10,_))}, {"Date"}),{{"Date", type date}}),
//Add dummy columns, join, remove dummy columns
Join = Table.RemoveColumns(
Table.Join(
Table.AddColumn(Dates, "tmp1", each 1), {"tmp1"},
Table.AddColumn(Projects, "tmp2", each 1), {"tmp2"},
JoinKind.FullOuter
)
, {"tmp1", "tmp2"}),
//Group dates and count required rows
GetResult = Table.Group(Join, {"Date"},
{
{"Team1_Prj_Count", each Table.RowCount(Table.SelectRows(_, each [Start_Team_1] <= [Date] and [End_Team_1] >= [Date])), type number},
{"Team2_Prj_Count", each Table.RowCount(Table.SelectRows(_, each [Start_Team_2] <= [Date] and [End_Team_2] >= [Date])), type number}
})
in
GetResult
Кажется, решает вашу проблему.