Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Power Query. Создание нового столбца таблицы по условию используя справочник
 
#1
25.03.2022 13:13:32
Приветствую коллеги!

Я к вам за оптимальным решением следующей задачи:

Имеем огромный лист Номенклатуры и имеем созданный большой справочник, в котором пытаемся прописать Категорию номенклатуры по части названия Номенклатуры.

Вроде запрос не сложный, но выполнял его ранее с помощью функции "Conditional Column", а запихивать в него целый справочник и заниматься его редактированием, то еще удовольствие.

Попытался сделать свой вариант, но чет он пока хромает... Пример во вложении

Уверен есть отработанное быстрое решение.

Заранее спасибо за помощь и за ваши варианты)
 
 
 
#2
25.03.2022 13:37:46
тут не смотрели?
Добавил копию вашего файла с точным исполнением из приема по ссылке
Изменено: Vik_tor - 25.03.2022 14:13:03
 
 
 
#3
25.03.2022 14:27:40
Спасибо!
Да, это рабочий вариант!
Честно думал в эту сторону, но почему то показалось, что много вокруг нужной функции движений и как будто бы есть решение проще...
Вот смотрите, мы ведь здесь явно делаем сравнение одной колонки с другой, только вместо нужной нам колонки мы добавляем значение 1 или 0,
Код
= Table.AddColumn(#"Развернутый элемент Пользовательский", "Вхождение", each if Text.Contains([SKU],[SKU NAME], Comparer.OrdinalIgnoreCase) then 1 else 0)

а хотелось бы в одно действие выполнить сравнение и подстановку. Если конечно такое возможно. На вид, как будто одно действие, должно работать явно быстрее, чем набор из 8...

Вам еще раз спасибо за предложенный вариант!
 
 
 
#4
25.03.2022 14:33:34
я тут не помогу. я начинающий PQвед.
 
 
 
#5
25.03.2022 14:36:12
Понял, спасибо.
Может кто из Гуру что подскажет по данному вопросу
Изменено: tdolgiy - 25.03.2022 14:37:27 (Опечатка)
 
 
 
#6
25.03.2022 15:09:30
Совсем не гуру но сказать хочется. Из исходных данных совсем не понятно, у вас  есть гарантия, что в исходных данных будут только вариант "мандарин" и "мандарины" и не будет "мандар." например? То есть, что все "не справочниковые" варианты от справочника могут отличаться только лишними буквами на хвосте.
 
 
 
#7
25.03.2022 15:17:56
Я привел всего лишь пример использования, для поиска оитимального решения. Конечно по факту может оказаться все что угодно...
Пока давайте разберемся, как это реализовать изящно и чтобы на больших данных работало быстро
 
 
 
#8
25.03.2022 15:22:39
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Category", each List.Accumulate (#"Table2", [SKU], (x,y) => Text.Replace (x, y[SKU NAME], y[Category]&"_"))),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Added Custom", {{"Category", each Text.BeforeDelimiter(_, "_"), type text}})
in
    #"Extracted Text Before Delimiter"

Вот решение быстрое, но явно с костылем в виде добавления аргумента для дальнейшего разделителя...
Хочется прямо заменить функцию Text.Replace на нечто, что просто поменяет значение целиком в ячейке, а не часть найденного текста...

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

Помогайте идеями господа)
Изменено: tdolgiy - 25.03.2022 15:26:46
 
 
 
#9
25.03.2022 16:14:23
tdolgiy, я бы так сделал (запрос категорий вставил сюда же во второй шаг):
Код
let
  src = Excel.CurrentWorkbook(){[ Name = "SKU" ]}[Content],
  categories_rec = List.Buffer ( Table.ToRecords ( Excel.CurrentWorkbook(){[ Name = "categories" ]}[Content] ) ),
  addCategory = Table.AddColumn (
    src,
    "Category",
    each List.Accumulate (
      categories_rec,
      null,
      ( s, c ) => if Text.Contains ( [SKU], c[SKU NAME], Comparer.OrdinalIgnoreCase ) then c[Category] else s
    )
  )
in
  addCategory

Вот сравнение скорости на 100 000 SKU (категорий столько же):
 
 
 
#10
25.03.2022 16:41:24
Цитата
tdolgiy написал:
для поиска оитимального решения
Если сравнивать по "начинается с..." как в вашем примере, то, например, вот.
Не знаю, как там с быстродействием.

Код
let
    temp = Table.AddColumn(табФрукты, "temp", each табСправочник),
    expand = Table.ExpandTableColumn(temp, "temp", {"SKU NAME", "Category"}),
    final = Table.SelectRows(expand, each Text.StartsWith([SKU], [SKU NAME], Comparer.OrdinalIgnoreCase) 
                                         )[[SKU], [Category]]
in
    final
Изменено: Xel - 25.03.2022 16:43:59
 
 
 
