Страницы: 1
RSS
Power Query подстановка данных из другой таблицы
 
Добрый день!

Решаю следующую задачу:
1) Есть исходная таблица с данными в которой среди прочего есть столбцы ИСПОЛНИТЕЛЬ и ДАТА (в реальности она формируется отдельным запросом, но в примере дана сразу).
2) Есть вторая таблица в которой для каждого исполнителя в разный период проставляется свой коэффициент K (таблица постоянно дополняется и периодов может быть сколь угодно много).
3) Через запрос необходимо к исходной таблице добавить столбец K и каждому исполнителю проставить его коэффициент.
Процесс выбора коэффициента следующий:
- каждая строка исходной таблицы имеет запись ИСПОЛНИТЕЛЬ и ДАТА
- в таблице коэффициентов ищется строка совпадающая с исполнителем (она только одна)
- затем дата в исходной таблице сравнивается с датами в таблице коэффициентов и первая для которой выполняется условие ДАТА_ИСХОДНАЯ <= ДАТА КОЭФФ. и дает нужный K
- для всех случаев, когда искомого пользователя в таблице К не находится либо его дата больше дат в таблице коэффициентов, то в исходную таблицу проставляется К=1000

Вот с формированием такого запроса и возникла трудность. Буду рад помощи в решении задачи.
 
Не уверен, что все прям правильно понял - но суть можно будет уловить. Сделал все в новом запросе, но и в имеющихся внес изменения(типы привел к одному, чтобы слияние прошло успешно)
Возможно, Вам хватит результата уже на этом шаге:
Код
#"Добавлен пользовательский объект" = Table.AddColumn(#"Измененный тип", "мин_равно", each if [DATE] <=[Дата_к] then [Дата_к] else #date(1900,1,1)),
Тогда и этот шаг можно будет записать иначе:
Код
= Table.AddColumn(#"Измененный тип", "мин_равно", each if [DATE] <=[Дата_к] then [Значение_к] else 1000)
и останется просто удалить дубли в разрезе Сотрудник-Статус.
P.S. Посмотрел, надо было мин.отбирать, а не макс.
Код
#"Добавлен пользовательский объект" = Table.AddColumn(#"Измененный тип", "мин_равно", each if [DATE] <=[Дата_к] then [Дата_к] else #date(2100,1,1)),
Изменил вложение, т.к. далее запрос тоже чуть поменялся в части замены ошибок.
Изменено: Дмитрий(The_Prist) Щербаков - 15.02.2020 19:37:45
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
Доброе время суток.
Ещё вариант.
Код
let
    Source = Excel.CurrentWorkbook(){[Name="source"]}[Content],
    typedSource = Table.TransformColumnTypes(Source,{{"STATUS", type text}, {"EMPLOYEE", type text}, {"DATE", type date}, {"FACT", type number}}),
    tableK = Excel.CurrentWorkbook(){[Name="table_k"]}[Content],
    unpivotedK = Table.UnpivotOtherColumns(tableK, {"EMPLOYEE"}, "DATE", "K"),
    typedK = Table.TransformColumnTypes(unpivotedK,{{"DATE", type date}, {"K", type number}}),
    markK = Table.AddColumn(typedK, "mark", each 1),
    union = typedSource & markK,
    prefillOrder = Table.Sort(union,{{"EMPLOYEE", Order.Ascending}, {"DATE", Order.Ascending}, {"mark", Order.Ascending}}),
    fillK = Table.FillUp(prefillOrder, {"K"}),
    sourceRowsOnly = Table.SelectRows(fillK, each ([mark] = null)),
    neededColsOnly = Table.RemoveColumns(sourceRowsOnly, {"mark"}),
    result = Table.ReplaceValue(neededColsOnly, null, 1000, Replacer.ReplaceValue, {"K"})
in
    result
 
Спасибо за столь скорые ответы. Вроде получается что задумывалось. Попробую теперь применить все к реальным данным.
Трансформировать таблицу коэффициентов в столбец как-то не приходило мысли, а оказывается так тоже можно и работает.

Цитата
Андрей VG написал:
fillK = Table.FillUp(prefillOrder, {"K"})
Расскажите, пожалуйста, как работает или где почитать про Table.FillUp, все ясно кроме нее, а русского внятного описания что-то не нахожу.
 
Так это вроде на языке "клац мышью" заполнение вверх...
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
Цитата
Vitali_ написал:
а русского внятного описания что-то не нахожу.
А что собственно не внятно написано в официальном справочнике Table.FillUp?
 
Код не мой, но работает как надо
Код
let source_table = Excel.CurrentWorkbook(){[Name="source"]}[Content],     
table_k = Excel.CurrentWorkbook(){[Name="table_k"]}[Content],     
Merge = Table.TransformColumns(Table.NestedJoin(source_table,{"EMPLOYEE"},     
table_k,{"EMPLOYEE"},"table",1),{{"table",each Table.Transpose     
(Table.DemoteHeaders(Table.RemoveColumns(_,{"EMPLOYEE"}))) }}),     
Add = Table.AddColumn(Merge,"K", each try Table.SelectRows (let n =  Date.From([DATE]), t = [table] in Table.AddColumn( t, "Name2", each n,
type date ), each Date.From([Column1]) >= [Name2])[Column2]{0} otherwise 1000)     
in  
Table.RemoveColumns(Add,{"table"})
Изменено: Mirdv - 16.02.2020 18:22:13
Страницы: 1
Наверх