Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Power Query аналог ВПР или ПОИСК из этой же таблицы, Можно ли осуществлять поиск значения по условию из этой же таблицы?
 
#1
14.06.2022 11:41:15
Добрый день!

Подскажите, пожалуйста, можно ли в Power Query осуществлять поиск по условию в этой же таблице?
Объединение таблиц подошло бы, но таблица только одна.

Условие:
Дан столбец с номером транзакции и ответственным. За один и тот же номер транзакции может быть несколько ответственных.
В столбце тип транзакции указывается тип, при этом для ответственного Петрова тип не проставлен.
Необходимо заполнить поле тип для пользователя Петров значением, равным типу этого же номера транзакции у ответственного Иванова.

В случае в Merge можно было бы как-то объединить таблицы по двум ключевым столбцам,

Но как быть, если таблица только одна? Можно ли осуществлять поиск по значениям в этой же таблице? Что-то вроде аналога формулы поиск+индекс?
Нашла много материала на эту тему, но только про случаи с двумя отдельными таблицами.  
Изменено: Ольга - 14.06.2022 12:05:30
 
 
 
#2
14.06.2022 12:00:18
У Сидорова тип средний, а не пусто. Проверьте свой пример еще раз
 
 
 
#3
14.06.2022 12:03:15
Ольга, у Вас в примере для транзакции 1538 4 стоки, в двух из которых указан разный тип. Каким из этих типов нужно заполнить пустые? Возможно ошибка в примере.
Цитата
Ольга написал:
В случае в Merge можно было бы как-то объединить таблицы по двум ключевым столбцам
Что мешает объединить таблицу саму с собой? :)
Но данный способ точно будет не самым оптимальным.
Уточните, сколько статусов у одной транзакции могут быть заполненными?
Если общий случай - у транзакции N ответственных и M заполненных статусов. По какой логике N-M ответственным выбрать статус? Ближайший непустой "сверху"?

P.S. Если для любого числа строк одной транзакции тип заполнен только в одной строке, то
Код
let
  data    = Excel.CurrentWorkbook(){[ Name = "data" ]}[Content],
  sort    = Table.Sort ( data, { { "номер транзакции", Order.Ascending }, { "Тип транзакции", Order.Descending } } ),
  group   = Table.Group ( sort, { "номер транзакции" }, { { "tbl", ( t ) => Table.FillDown ( t, { "Тип транзакции" } ) } }, GroupKind.Local ),
  combine = Table.Combine ( group[tbl] )
in
  combine
Изменено: surkenny - 14.06.2022 12:03:47
 
 
 
#4
14.06.2022 12:06:09
Цитата
написал:
У Сидорова тип средний, а не пусто. Проверьте свой пример еще раз
Спасибо, поправила описание задачи - для Петрова
 
 
 
#5
14.06.2022 12:18:40
Цитата
написал:
Ольга, у Вас в примере для транзакции 1538 4 стоки, в двух из которых указан разный тип. Каким из этих типов нужно заполнить пустые? Возможно ошибка в примере.
Цитата
Ольга написал:
В случае в Merge можно было бы как-то объединить таблицы по двум ключевым столбцам
Что мешает объединить таблицу саму с собой?
Но данный способ точно будет не самым оптимальным.
Уточните, сколько статусов у одной транзакции могут быть заполненными?
Если общий случай - у транзакции N ответственных и M заполненных статусов. По какой логике N-M ответственным выбрать статус? Ближайший непустой "сверху"?

P.S. Если для любого числа строк одной транзакции тип заполнен только в одной строке, то
Код
    [URL=#]?[/URL]       1  2  3  4  5  6  7      let        data    = Excel.CurrentWorkbook(){[ Name =   "data"   ]}[Content],        sort    = Table.Sort ( data, { {   "номер транзакции"  , Order.Ascending }, {   "Тип транзакции"  , Order.Descending } } ),        group   = Table.Group ( sort, {   "номер транзакции"   }, { {   "tbl"  , ( t ) => Table.FillDown ( t, {   "Тип транзакции"   } ) } }, GroupKind.Local ),        combine = Table.Combine ( group[tbl] )    in        combine  
 
Спасибо! Поправила описание задачи - ошиблась с фамилией.
У одного номера транзакции может быть разное количество ответственных  (как в примере - для транзакции 1537 два ответственных, для 1538 - три). У Иванова и Сидорова тип транзакции проставлен всегда, у Петрова - никогда. Нужно проставить значение для Петрова - тип транзакции всегда равен типу этой же транзакции у Иванова.

Вообще, у меня сейчас получилось решить задачу двойной сортировкой и заполнением значений вниз: я отсортировала таблицу по 1)номеру транзакций и 2)типу транзакции. Отфильтровала только значение Иванов и Петров (получилось, что для каждого Иванова тип заполнен и он на первом месте в сортировке, Для Петрова не заполнен. И заполнила значения вниз - то есть значение Иванова спустилось на Петрова.

Но тут выяснилась другая проблема: может быть такое, что у одного номера транзакции ответственный только Петров. Тогда "заполнить значение вниз" работает некорректно и тянет значение из другого номера транзакции. Можно ли как-то сюда вписать условие, что если у номера транзакции ответственный только Петров, то значение равно 0?

Я думаю как-то реализовать это с помощью аналога СЧЕТЕСЛИ, можно ли это сделать в Power Query?  Например, добавить вспомогательный столбец со счетом фамилии Иванов для каждого номера транзакции. Затем отфильтровать столбец, убрав значение 0. И затем проделать ту же процедуру с заполнением вниз?
 
 
 
#6
14.06.2022 12:35:14
Ольга,
Код
let
  data = Excel.CurrentWorkbook(){[ Name = "data" ]}[Content],
  group = Table.Group (
    data,
    { "номер транзакции" },
    {
      {
        "tbl",
        ( t ) =>
          [
            value = Table.SelectRows ( t, ( r ) => r[Отвественный] = "Иванов" )[Тип транзакции]{0}?,
            result = Table.FromRecords (
              Table.TransformRows (
                t,
                ( r ) => Record.TransformFields ( r, { { "Тип транзакции", ( x ) => if r[Отвественный] = "Петров" then value else x } } )
              )
            )
          ][result],
        Value.Type ( data )
      }
    }
  ),
  combine = Table.Combine ( group[tbl] )
in
  combine
 
 
 
#7
14.06.2022 13:09:34
surkenny, Супер, спасибо большое! Именно то, что нужно!
Буду осваивать создание подзапросов в запросе :)
 
 
 
Страницы: 1
Читают тему
Наверх