#11
25.03.2022 17:38:40
Ребята, вариантов накидали, спасибо,  есть повод поразмыслить)
Xel ,
Ваш вариант немного напоминает первый предложенный от Vik_tor, получается, что мы в любом случае джойним все варианты со всеми, а потом выбираем нужное. Надо посмотреть на быстродействие. Спасибо за идею!

surkenny,
Ваш вариант ближе к моим мыслям, и вроде все делается в одно действие! Не все понимаю как работает, буду разбираться. Спасибо за идею!

Если у кого будут еще варианты, не стесняйтесь, все мы учимся и разные подходы приветствуются!

Всем добра!
Изменено: tdolgiy - 25.03.2022 17:42:00
 
 
 
#12
25.03.2022 17:56:10
Цитата
tdolgiy написал:
мы в любом случае джойним все варианты со всеми
Мы и в List.Accumulate тоже пробегаемся по всем вариантам. Можно еще List.Select попробовать вместо List.Accumulate. Будет время - протестирую. Но не думаю, что будет разница.
 
 
 
#13
25.03.2022 18:19:15
Цитата
tdolgiy написал:
все варианты со всеми, а потом выбираем нужное

Насколько я понимаю, ListAccumulate делает то же самое, просто вы не видите одним шагом на предпросмотре всю большую таблицу.
Но тут вопрос в другом, наверное. Вы заменяете текст (довольно ресурсоемко), а потом отрываете хвост после замены, это точно лишние действия, жрущие ресурсы. Если таблички достаточно в таком виде, как она получается во втором варианте (и у меня), то реплейс вообще не нужен. Что проверять "текст содержит" или "текст начинается с..." и не проще ли возможные варианты написания забить в справочник или разрешить только "Мандарин" и за "Мандарины" отрывать руки. Это на скорости работы может гораздо значительнее сказаться.
 
 
 
#14
25.03.2022 18:29:05
Xel,
Возможно, вероятно тест на больших данных расставит все на свои места, surkenny, вроде свои результаты изысканий протестировал. У меня сейчас нет под рукой возможности провести похожий тест... В люббом случае исходя из описанного кейса, задача начинается с проверки услови - и безусловно это ресурсоемкая задача , но таков кейс... А вот что подставить в место найденного условия, и как это следующий вопрос, о нем и говорим.

surkenny,
Буду благодарен, если сможете посмотреть быстродействие!

Ребят спасибо!
Изменено: tdolgiy - 25.03.2022 18:31:00
 
 
 
#15
25.03.2022 23:29:19
tdolgiy, вот сравнительная таблица скорости на 150 000 sku и 100 категориях:

1. В SKU может быть что-то подобное Мандариновый свитер. Чтобы мандарин не заменился раньше времени на фрукт в коде из #8 добавил сортировку категорий по количеству букв в SKU NAME. В своих запросах так же добавил сортировку, чтобы последним найденным вхождением всегда было самое длинное.
2. Запрос из #8 оставляет в категории наименование SKU в категории, если ничего в SKU не удалось заменить. Добавил проверку на наличие разделителя. На картинке результат до изменения:

Самый быстрый - surkennyListSelectRecs_withCase. Результат аналогичен подправленному #8, но в 2 раза быстрее.
Скрытый текст

Самый оптимальный - surkennyListSelectRecs. Позволяет игнорировать регистр.
Скрытый текст

Отличие обоих вариантов только в значимости регистра для нахождения подстроки: Text.Contains ( [SKU], x[SKU NAME], Comparer.OrdinalIgnoreCase ).
Итого, я бы выбрал surkennyListSelectRecs. Незначительно медленнее, но позволяет присваивать категорию игнорируя регистр. Если для запросов #8_Variant и surkennyListSelectRecs_withCase прописывать замены с разной капитализацией, это увеличит время работы запроса.
Изменено: surkenny - 25.03.2022 23:47:26
 
 
 
#16
25.03.2022 23:46:05
surkenny,
Вот это подход, молодец! Все четко и по полочкам, не придерешься)
Удивлен, что мой Вариант #8, с таким костылем оказался все же в правильном русле по скорости... Ну а вы молодец, что доработали и проверили на практике. Это позволяет реально не додумывать, что делает функция внутри себя, а реально оценивать практический пример.

Спасибо отдельно, что добавили регистронезависимость. Чет об этом на начальном этапе не подумал.

Всем добра ну и новых идей!
 
 
 
#17
25.03.2022 23:55:16
tdolgiy, для домашнего задания: вот этот ? тут не случайно :)
   each List.Last ( List.Select ( categories_rec, ( x ) => Text.Contains ( [SKU], x[SKU NAME] ) ) )[Category]?
Изменено: surkenny - 26.03.2022 00:02:46
 
 
 
Страницы: 1
Наверх